Microsoft ASP.NET 4 : Using the SqlProfileProvider (part 1) - The Profile Databases, Defining Profile Properties

6/25/2012 5:57:27 PM
The SqlProfileProvider allows you to store profile information in a SQL Server database. You can choose to create the profile tables in any database. However, you can't change any of the other database schema details, which means you're locked into specific table names, column names, and serialization format.

From start to finish, you need to perform the following steps to use profiles:

  1. Enable authentication for a portion of your website.

  2. Configure the profile provider. (This step is optional if you're using SQL Server Express. Profiles are enabled by default.)

  3. Create the profile tables. (This step isn't required if you're using SQL Server Express.)

  4. Define some profile properties.

  5. Use the profile properties in your web page code.

You'll tackle these steps in the following sections.

1. Enabling Authentication

Because profiles are stored in a user-specific record, you need to authenticate the current user before you can read or write profile information. You can use any type of authentication system, including Windows-based authentication and forms-based authentication. The profile system doesn't care—it simply stores the user-specific information in a record that's identified based on the user name. Seeing as every authentication system identifies users uniquely by user name, any authentication system will work.

The following web.config file uses Windows authentication:

    <authentication mode="Windows"/>

<deny users="?"/>

Because this example uses Windows authentication, you don't need to create a record for each user. Instead, you'll use the existing Windows user accounts that are defined on the web server. This approach also saves you from creating a login page, because the browser handles the login process. 

If you decide to use forms authentication instead, you'll need to decide whether you want to perform the authentication using your own custom user list  or in combination with the membership features. In most cases, the membership and profiles features are used in conjunction—after all, if you're using the profiles feature to store user-specific information automatically, why not also store the list of user credentials (user names and passwords) automatically in the same database?

Once you've chosen your authentication system (and taken care of any other chores that may be necessary, such as creating a user list and generating your login page), you're ready to use profiles. Remember, profiles store user-specific information, so the user needs to be authenticated before their profile is available. In the web.config file shown previously, an authorization rule ensures this by denying all anonymous users.

2. Using SQL Server Express

When using SQL Server Express, ASP.NET stores profile information in an automatically generated database file named aspnetdb.mdf. If this file doesn't exist, it's created the first time you use any membership or profiles features, and it's placed in the App_Data subdirectory of your web application. The best part is that you don't need to go through any additional configuration steps, because ASP.NET is configured, by default, to use SQL Server with profiles.

3. Using the Full Version of SQL Server

This automatic-database creation feature relies on SQL Server Express. If you're using a non-Express version of SQL Server, you must create the database you need manually and explicitly configure the profiles feature in the web.config file.

By default, the connection string that is used with profiles is named LocalSqlServer. You can edit this connection string directly in the machine.config file. However, if you just need to tweak a single application, it's better to adjust the web.config file for your web application.

To do so, you need to remove all the existing connection strings using the <clear> element in your web application's web.config file. Then, add the LocalSqlServer connection string again—but this time with the right value:

        <clear />
        <add name="LocalSqlServer" providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;Integrated Security=SSPI;
Initial Catalog=aspnetdb" />

You'll then need to create the aspnetdb database using the aspnet_regsql.exe command-line utility. This is the same tool that allows you to generate databases for other ASP.NET features, such as SQL Server–based session state, membership, roles, database cache dependencies, and Web Parts personalization. You can find the aspnet_regsql.exe tool in the c:\Windows\Microsoft.NET\ Framework\[Version] folder (where [Version] is the version of ASP.NET that's installed, like v4.0.30319).

To create the tables, views, and stored procedures required for profiles, you use the -A p command-line option. The other details you may need to supply include the server location (-S), database name (-d), and authentication information for connecting to the database (use -U and -P to supply a password and user name, or use -E to use the current Windows account). If you leave out the server location and database name, aspnet_regsql.exe uses the default instance on the current computer and creates a database named aspnetdb.

The easiest way to use aspnet_regsql is to open the Visual Studio command prompt. To do so, open the Start menu and choose All Programs => Microsoft Visual Studio 2010 => Visual Studio Tools => Visual Studio Command Prompt. The following example creates a database named aspnetdb in the SQL Server database server on the current computer. It adds all the ASP.NET tables, including the ones used for membership, role-based authentication, and profiles:

aspnet_regsql.exe -S (local) -E -A all

If you want to use a different database, you must specify the database name using the -d parameter. Either way, you should use a new, blank database that doesn't include any other custom tables. That's because aspnet_regsql.exe creates several tables for profiles (see Table 21-1 in the next section), and you shouldn't risk confusing them with business data.


This command line uses the -A all option to create tables for all of ASP.NET's database features, including profiles and membership. You can also choose to add tables for just one feature at a time.

4. The Profile Databases

Whether you use aspnet_regsql to create the profile databases on your own or you use SQL Server Express and let ASP.NET create them automatically, you'll wind up with the same tables. Table 1 briefly describes them. (The rather unexciting views aren't included.)

The contents aren't of much interest, because ASP.NET manages them automatically. All the information you store in a profile is combined into one record and quietly placed in a field named PropertyValuesString in a table named aspnet_Profile.

Table 1. Database Tables Used for Profiles
Table NameDescription
aspnet_ApplicationsLists all the web applications that have records in this database. It's possible for several ASP.NET applications to use the same aspnetdb database. In this case, you have the option of separating the profile information so it's distinct for each application (by giving each application a different application name when you register the profile provider) or sharing it (by giving each application the same application name).
aspnet_ProfileStores the user-specific profile information. Each record contains the complete profile information for a single user. The PropertyNames field lists the property names, and the PropertyValuesString and PropertyValuesBinary fields list all the property data, although you'll need to do some work if you want to parse this information for use in other non-ASP.NET programs. Each record also includes the last update date and time (LastUpdatedDate).
aspnet_SchemaVersionsLists the supported schemas for storing profile information. In the future, this could allow new versions of ASP.NET to provide new ways of storing profile information without breaking support for old profile databases that are still in use.
aspnet_UsersLists user names and maps them to one of the applications in aspnet_Applications. Also records the last request date and time (LastActivityDate) and whether the record was generated automatically for an anonymous user (IsAnonymous). 

Figure 1 shows the relationships between the most important profile tables.

Figure 1. The profile tables

5. Defining Profile Properties

Before you can store any profile information, you need to specifically define what you want to store. You do this by adding the <properties> element inside the <profile> section of the web.config file. Inside the <properties> element, you place one <add> tag for each user-specific piece of information you want to store. At a minimum, the <add> element supplies the name for the property, like this:

        <add name="FirstName"/>
        <add name="LastName"/>

Usually, you'll also supply the data type. (If you don't, the property is treated as a string.) You can specify any serializable .NET data type, as shown here:

<add name="FirstName" type="System.String"/>
<add name="LastName" type="System.String"/>
<add name="DateOfBirth" type="System.DateTime"/>

You can set a few more property attributes to create the more advanced properties shown in Table 2.

Table 2. Profile Property Attributes
Attribute (for the <add> Element)Description
nameThe name of the property.
typeThe fully qualified class name that represents the data type for this property. By default, this is System.String.
serializeAsThe format to use when serializing this value (String, Binary, Xml, or ProviderSpecific). You'll look more closely at the serialization model in the section "Profile Serialization."
readOnlyA Boolean value that determines whether a value is changeable. If true, the property can be read but not changed. (Attempting to change the property will cause a compile-time error.) By default, this is false.
defaultValueA default value that will be used if the profile doesn't exist or doesn't include this particular piece of information. The default value has no effect on serialization—if you set a profile property, ASP.NET will commit the current values to the database, even if they match the default values.
allowAnonymousA Boolean value that indicates whether this property can be used with the anonymous profiles feature . By default, this is false.
providerThe profile provider that should be used to manage just this property. By default, all properties are managed using the provider specified in the <profile> element, but you can assign different properties to different providers.

Top 10
Review : Sigma 24mm f/1.4 DG HSM Art
Review : Canon EF11-24mm f/4L USM
Review : Creative Sound Blaster Roar 2
Review : Philips Fidelio M2L
Review : Alienware 17 - Dell's Alienware laptops
Review Smartwatch : Wellograph
Review : Xiaomi Redmi 2
Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone