Asp.Net Mystery Membership Database

I’ve used the built-in Asp.Net Membership Provider a number of times for its simplicity and straight forward API. There are a few mystical elements that can trip you up, if you don’t understand how the provisioning process works. There are two primary options:

• manual
• auto-magical

Manual

I generally create the membership database before I start working on the user interface (seems logical right?). I do that by running the membership provider wizard by opening a Command Prompt, navigating to the .Net 2.0 framework folder and running the aspnet_regsql.exe command (c:\windows\Microsoft.net\framework\v2.0.50727\aspnet_regsql.exe on a 32bit OS).

That launches the setup wizard where you can choose how you want the database configured in your SQL Server instance:

• use an existing database such as the application database or
• a stand-alone membership database if you want to have multiple applications use the same membership database

The wizard then runs the necessary scripts against the selected database (creating the database if it doesn’t exist) and populates it with the tables, views, and stored procedures that the API uses to manage the membership details.

Auto-magical

In case you hadn’t guessed, this is method enshrouded by mystery (not really, but it can seem like it). If you don’t choose to manually create your membership database (via the wizard), then the default option as defined in the machine.config file is to use a SQL Express database which ends up getting created in the application’s App_Data folder in Visual Studio.

<connectionStrings>
        <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Depending on your version of Visual Studio and perhaps the method you use to initiate the creation of this database, it might not be obvious exactly where the database is or how it is configured to point to that database file. The corresponding connection string doesn’t necessarily show up in the web.config file because it is inherited from the one defined in the machine.config.

There are multiple ways you can initiate the creation of the membership database. A couple include:

1. Select “ASP.NET Configuration” from the Visual Studio “Project” menu (screenshot is from VS 2010).
2. Drag a login control onto the design surface and select “Administer Website” from its context menu.

Upon first use of adding a user, role, or other activity a SQL Express database (ASPNETDB.MDF) will be created including the membership provider’s tables, views, and stored procedures used to manage the membership and personalization system.

Mystery

In a quick prototype project, I used the auto-magical process and added a few users. You will notice in the App_Data folder, there doesn’t appear to be an ASPNETDB.MDF, only a TeamStrength.mdf file, so where are my users stored?

Upon closer inspection with Windows Explorer, there actually is a membership database in that folder.

Mystery solved.

You may also be interested in my introduction to implementing ASP.NET Role Based Security presentation or the code samples from the presentation can be found through a link on that blog post.

Photo credit: m_bahareth / CC BY 2.0

NUFW Presentation - .Net Role Based Security

Last night I presented .Net Role Based Security (using the built-in Membership and Personalization provider model) for the Fort Wayne .Net User Group. Click Here to download the presentation and code samples. Armanda Turney added to the discussion the use of the SiteMap file with the enabling of security trimming to further programatically enhance the user experience by limiting the navigation menu options based on user roles. The .Net membership and personalization model provides a quick and easy method to add and customize membership and role based security in a way that is accessible even to beginner programmers.

Creating a Windows Phone 7 app using WCF, and SQL Azure

Last night I gave a presentation with demos for fwPASS creating a Windows 7 app displaying a list of gymnasts from a SQL Azure database using WCF (Windows Communication Foundation). The WCF service used the Entity Framework and the .Net Membership provider.

Windows phone 7, WCF, and SQL Azure

View more presentations from Dean Willson

The application was surprisingly easy to create despite the fact that were a lot of moving pieces with integration between all the different components.

The presentation covered the following tools:

• Windows Phone Marketplace App Hub 
• Visual Studio IDE
• SQL Server Management Studio (SSMS)
• Windows Azure Platform Management Portal
• SQL Azure Migration Wizard
• Windows Phone 7 Emulator

 

All that in 70 minutes. Yes, I talk fast. This was a first time presentation. Below is the demo script I used:

Marketplace

1. Preview App Hub http://create.msdn.com

· Show app details text, iconography

Database

1. SSMS – show local ChalkChimp database

· Show script for creating additional users?

2. http://windows.azure.com SQL management interface

· No query designer

3. SQL Azure Migration tool

· Scripts out db

· Shows unsupported features

· deploys

4. Azure limitations in SSMS

· No query designer

· Admin account (other users don’t have access to the master database)

WCF

1. Create new project WCF Application

· Highlight Framework version 3.5

· Change binding to basicHTTPBinding

2. New Item à ADO.Net Entity

· Azure connection

3. Switch to finished project

4. Modify Iservice.cs, modify Service.cs implementation

5. Show .Net membership bits in web.config

6. View in browser (copy url)

WP7 client

1. Open Visual Studio (another)

2. New Project à Windows Phone 7 application

3. New item à Phone portrait page

4. Add Service reference

· Show service methods

· Advanced show generate async methods

5. Switch to finished project

· Show xaml bindings

· Show async methods and completed event handlers

· switch device to emulator

6. Run it

Optional

· Show property pages and iconography

 

Technorati Tags: Windows Phone 7,WP7,WCF,Silverlight,SQL Azure

What is the DataField Name of a GridView bound to a String Array?

This is a special case that I don’t remember experiencing before but resolved with the help of a StackOverflow question Binding an ASP.NET GridView Control to a string array.

In a simple Asp.Net Membership management application I was simply binding a Role list to a GridView so I could click a link to see which users were members of a selected Role. I was using the following syntax in the CodeBehind where grvRoles is the GridView:

grvRoles.DataSource = Roles.GetAllRoles

grvRoles.DataBind()

Note: Roles.GetAllRoles() is in the System.Web.Security namespace which returns a string array of Roles from the aspnetdb Membership Database.

On the Designer:

<asp:GridView ID="grvRoles" runat="server" 

    EnableModelValidation="True">

    <Columns>

        <asp:CommandField ShowSelectButton="True" />

    </Columns>

</asp:GridView>

Which produced the following output with the GridView auto-generated columns. Notice the column name is “Item”, but the datasource is a string array which doesn’t technically have a DataField name like if it was bound to a Generic List or Collection of class objects.

I want to use a LinkButton where the CommandArgument is the Role name which is represented as a String value of the array. So If I convert the “Select” Command button column to a Template field, what do I use as the DataField name? Generally I would use the syntax:

<asp:GridView ID="GridView1" runat="server" 

    EnableModelValidation="True">

    <Columns>

    <asp:TemplateField ShowHeader="False">

        <ItemTemplate>

            <asp:LinkButton ID="lnkMembers" 

                runat="server" CausesValidation="False"

                CommandArgument='<%#Eval("Item") %>' 

                CommandName="Select" Text="Members"></asp:LinkButton>

        </ItemTemplate>

    </asp:TemplateField>

</Columns>

</asp:GridView>

 

Note the CommandArgument=’<%#Eval(“Item”) %>’, but that won’t work, because the String Array doesn’t actually have an Item property. Instead you can use the longhand syntax: CommandArgument='<%# container.dataitem %>'.

 

If I don’t want to have the GridView auto-generate the columns, what do I use for the asp:BoundField name? Apparently there is a secret syntax by using an exclamation point <asp:BoundField DataField="!" HeaderText="Role" />.

MvcScaffolding uses SQL Express by Default

The fact that the MvcScaffolding package uses SQL Express by default can be both good and deceiving at the same time.

Using an Existing Database (not Code-First)

I used the package on a MVC3 project to scaffold the repository models and controllers for an existing SQL Server Standard database (not SQL Express) that had an existing Ado.Net Entity Framework model. The EF connection string obviously pointed to the existing database. However when running the application, the Index pages didn’t list any of the existing data from my database.

If this looks like the same problem you are having, feel free to skip to the bottom for the solution (convention over configuration).

Where’s the data?

I could add records, but they didn’t show up in the existing database. There was no Express database .mdf file in the application’s App_Data folder. There were no additional  connection strings (other than the EF connection to the existing database) in the web.config files or any of the dbcontext class files. I profiled the existing database with SQL Profiler and the existing database was completely untouched. I even added a <remove name=”LPMEntities” /> line before the EF connection string to make sure any default connection from the server root or machine.config was taken out of the inheritance tree (similar to what you would do for the aspnetdb membership database if you are using a full SQL Server instance).

The new data was being stored somewhere, but where? In a newly created SQL Express database.

How do I See the Data?

Open your SQL Server Management Studio (SSMS) or the version of SSMS for SQL Express. In the Object Explorer window, click Connect. Use .\sqlexpress as the server to connect to using Windows Authentication and voila the new mysterious “hidden” database.

I hope this helps to clarify for other people attempting to use the MvcScaffoling in a Database-First scenario.

The Solution – Understand the Convention over Configuration

It took me a while to find a small subtle, yet crucial, detail in Scott Guthrie’s Using EF “Code First” with an Existing Database.

The following note is stated at the end of Step 5 – Configuring our Database Connection String:

EF “code first” uses a convention where context classes by default look for a connection-string that has the same name as the context class.  Because our context class is called “Northwind” it by default looks for a “Northwind” connection-string to use.  Above our Northwind connection-string is configured to use a local SQL Express database.  You can alternatively point it at a remote SQL Server.

In my case the Entity Framework connection string in the web.config was named “LPMEntities”. The class implementing the DbContext is named LPMContext as shown below.

namespace LPM.Models

{

    public class LPMContext : DbContext

    {

        public DbSet<LPM.Type> Types { get; set; }

        additional stuff here...

    }

}

Since LPMEntities is not the context class name, the EF generates a local SQL Express database.

I just added a connection string to the web.config with a name of LPMContext (same name as the context class) and my MVC forms are populated from the existing database.

Victory!

photo credit: Si1very / CC BY-SA 2.0