ASP.NET 2.0 SQL Table Profile Provider Released Today

Hao and Stefan just shipped a cool new Profile Provider download on the ASP.NET Sandbox Site.  It provides two new profile provider implementations (with complete source code) that you can use.  One enables you to map profile properties directly to a SQL table in a database -- enabling you to perform richer queries on the back-end for your personalization data.  The second implementation enables you to map profile properties to stored procedures in a database -- enabling you to perform whatever custom data logic you want to persist your user personalization data. 

Enabling them within an ASP.NET 2.0 application is easy.  You just replace the profile provider mapping within the web.config file for your application (no code changes required).  Calls to and from the ASP.NET 2.0 Profile system will then delegate to the configured provider.  The profile system allows you to map different profile properties to different providers - so you could have some go against the built-in blob storage provider, some against the SQL table one, some against the SPROC one, and some against a completely new provider you implement (note: I probably wouldn't recommend having 4 different profile providers at once -- but it is nice to know you have the flexibility <g>).

Here are some past posts I've done about the ASP.NET 2.0 Profile system if you are interested in learning more about it:

Hope this helps,

Scott

 

Published Tuesday, January 10, 2006 9:29 PM by ScottGu

Comments

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Wednesday, January 11, 2006 7:21 PM by Jon
It's good that someone did this, I had planned on writing one myself. Saving all profile properties in 1 varchar field is a very bad idea for any kind of social networking site- you need SQL access to each field.

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Wednesday, January 11, 2006 10:13 PM by gozh2002
That looks great. I read the whitepaper word document, which said SqlTableProfileProvider converts the UserName and Application to a UserID field as primary key. If I want to used it to look up foreign keyed data in other tables, how do I access the key?

Does it automatically give me Profile.Username, Profile.ApplicationName, and a Profile.UserId to use, after I log in?

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Thursday, January 12, 2006 8:35 AM by gozh2002
that is great. I tested it, but the Custom UserProfile table is using UserId as primary key. So how could I get the key so I can search other linked data in my own query. I only can reference Profile.UserName. :(

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Thursday, January 12, 2006 1:06 PM by scottgu
Hi gozh2002,

Here is an answer I heard back from the team working on it:

If Membership is being used on the same site, just call Membership.GetUser :

MembershipUser mu = Membership.GetUser("username");
Guid g = (Guid)mu.ProviderUserKey;

From a SQL query standpoint - you can do the same thing with the following code in ADO.NET or in SQL in a sproc:

select m.UserId
from dbo.vw_aspnet_Applications a,
dbo.vw_aspnet_Users u,
dbo.vw_aspnet_MembershipUsers m
where a.LoweredApplicationName = LOWER(@pApplicationName)
and a.ApplicationId = u.ApplicationId
and u.LoweredUserName = LOWER(@pUserName)
and u.UserId = m.UserId

This query assumes you pass in the string username in @pUserName and the string application name in @pApplicationName.

Hope this helps,

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Saturday, January 21, 2006 8:55 AM by lightweight
So did you guys get it working... I'm using SQLexpress and get stopped at type="Microsoft.Samples.SqlTableProfileProvider" in web.config... it gives "could not load type 'Microsoft.Samples....'

any ideas?

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Saturday, January 21, 2006 12:44 PM by scottgu
Hi lightweight,

Did you save the assembly from the above provider in the \bin directory of your application? From the error message, it sounds like it is having problems finding/loading the assembly.

Thanks,

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Saturday, January 21, 2006 6:42 PM by lightweight
Now I do :), but the same error. Not sure if I did it correctly... I placed SqlTableProfileProvider.cs in the bin directory of the app (coding with VB, sqlexpress, VS2005, XP Sp2).

I only performed the steps in the doc (web.config edits and sql changes) for the table provider, so I might be missing a step that is obvious to most developers.

new to the dev side.. appreciate your help!

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Saturday, January 21, 2006 8:37 PM by lightweight
I'm trying to compile the sqltableprofileprovider.cs using csc but am unsure of the syntax. When I type csc /t:library sqltableprofileprovider.cs I receive the below error. Scouring msdn... I'll find it eventually :)

SqlTableProfileProvider.cs(75,36): error CS0103: The name 'SqlStoredProcedureProfileProvider' does not exist in the current context
SqlTableProfileProvider.cs(82,28): error CS0103: The name 'SqlStoredProcedureProfileProvider' does not exist in the current context

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Sunday, January 22, 2006 12:39 AM by lightweight
I got tired of messing with it and cheated.
Instead I put the contents of SqlStoredProcedureProfileProvider.cs at the front of SqlTableProfileProvider.cs and then performing a csc /t:library on SqlTableProfileProvider.cs ... next I added the created .dll file to the bin directory of my VS2005 web site. This worked fine and everything is up and running!

I would still be interested in hearing what the "correct" way to compile the files is.

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Monday, January 23, 2006 1:04 AM by gozh2002
Yeah, it worked for me, though you can not use User as table name and something similar to this.
The sql command generated not include a "[""]" to support reserve keyword in sql. It is still a great one and easy to use.

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Tuesday, January 24, 2006 7:04 PM by NZ Web Host
This is a great sample as the existing profile database schema doesn't allow SQL queries against it.. I'm surprised that MS didn't choose an XML field for the custom profile data.. The SqlTableProfile Provider is a great help!

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Tuesday, January 31, 2006 11:34 PM by S. Justin Gengo
Scott, getting back to gozh's orginal question and your answer from the development team about using Membership.GetUser("username") I've confirmed that this works just fine: Membership.GetUser.ProviderUserKey - Returns the logged in user's GUID.

Sincerely,

Justin

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Monday, February 6, 2006 2:21 PM by Wendy Daunheimer
Like lightweight, I'm having trouble trying to use this provider. I'm getting the same problem in web.config with the "Parser Error Message: Could not load type 'Microsoft.Samples.SqlTableProfileProvider'". I read Scott's question above about "Did you save the assembly from the above provider in the \bin directory of your application", but there is no bin directory in the .msi file given to download the code, and I don't know what is meant by "the assembly from the above provider".

I'm just a newbie trying to find an answer for this profile problem - can anyone who has downloaded this code tell me what might be wrong and maybe step by step how to fix it?

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Wednesday, February 8, 2006 2:16 AM by scottgu
Hi Wendy,

Can you send me email (scottgu@microsoft.com) describing the issue you are having? I'll then loop you in with the developer who worked on it who can help.

Thanks,

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Friday, July 28, 2006 11:25 AM by Lex Hegt
Hi Scott, I used the sample SQL script which was provided with the provider and found out that the aspnet_Users_CreateUser stored procedure is used to create users. Is there a specific reason why this one is used, instead of aspnet_Membership_CreateUser. A number of nice things (like creating a record in the aspnet_Membership-table where the passwords are stored) will not take place when that first stored procedure is called.

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Friday, July 28, 2006 9:42 PM by ScottGu

Hi Lex,

The Profile feature supports both Windows and Forms based authentication -- which is why it doesn't store users in the aspnet_Membership table.  That table is only for the Membership service -- which is typically used with Forms Auth.

Note that if you use the Profile feature in conjunction with Forms based membership, though, you should see that the user gets created in the Membership table for you as well.

Hope this helps,

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Monday, July 31, 2006 2:38 AM by Lex Hegt
Thanks Scott! I adapted the Stored Procedure to suit my needs on the aspnet_Users table, but I also need more attributes on the aspnet_Roles table. Is that possible as well? Lex

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Monday, July 31, 2006 9:38 PM by ScottGu
Hi Lex, To add additional schema properties for your Roles table you could modify the built-in Roles provider in ASP.NET. You can download this code from this site: http://msdn.microsoft.com/asp.net/downloads/providers/ Hope this helps, Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Monday, September 18, 2006 6:51 AM by Craig G
Hi Sorry to join this late but I am hoping someone can help. I am using the TableProfileProvider but get an error because the SetPropertyValues procedure cant append the firstname, lastname and age strings from my app. This is what I get when CreateUserWizard1_CreatedUser is called: Error: {"Line 1: Incorrect syntax near ','."} ?cmd.CommandText "IF EXISTS (SELECT 1 FROM ProfileTable_1 WHERE UserId = @UserId) BEGIN UPDATE ProfileTable_1 SET , LastUpdatedDate=@LastUpdatedDate WHERE UserId = '850e3a1d-fa8f-419d-8bb0-793dd348b225'END ELSE BEGIN INSERT ProfileTable_1 (UserId, LastUpdatedDate ) VALUES ('850e3a1d-fa8f-419d-8bb0-793dd348b225', @LastUpdatedDate) END" I am using sql2000, is this why the above string is not being created correctly? Any help appreciated. Thanks

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Tuesday, September 19, 2006 12:04 AM by ScottGu

Hi Craig,

If you want to send me email describing the error, I can loop you in with a dev who worked on this who can help.

Thanks,

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Tuesday, September 19, 2006 3:53 AM by Craig Gregory

Hi Scott

Thanks for the response but I have managed to get it working.

Basically I had to change the SetPropertyValues routine and comment out the areas that had been updated to cater for the Null values. I must have had something wrong somewhere because my firstname, lastname etc were not being appended to the update string.

A quick thanks for all the resources you have helped put together on this subject, I would not have gotten this far without it.

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Sunday, September 24, 2006 12:45 PM by ScottGu

Hi Bryan,

Have you looked at this recipe I did on enabling role based security: http://weblogs.asp.net/scottgu/archive/2006/07/23/Recipe_3A00_-Implementing-Role-Based-Security-with-ASP.NET-using-Windows-Authentication-and-SQL-Server.aspx

I'm not sure I entirely understand how you are using Profile with Roles.  Can you provide more information on this?

Thx!

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Thursday, September 28, 2006 7:56 PM by Dan
Scott, Is there an MS Access equivilent to this SQL Table Profile Provider? I need to use Access. thx, ciao, Dan

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Friday, September 29, 2006 12:47 AM by ScottGu

Hi Dan,

Unfortunately I'm not aware of an MS Access equivalent to the SQL Table Provider.  You could ceretainly build one yourself - but I'm not aware of a pre-built one out there.

Sorry!

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Wednesday, November 8, 2006 3:54 PM by Terrence
Scott, is there a way to have my User table in my db, but all of the the other mbership/roles etc table reside in the aspnetdb? Thanks, Terrence

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Wednesday, November 8, 2006 10:13 PM by ScottGu

Hi Terrence,

One option would be to start with the source code for this provider here: http://weblogs.asp.net/scottgu/archive/2006/10/13/Tip_2F00_Trick_3A00_-Source_2F00_Documentation-for-Simple-ASP.NET-2.0-SQL-Providers-Published.aspx

Hope this helps,

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Wednesday, December 27, 2006 3:12 PM by Rafael Spínola

That feature is really what I wanted. Not only it is much more efficient to store profile data, but it allows an easy integration with all of my data. I just created and UserID int identity column on my profile and use it as foreign key on the other tables. Awesome! Really saved the day!

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Tuesday, February 13, 2007 12:13 PM by Warren Hudson

How would I go about implementing the SqlStoredProcedureProfileProvider in a web service? I would like to expose a user's profile information via a web service based on the user name passed into the web service function, but the profile class is not accessible from a web service.

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Tuesday, February 13, 2007 2:14 PM by ScottGu

Hi Warren,

What you can do is write code like this to retrieve profile information from within a web-service:

string name = (string) HttpContext.Current.Profile.GetPropertyValue("name");

Hope this helps,

Scott

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Tuesday, February 13, 2007 2:48 PM by Warren Hudson

Thanks Scott. I was able to retrieve a specific user's profile and roles information in a web service by using the following:

Dim strUserName As String = "TheUser"

dim strProfile As String

Dim objProfile As New Web.Profile.ProfileBase

Dim arrRoles() As String

objProfile.Initialize(strUserName, True)

strProfile = CType(objProfile.GetPropertyValue("ProfileField1"), String)

arrRoles = Web.Security.Roles.GetRolesForUser(strUserName)

objProfile = nothing

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Sunday, March 4, 2007 9:54 AM by Hasan

Hello,

Thanks for mentioning this great tool.

I was trying to benefit from this tool to add custom fields to the ASP.Net 2 create user wizard control.

Adding more fields like "Address,City,Country...etc" and this is what I came up with:

protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)

   {

       Profile.Name = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtName")).Text;

       Profile.CountryID = ((DropDownList)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("drplstCountry")).Text;

       Profile.City = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtCity")).Text;

       Profile.Address = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtAddress")).Text;

       Profile.Phone = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtPhone")).Text;

       Profile.Fax = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtFax")).Text;

       Profile.PostalCode = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtPostalCode")).Text;

       Profile.Job = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtJob")).Text;

       Profile.DOB = ((Calendar)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("calendarDOB")).SelectedDate.ToShortDateString();

       Profile.Save();

   }

It seems to be the user is created, but later on when I was trying to retrieve the custom profile data like Profile.Name, it comes back with empty result! Further investigating I found out that the UserID which is being inserting in the Custom Profile table is different from the one in asp_Users table!

I tried to create a new user, then I found out another thing, every time I create a new user it is created normally in the asp_users table but in the Custom Profile table it just replace the old values of a user!

What am I doing wrong?

Thanks.

# re: ASP.NET 2.0 SQL Table Profile Provider Released Today

Monday, March 5, 2007 2:14 AM by Hasan

Hey;),

I found out what was wrong.

In the Create User Wizard, the Profile is as an anonymous user! So when I save the profile it actually saves it as anonymous not as the new user's profile.

The solution was to do this:

ProfileCommon createdUserProfile = Profile.GetProfile(((CreateUserWizard)sender).UserName);

createdUserProfile.Name = ((TextBox)CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("txtName")).Text;

... and so on.

So the ProfileCommon can get me the Profile for the created user.

Thanks.