Sitecore Custom User Profile - where is it stored how can it be queried

Question

I have created a custom User profile template and object in the core database in Sitecore (as per the Security API Cookbook).

I can select this programmatically (as per the Security API Cookbook) so that my extranet users have an extended profile, that covers all the usual suspects (Address, phone, email format etc.)

However, where is this data stored? And how do I access it if I want to query the database to return a subset of users based on this profile data.

A typical requirement for an extranet member system is to extract a list of users to contact either in an email or a phone type campaign. Can this be done with the Sitecore membership system?

UPDATE> I'm going to take a guess and say the profile data is stored in aspnet_Profile.PropertyValuesBinary .. which would make it nigh on impossible to query and not suited to my purpose. That is unfortunate. So to extend my question, if that is the case, is it possible to get Sitecore to store those values in the text field so they are searchable?

Abott

Answer 1

The standard Microsoft implementation of the SqlProfileProvider (which is used in Sitecore by default) stores the user profile information in the aspnet_Profile table. All the properties are serialized into the PropertyNames / PropertyValuesString columns. The PropertyValuesBinary is used to store the binary data (images). You can find more details if you look at the code of System.Web.Profile.SqlProfileProvider, SetPropertyValues method.

Next, all the custom properties you define in the user profile, are serialized to the SerializedData property of the Profile class, and it is again serialized to the PropertyNames / PropertyValuesString columns like any other property.

Also, couple of properties are stored in aspnet_Membership table (for some reason) - Email and Comment.

So, if you are going to query the users by Email, you can use FindUsersByEmail method of MembershipProvider. Otherwise, if you plan to filter by another property value, I suppose, you'll have to get all users and filter the obtained collection.

Hope this helps.

Willms

thanks. I suppose I'm looking for information on how Sitecore has extended the SqlProfileProvider.. it seems however it is in exactly this way.. I have found previously that the method you use to store the data defines which field (String/Binary) the property value ends up in .. SetPropertyValue(string key, object value) puts it in the Binary field while SetCustomProperty(string key, string value) puts it in the string field.. I still think its a #fail to both Microsoft and Sitecore for implementing a profiling solution that is about as query-able as dead dog. – Abott

Answer 2

I faced this exact problem last week, didn't come up with a permanent solution, but to solve my particular issue, I wrote a little helper page and added it as a Sitecore application to be accessed from the CMS interface. All it did was query all users, and determine if they had any of like 5-6 profile properties assigned.

var userList = Sitecore.Security.Accounts.UserManager.GetUsers();

That is the relevant line to grab the users, it returns Sitecore.Common.IFilterable

So if you need to do something where you're grabbing profile info from all users, you cn do something like this:

foreach (Sitecore.Security.Accounts.User user in userList)
{
    Sitecore.Security.UserProfile profile = user.Profile;
    string whatever = profile["Whatever"];
    //add whatever to a list or something
}

This worked out very well for my purposes, but I don't know how feasible it will be in your situation.

Flack

yeah getting the entire user list is probably only good if you have a few users. I'm planning for thousands, probably tens of thousands.. although this method works, "how feasible it will be in your solution" is the big question. but cheers! – Abott

Awesome - exactly what I needed for report purposes to extract some of the profile info out for further processing. – Mauricio