ASP.NET 2.0 SQL Table Profile Provider Released Today

32 Comments

• 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.
  

• 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?
  

• 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. :(
  

• 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
  

• 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?
  

• 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
  

• 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!
  

• 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
  
  
  
  

• 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.
  

• 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.
  

• 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!
  

• 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
  

• 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?
  

• 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
  

• 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.
  

• 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
  

• 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
  

• 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
  

• 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
  

• 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
  

• 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.
  

• 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!
  

• Scott,
  Is there an MS Access equivilent to this SQL Table Profile Provider? I need to use Access.
  thx,
  ciao, Dan
  

• 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
  

• 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
  

• 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
  

• 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!
  

• 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.
  

• 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
  

• 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
  

• 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.
  

• 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.