Using ASP.NET SQL Membership Provider, how do I store my own per-user data?

Question

I'm using the ASP.NET SQL Membership Provider. So, there's an aspnet_Users table that has details of each of my users. (Actually, the aspnet_Membership table seems to contain most of the actual data).

I now want to store some per-user information in my database, so I thought I'd just create a new table with a UserId (GUID) column and an FK relationship to aspnet_Users. However, I then discovered that I can't easily get access to the UserId since it's not exposed via the membership API. (I know I can access it via the ProviderUserKey, but it seems like the API is abstracting away the internal UserID in favor of the UserName, and I don't want to go too far against the grain).

So, I thought I should instead put a LoweredUserName column in my table, and create an FK relationship to aspnet_Users using that. Bzzzt. Wrong again, because while there is a unique index in aspnet_Users that includes the LoweredUserName, it also includes the ApplicationId - so in order to create my FK relationship, I'd need to have an ApplicationId column in my table too.

At first I thought: fine, I'm only dealing with a single application, so I'll just add such a column and give it a default value. Then I realised that the ApplicationId is a GUID, so it'd be a pain to do this. Not hard exactly, but until I roll out my DB I can't predict what the GUID is going to be.

I feel like I'm missing something, or going about things the wrong way. What am I supposed to do?

Answer 1

I think you are looking for the ProfileProvider which let's you associate any arbitrary information you wish with the user.

ASP.NET Profile Properties Overview

ADDITION If the built-in ProfileProvider will not suit your needs, then you might consider implementing your own ProfileProvider by writing a class that derives from System.Web.Profile.ProfileProvider. This would enable you to write something that avoids the serialization issues you mentioned in your comment.

Implementing a Profile Provider

ADDITION Note about the SqlMembershipProvider. You are indeed correct that the Membership classes are really keyed on username even though the schema is keyed on UserId. This is frankly one of my pet peeves about the SqlMembershipProvider classes. This actually creates problem in a multi-application environment where you want a single user store but independent application role lists.

My recommendation would be to key on UserId since it is, as you mentioned, the primary key of the aspnet_Users table and is used in all the foreign key relationships and it is a single value. If you key on LowerUsername (and ApplicationId), and the username changes, you'll need to have cascade update enabled so that the change ripples to your custom tables.

Answer 2

To do this, you can implement a profile provider. Its not very difficult. You will basically set up your user specific settings like this:

(web.config):

<profile enabled="true" defaultProvider="MyProvider">
    <providers>
        <add name="MyProvider" connectionStringName="MembershipCnnStr" applicationName="MyApp" type="System.Web.Profile.SqlProfileProvider"/>
    </providers>
    <properties>
        <add name="EmployeeId" type="String" />
        <group name="UserSettings">
            <add name="IsSandboxMode" type="Boolean" defaultValue="false" />
            <add name="Shortcuts" type="System.Collections.Generic.List`1[System.string]" />
        </group>
    </properties>
</profile>

Answer 3

If you're looking at adding simple data tied to your users, such as added profile properties, you should look into Personalization.

Example, if you want to store a person's mother's maiden name as a part of their profile information you can do so using this feature.

It probably isn't the right choice for complex data, but it's a start.