Using the SQL Table Profile Provider in ASP.NET 4 Web Applications (C# & VB)

This post and accompanying projects demonstrate how to use Microsoft's SQL Table Profile Provider with an ASP.NET Web application.

ASP.NET's built-in Profile provider (SqlProfileProvider) stores Profile data in an oddball, serialized format that’s hard to use in queries and may not scale well in large sites.

To help developers use a more standard database format, Microsoft posted the source code for a sample provider (SqlTableProfileProvider). It stores each Profile property in a separate database column.

The SqlTableProfileProvider includes instructions and sample code for its use in file-based ASP.NET web sites where much of the Profile code is generated dynamically by ASP.NET. However, there's no documentation or sample showing  how to use SqlTableProfileProvider in a pre-compiled Web application.

A group of Microsoft MVPs took on the issue. This project and article are the results.

Of course, it would be better if  Microsoft provided a solution that's an integral and supported part of ASP.NET. If you agree, please vote for the enhancement on Microsoft Connect.

Source Code

If you’d rather stop reading and just get straight to the projects, they’re on the MSDN Code Gallery in the sample’s Downloads tab. Click here for the C# code and here for the VB code.

Resources Required

• Microsoft Web Platform - Be sure to select Visual Web Developer 2010 Express, SQL Server Express 2008 R2, and Sql Server 2008 R2 Management Studio Express.
• Table Profile Provider

Installation of this Sample

For the easiest installation (without the need to fix file paths), unzip this solution into a folder named c:\aspnet4profile to create a folder structure like this:

 
c:\aspnet4profile
   aspnet4profile.sln
   \aspnet4profilecs
   \SQLTableProfileProviderCS


Open the solution in Visual Studio 2010, build, and browse to default.aspx.

Technical Discussion

The sample solution includes two projects: a Web application named aspnet4profilecs and the SQLTableProfileProvidercs class library. The Web application references the SQLTableProfileProviderCS.dll.

SqlTableProfileProvider.cs

The file SqlTableProfileProvider.cs is the provider source code as released by Microsoft. Read the author's White Paper for implementation details.

SqlStoredProcedureProfileProvider.cs

SqlStoredProcedureProfileProvider.cs is an alternate implementation provided by Microsoft that uses stored procedures for database access. This code is not used in this project, except for a couple of routines that detect the connection configuration. For details, see the author's White Paper.

ProfileCommon.cs

ProfileCommon.cs is where you manually insert the profile properties that you are storing in the database table.

Recall that in a file-based Web site, the run-time compiler reads the Profile properties from the web.config file and generates the required code dynamically. Profile properties look like this:

<profile enabled="true" >
    <properties>
        <add name="FirstName" />
    </properties>          
</profile>

Important: The preceding web.config markup must not be part of a Web application using the SqlTableProfileProvider described here. Instead, you  create equivalent properties in code inside ProfileCommon.cs as shown in the excerpt below:

[CustomProviderData("FirstName;nvarchar")]
public virtual string FirstName
{
    get
    {
        return ((string)(this.GetPropertyValue("FirstName")));
    }
    set
    {
        this.SetPropertyValue("FirstName", value);
    }
}

The trick in getting the class to work is the decoration [CustomProviderData("FirstName;nvarchar")] where you tell ASP.NET the name of the Profile property (FirstName) and the SQL Server data type (e.g., nvarchar) to expect.

BTW, if you're using Profiles for anonymous users, there's an additional decoration,

[SettingsAllowAnonymous(true)]
[CustomProviderData("FirstName;nvarchar")]

Telling ASP.NET to Use SQLTableProfileProvider

The <profile> node in our project's web.config file tells ASP.NET to use the custom SQLTableProfileProvider rather than the built-in default version. In the snippet below you can see the inherits attribute's value tells ASP.NET the type followed by a comma and then the assembly where it can find the type.  The table attribute points to the name of the SQL database table where the Profile data is being stored. In this case the table name is aspnet_Profile2.

<profile enabled="true" 
        defaultProvider="TableProfileProvider"  
        inherits="Microsoft.Samples.ProfileCommon,SQLTableProfileProviderCS">
  <providers>
    <clear/>
    <add name="TableProfileProvider"
            type="Microsoft.Samples.SqlTableProfileProvider, SQLTableProfileProviderCS"
            connectionStringName="LocalSqlServer" table="aspnet_Profile2" 
            applicationName="/" />
  </providers>
</profile>

Creating the New Database Table

Because you're no longer using the default Profile Provider, you won't be using the default database table. Instead, you must add a new table to the database (ASPNETDB.MDF in our case) to support separate profile fields. You'll find a small starter sample SQL script in the Web project's App_Data folder. As you can see from the following excerpt the script creates the Profile data columns (such as FirstName, LastName, Age) along with other columns required by the base provider:

CREATE TABLE [dbo].[aspnet_Profile2](
    [UserID] [uniqueidentifier] NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Age] [int] NULL,
    [DateOfBirth] [datetime] NULL,
    [ProfileVersion] [int] NULL,
    [LastUpdatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Profile_UserProfile2] PRIMARY KEY CLUSTERED

The easiest way to run a SQL script is to open the database in SQL Server Management Studio Express.

Using ProfileCommon in ASP.NET Pages

You use the ProfileCommon class much like you use ASP.NET's auto-generated version. The page  registeredonly.aspx is accessible only by logged in users. You call GetUserProfile() to get the Profile data for the current authenticated user.

ProfileCommon Profile = ProfileCommon.GetUserProfile();

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        txtFirstName.Text = Profile.FirstName;
        txtLastName.Text = Profile.LastName;
        txtAge.Text = Convert.ToString(Profile.Age); 
    }
}

Support

Help Us Help You

If you agree that using the SQLTableProfileProvider in ASP.NET Web applications is harder than it needs to be, please vote for the product feedback called Add Support for the SQL Table Profile Provider in ASP.NET 4 Web Applications on Microsoft Connect.

Credits

Ken