ASP.NET MVC: UserProfile Using the SqlTableProfileProvider


Many have discussed the woes of working with the default ASP.NET SqlProfileProvider in a web application as the profile information is pretty much impossible to query. Gratefully another profile provider was developed, the SqlTableProfileProvider, that stores user profile information in a table format with one column per profile property. The code for that can be found here. But alas, there is still one more issue when using the SqlTableProfileProvider when developing an ASP.NET MVC application. Since it is a web application project and not a web site project, you lose the convenience of the strongly typed Profile object when coding. Again, another work around has been created that solves this problem here. Unfortunately the previously cited example assumes you are using the default SqlProfileProvider and not the SqlTableProfileProvider. I wanted to just name a few changes that need to be made when using the cited Profile Provider in an ASP.NET MVC application with the SqlTableProfileProvider.

First, since you are specifying the profile properties in the UserProfile class, you need to remove the profile property definitions from the web.config profile section. Instead of your profile section looking like this:

<profile enabled="true" defaultProvider="TableProfileProvider" inherits="App.Domain.Models.Account.UserProfile">
  <providers>
    <clear />
    <add name="TableProfileProvider" type="Microsoft.Samples.SqlTableProfileProvider" connectionStringName="Database" table="Profiles" applicationName="/" />       
  </providers>
  <properties>
    <add name="FirstName" type="string" defaultValue="" customProviderData="FirstName;nvarchar" />
    <add name="LastName" type="string" defaultValue="" customProviderData="LastName;nvarchar" />
  </properties>
</profile>

Your profile section will not have the properties section and will look like this:

<profile enabled="true" defaultProvider="TableProfileProvider" inherits="App.Domain.Models.Account.UserProfile">
  <providers>
    <clear />
    <add name="TableProfileProvider" type="Microsoft.Samples.SqlTableProfileProvider" connectionStringName="Database" table="Profiles" applicationName="\" />       
  </providers>
</profile>

If you don’t remove the properties section, you will get a ConfigurationErrorsException at run-time saying “This profile property has already been defined.”

Second, while the UserProfile class will define which properties are available in the profile, you still need to specify the CustomProviderData information that gives the provider the column name and type for each property. To do this, just add the CustomProviderData attribute to each property in the UserProfile class.

public class UserProfile : ProfileBase
{
    [SettingsAllowAnonymous(false), CustomProviderData("FirstName;nvarchar")]
    public string FirstName
    {
        get { return base["FirstName"] as string; }
        set { base["FirstName"] = value; }
    }

    [SettingsAllowAnonymous(false), CustomProviderData("LastName;nvarchar")]
    public string LastName
    {
        get { return base["LastName"] as string; }
        set { base["LastName"] = value; }
    }
}

With those two changes you should be up and running with a strongly typed Profile object using the SqlTableProfileProvider.

One Response to “ASP.NET MVC: UserProfile Using the SqlTableProfileProvider”

  1. Dain Taffe Says:

    Hi Nick, I appreciate you giving this tip, but I must tell you that I am really clueless as to how to implement it. I am not sure if I am to add it as a model or a controller and since I am using VS12, a Userprofile class already exist in the AccountModels.cs file. Do I replace that class with yours? I am really not sure what to do?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: