SqlProfileProvider vs. SqlTableProfileProvider

The SqlProfileProvider is the built-in Profile Provider that ASP.NET comes with by default.  It allows you to define custom fields that make up profiling data you would want to collect and store about your users.  This could be demographics, preferences, or any other useful details.  This specific implementation is designed in a way where no matter what fields you define, your database schema will be compatible with any upgrade Microsoft could send down to patch the Provider system or ASP.NET in general.  The way this is accomplished is by storing a blob of all the field names in one table column and a blob of all the field values in another column; it’s similar to the concept of a dictionary with key/value pairs.  The difficult part is that these blobs can contain clear-text, XML, and/or binary, depending on how the source data type serializes by default.  Unfortunately, this highly flexible solution is quite unusable if you need a clear view of the Profile data for reporting purposes or stored procedure needs.

When trying to find an already implemented custom provider that would allow to store each Profile field in its own table column, I came across a blog post from Scott Guthrie introducing such an implementation done by two Microsoft employees.  The name of this provider is the SqlTableProfileProvider, and it can be found at the following link:

Table Profile Provider Samples

At the above location, you will find an MSI download that will install a sample project containing a few source code files you can include in your project; you will also find a download link for a "white paper" Word document that explains the usage of the SqlTableProfileProvider and the SqlStoredProcedureProfileProvider custom providers.  The nice thing about both of these providers is that their use in code and their configuration in the Web.config is extremely similar to how the original SqlProfileProvider is used and configured.

Setting up the SqlTableProfileProvider

As was done with the Membership and Role Management Providers, here is the XML fragment that would go in your Web.config:

<configuration>
    <connectionStrings>
        <add name="MyDB" connectionString="..." />
    </connectionStrings>
    <system.web>
        ... authentication & authorization settings ...
        <profile enabled="true"
                 defaultProvider="ConfiguredProfileProvider">
            <providers>
                <add name="ConfiguredProfileProvider"
                     type="Microsoft.Samples.SqlTableProfileProvider"
                     connectionStringName="MyDB"
                     table="custom_Profile"
                     applicationName="SampleWebSite" />
            </providers>
            ... properties section found in the next XML snippet ...
        </profile>
    </system.web>
</configuration>

WARNING: According to Microsoft, you should never use the default provider settings and should always add a new, configured provider; in addition you must be sure to specify the applicationName attribute! See Scott Guthrie’s blog post on this topic for more details. Apparently, you can also use a value of "/" for the applicationName and it will use the root application name.

You’ll notice in the type attribute that we are not referencing the default SqlProfileProvider that ships with ASP.NET.  What you need to do is get this new class included or referenced in your solution so that this namespace reference can successfully resolve.  I opted to just include the code files in my solution as is; I chose to place them in my App_Code folder.  The code files you want to grab are likely in the default location that the MSI installed to, which is Program Files/Microsoft/ASP.NET 2.0 Table Profile Provider Samples.  The two files you need are SqlStoredProcedureProfileProvider.cs and SqlTableProfileProvider.cs, both of which are found in the App_Code folder of the sample project.  You need to copy both code files to your solution, as it appears that the SqlTableProfileProvider depends on some logic in the SqlStoredProcedureProfileProvider for some reason.

Also note the table attribute above (which is not present in the built-in SqlProfileProvider), which tells the provider which table in your database will be storing the custom Profile fields.  As for the "properties section" referenced near the end of the above snippet, this is where you specify the custom Profile fields and their mappings to column names in the specified database table.  The advantage of specifying this information in the Web.config is that you will be able to get programmatic access to these fields as strongly-typed properties on a proxy class named Profile in your code-behind.  Here is a sample properties configuration:

<properties>
    <add name="FirstName"
         type="string"
         defaultValue="[null]"
         customProviderData="FirstName;nvarchar" />
    <add name="Age"
         type="int"
         customProviderData="Age;int" />
    <add name="AccountExpiration"
         type="DateTime"
         defaultValue="[null]"
         customProviderData="AccountExpiration;datetime" />
    <add name="CookieID"
         type="System.Guid"
         defaultValue="[null]"
         customProviderData="CookieID;uniqueidentifier" />
</properties>

The Profile system provides a configuration point for each Profile field called customProviderData.  It appears the default SqlProfileProvider doesn’t even look at this additional attribute, but rather reserves this configuration point for use by custom implemented Profile providers.  The SqlTableProfileProvider utilizes this extra metadata field as a place to put mapping information for a column name and ADO.NET database type.  If you have questions about which ADO.NET and .NET types to use/specify in this configuration, there is helpful information in the accompanying white paper found on the download site.  Also found in that document is a great discussion you may want to read about the defaultValue attribute, what it configures, and how it might differ for value types (such as integer).

Creating the Database Table

Now that you have the code files in your solution and have configured the Profile fields, you need to create the custom table in the database that this provider will query and update.  Here is a sample SQL definition for our custom profile table:

create table dbo.custom_Profile
(
    UserId               uniqueidentifier not null Primary Key,
    FirstName            nvarchar(50) null,
    Age                  int null,
    AccountExpiration    datetime null,
    CookieID             uniqueidentifier null,
    LastUpdatedDate      datetime not null
)
go

Note that the SqlTableProfileProvider requires two extra columns in addition to your custom defined Profile properties, namely UserId and LastUpdatedDate.  One other thing to be aware of is that if you use the Membership API to delete a user programmatically, this will remove all entries related to that user in the database except from your custom Profile table.  I believe this might be because the Profile system can apparently be used independently of the Membership system, even allowing you to store Profile data on anonymous, unauthenticated users.  I think the same caution would go for any utility that presumably uses this Membership API under the covers, such as the ASP.NET Website Administration Tool launched from Visual Studio that I referred to earlier in this series with a post titled ASP.NET Providers - Getting Started.  Up to this point, I have resorted to manually clearing out Profile table rows myself that are linked to the membership account being removed from the database.

Also, if your web site is using a Windows user or system account for the identity of the worker process, you’ll want to make sure that set of credentials has SELECT, INSERT, UPDATE, and DELETE permissions granted to it for this new custom database table you created.  There are also some EXECUTE, SELECT, and UPDATE permissions required on a few of the already-existing stored procedures that the Provider system uses.  Again, check the accompanying white paper on the download site for a discussion about these procedure and for some SQL statements to help you automate the granting of these permissions.

The Profile Proxy Class

If you are using the Web Site project template in Visual Studio, then you are good to go with using the Profile proxy class that can be used anywhere in your code to retrieve and update Profile fields on the current user.  The availability of this Profile class is independent of whether you are using the built-in ASP.NET SqlProfileProvider or the custom SqlTableProfileProvider that I’ve been showcasing thus far.  However, if you are using the Web Application project template, this proxy object is not auto-generated for you by Visual Studio.  For a detailed discussion of the problem and a working solution, see my next post which is entitled ASP.NET Profile and Web Application Projects.

Use of this Profile class couldn’t be easier and is exactly as you would expect it to be.  Here is a code snippet that both retrieves and updates strongly-typed Profile properties (exactly as they were defined in the Web.config) using this proxy object:

// Set Profile property
Profile.FirstName = txtBoxFirstName.Text;

// Retrieve Profile property
int age = Profile.Age;

Read On!

This crash course of the ASP.NET Provider system is pretty much done I guess you could say, as far as a tutorial goes that is.  I still have another two posts on the radar that will be coming over the next few days regarding issues and workarounds that I encountered.  One post will deal with generating this Profile proxy class under the Visual Studio Web Application project template.  The other post will be about the advantages of using this system under IIS 7 found in Windows Vista and Server 2008; it will also include some issues with using this system if some of your content is static HTML instead of ASPX pages.  Here are the links to these two posts:

• ASP.NET Profile and Web Application Projects
• IIS 7 and ASP.NET Providers (Membership, Roles, and Profile)

It has been a very pleasant experience overall to work with the ASP.NET Provider system because of it’s highly customizable and flexible design.  If your Membership, Role Management, and Profile needs are fairly common and typical, this framework is a piece of cake to incorporate into your product (including your existing ones, no matter how out-of-date they are).  Hope you’ve enjoyed the series!