Implementing the Table and StoredProcedure
Profile Providers for ASP.NET

by Peter A. Bromberg, Ph.D.

Peter Bromberg
"Should democracy be allowed to fail in Iraq and terror permitted to triumph, then the war on terror will never be won elsewhere." --Nuri al-Maliki, to the US Congress

When ASP.NET 2.0 first arrived last year, there wasn't very good sample code or documentation, and if you didn't have any experience in "rolling your own" provider implementation, there were some drawbacks that didn't look like they would be very easy to fix.



Often developers coming to a new platform such as .NET are inclined to "write their own" functionality either because they aren't aware that such functionality is already built in to the Framework, or because they find it too daunting a task to learn how to use it.

One of the biggest drawbacks I saw to using Profile was that the default Sql Server Profile Provider stores Profile data in a "non-searchable way": the ASP.NET 2.0 Profile feature ships with a single provider – the SqlProfileProvider.  This provider is very flexible and automatically works with any serializable type that is stored in a Profile object.  However the cost of this flexibility is that Profile data is stored in either an opaque text blob or an opaque binary blob.  As a result there is no direct way to query Profile data from inside of the database. You cannot search profiles of your "members" with a normal SQL query, and you cannot index the profile data with FullText, because all the profile name/value pairs are serialized and stored in a single column, essentially.

However, after I while I started working a bit more with these ProviderBase classes. One effort was to create Membership and Roles providers that worked with the SQLite Database engine. When Robert Simpson wrote his ADO.NET 2.0 provider for SQLite, he stuck to the provider model. Then, I started working on integrating Membership and Roles. Most recently, Scott Guthrie posted on his blog about the ASP.NET Provider samples - a sample pack that contains base provider classes for most of the ASP.NET 2.0 providers. Finally, the ASP.NET site "sandbox" area points to two sample implementations by Hao Kung, and that gave me enough ammunition to overcome the "unsearchable profile issue".

Hao's samples allow you to create your own Profile table in the normal non-serialized manner that one would expect. You can have a column of any datatype you want for each profile item in your custom profile. Further, he provides a second sample that shows how to implement the set and get profile stored procedures. In this sample, I have set up my own custom profile table that one would expect to have in a typical ".NET related" site where members can have their experience level and preferences stored, these preferences can be searched, and then if the "Newsletter" preference is "Yes", the site owner could use this information to email out a topic-specific newsletter to say, members whose experience level is "Beginner" and whose first topic preference is "WebServices".

The key thing to understand about the Provider model is that if you do not like a particular provider, you are free to implement your own, deriving from the ProviderBase class for that feature, and in the web.config, you simply tell the app to use your custom provider, and that's exactly what it obediently does.

So, let's take a look at the table structure first:

Provider tables schema

Note in the figure above that I use two SQL Server 2005 tables: Categories simply holds the various categories of Preferences available on the hypothetical web site, e.g. "ASP.NET", "WebServices", "Remoting", etc. The second table, ProfileTable_1, has the same name as Hao's example, with the typical data one would expect to collect from a "Member" - FirstName, LastName, etc., and three preferences - each of which is an integer value from the Categories table, and a bit field "Newsletter" which indicates that the user either does or does not want to receive emailed newsletters.

This table will work with both the Table provider (which I"ve implemented, but which is commented out in the sample web.config) and with the Stored Procedure provider. Now let's look at the web.config elements required to make this come alive:

<?xml version="1.0"?>

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

 <appSettings/>

 <connectionStrings>

<clear/>

<!-- CHANGE THIS CONNECTION STRING TO POINT AT YOUR DATABASE-->

<add name="LocalSqlServer" connectionString="Data Source=(local);Integrated Security=True;database=bugz;Initial Catalog=bugz"/>

</connectionStrings>

 <system.web>

<roleManager enabled="true" />

<compilation debug="true"/>

<authorization>

<deny users="?"/>

</authorization>

<membership defaultProvider="AspNetSqlMembershipProvider" userIsOnlineTimeWindow="15">

<providers>

<clear/>

<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" passwordAttemptWindow="10" passwordStrengthRegularExpression="" minRequiredPasswordLength="5" minRequiredNonalphanumericCharacters="0"/>

</providers>

</membership>

<profile enabled="true" defaultProvider="StoredProcedureProfileProvider">

<providers>

<clear/>

<!--

<add name="TableProfileProvider"

type="Microsoft.Samples.SqlTableProfileProvider"

connectionStringName="TestDatabase"

table="ProfileTable_1"

applicationName="/"/>

-->

 

<add name="StoredProcedureProfileProvider"

type="Microsoft.Samples.SqlStoredProcedureProfileProvider"

connectionStringName="LocalSqlServer"

 

setProcedure="setCustomProfileData"

readProcedure="getCustomProfileData"

applicationName="/"/>

</providers>

<properties>

<!-- config for table provider -->

<!--

<add name="FirstName" defaultValue="[null]" customProviderData="FirstName;nvarchar"/>

<add name="LastName" defaultValue="[null]" customProviderData="LastName;nvarchar"/>

<add name="Email" type="string" customProviderData="Email;varchar"/>

<add name="Phone" type="string" customProviderData="Phone;varchar"/>

<add name="Address" type="string" customProviderData="Address;varchar"/>

<add name="City" type="string" customProviderData="City;varchar"/>

<add name="State" type="string" customProviderData="State;varchar"/>

<add name="Zipcode" type="string" customProviderData="Zipcode;varchar"/>

<add name="SignupDate" type="string" customProviderData="SignupDate;varchar"/>

-->

<!-- config for stored procedure provider -->

<add name="FirstName" defaultValue="[null]" customProviderData="FirstName;nvarchar;50"/>

<add name="LastName" defaultValue="[null]" customProviderData="LastName;nvarchar;50"/>

<add name="Email" defaultValue="[null]" type="string" customProviderData="Email;varchar;150"/>

<add name="Phone" defaultValue="[null]" type="string" customProviderData="Phone;varchar;15"/>

<add name="Address" defaultValue="[null]" type="string" customProviderData="Address;varchar;300"/>

<add name="City" defaultValue="[null]" type="string" customProviderData="City;varchar;28"/>

<add name="State" defaultValue="[null]" type="string" customProviderData="State;varchar;5"/>

<add name="Zipcode" defaultValue="[null]" type="string" customProviderData="Zipcode;varchar;50"/>

<add name="SignupDate" defaultValue="[null]" type="datetime" customProviderData="SignupDate;datetime;8"/>

<add name="Experience" defaultValue="[null]" type="int" customProviderData="Experience;int;4" />

<add name="Preference1" defaultValue="[null]" type="int" customProviderData="Preference1;int;4" />

<add name="Preference2" defaultValue="[null]" type="int" customProviderData="Preference2;int;4" />

<add name="Preference3" defaultValue="[null]" type="int" customProviderData="Preference3;int;4" />

<add name="Newsletter" defaultValue="false" type="bool" customProviderData="Newsletter;bit;1" />

</properties>

</profile>

<authentication mode="Forms">

<forms name="/"

loginUrl="Login.aspx"

protection="All"

timeout="30"

path="/"

requireSSL="false"

slidingExpiration="true"

defaultUrl="Default.aspx"

enableCrossAppRedirects="true"/>

</authentication>

 

<!-- <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">

<error statusCode="403" redirect="NoAccess.htm" />

<error statusCode="404" redirect="FileNotFound.htm" />

</customErrors>

-->

 </system.web>

</configuration>

Let's go through these elements, from top to bottom:

First, we have to have a connection string to the Sql Server 2005 database. This is in the connectionStrings section, and notice that this section has a <clear/> element that appears first. This is to clear out the default SQLExpress provider that is already mapped in your machine.config.

Next, roleManager is set to enabled - in other words, yes we want roles.


Next, our membership section clears and then adds our ASPNETSqlMembershipProvider - the default provider that comes from running ASPNETRegSQL or:

System.Web.Management.SqlServices.Install(database,System.Web.Management.SqlFeatures.All, connectionString);

Next, we have our Profile section which declares our custom StoredProcedureProfileProvider as the default, and adds the declarations for all the table provider fields (commented out and operational, but not used in this sample) and for the StoredProcedureProfileProvider that we've implemented here and described above.

Note the way each table column is mapped to a Profile field:

<add name="Newsletter" defaultValue="false" type="bool" customProviderData="Newsletter;bit;1" />

The above element adds the "NewsLetter" profile field, with a default value of "false", defines it as boolean, and gives the Stored proc set /get method it's name, Sql data type and size. Everything from there on is handled automatically by the provider implementation class.

Finally, I have set up a standard FormsAuthentication Authentication node.

This now brings us to the provider implementation. The SqlStoredProcedureProvider has a number of fields and methods, almost all of which are from the base class or are overridden from the base class:


In this particular sample the most important methods are the SetPropertyValues and GetPropertyValues methods, which use the definitions in the web.config and the associated 2 stored procedures to store and retrieve Profile Property values respectively. The other methods that use SQL Server I've simply lifted out of the TableProvider, they aren't used as frequently and therefore I saw no particular importance in spending a lot of time converting them to stored procedures just to be a "sproc purist". (However, if that type of exercise floats your boat, please be my guest!)

When you put all this together you will see a Login Page with a Login Control and a CreateUserWizard control. You can create a new user, and then login, and you'll be transported to my nifty "Profile Page":

Here you can see that the Experience dropdown is populated with the choices, and each of the Preference dropdowns has been databound with the choices from the Categories Table, with the user's actual last preference being shown as the selected item. If you are a newly created user, all these items will of course, be blank.

Finally, once again, I've made good use of Peter Kellner's excellent MembershipUserObjectDataSource and associated sample pages to provide a way for an administrator to set roles and other features on users with a nice web interface. My CreateUser Form also uses Peter's implementation.

Summary: By implementing a custom Table / StoredProcedure Profile Provider, we can not only make it easy to collect and modify custom user Profile items, we also have the freedom to search and filter against this data as with any other Sql Server database table, while still maintaining 100% integration with the ASP.NET 2.0 Provider model.

The Visual Studio 2005 download below has the complete "Bugz" MDF / LDF Sql Server 2005 database files already in the APP_DATA folder and only needs to be attached to your database. It is already "wired up" for Membership, Roles and Profile features. If you are using SqlExpress, all you should need to do is alter the connection string data with the AutoAttach feature. Or, you can use Sql Management Studio Express to attach the database MDF file just as with the full SQL Server 2005.

The web site is an ASP.NET 20 "Web Site" file-based project, just double click on the "sln" file. I also have a sample page that illustrates how to enable a database for Membership, Roles and Profiles programmatically. Please don't flame me because I didn't include validation of form entries, this, that or the other thing! This is not a complete application; it's purpose is solely to illustrate how the custom StoredProcedureProfileProvider can be implemented.

Download the Visual Studio 2005 Solution that accompanies this article


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.
Article Discussion: