Querying Profile Values from default Profile Provider

Hey all,

As most of you know if you've been involved with the new Membership features of .NET 2.0, there's a handy way to store user specific settings called the Profile.   This allows you to specify in your config files certain attributes you wish to store on a per user basis.   At compile time, the .NET compiler will adjust its native profile object to account for the profile properties you configure so that you can access them directly with code.

Let's assume that you are going to store First Name and Last Name on the user profile.  Your configuration would look like this:

<system.web>
  <profile>
    <properties>
    <add name="FirstName" type="System.String"/>
    <add name="LastName" type="System.String"/>
  </properties>
</system.web>

Once that was done, you could reference those properties directly off of your code:

protected void Page_Load(object sender, EventArgs e)
{ 
      Profile.FirstName = 'John'; 
      Profile.LastName = 'Smith';
}

However, there's a small problem with doing this, and it's in how the values are stored in the data store that you are using for your Membership and Profile providers.   The names and values of the profile properties are stored in a formatted string format like so:

 The propertynames field:

FirstName:S:0:4:LastName:S:4:5

The propertyvalues field:

JohnSmith

In essence, the propertynames field contains the field name, the datatype, the index, and the length of each field as it exists in the propertyvalues field.

This makes it very difficult to pull data out with simple Sql.

There are several alternatives, including using a SqlTableProvider (found at http://www.asp.net/sandbox/samp_profiles.aspx?tabindex=0&tabid=1), and those are useful if you are looking to do a full blown search mechanism.  But what if you don't want to replace your entire provider model and do a conversion, and only want to see what data you have?

One thing you can do is construct a user-defined Sql function to parse the propertynames field, and then use that parsing to do an appropriate substring on the propertyvalues field.  Here's an example.

I'm going to build a user-defined function that takes the text I wish to parse, the field name I am looking for, and the indexed value I am after.  In this example, if I pass in an indexed value of '2' I will get the datatype, '3' will return me the start position, and '4' will get the field length.

CREATE FUNCTION [dbo].[GetProfileParameters](@text varchar(8000), @searchKey varchar(8000), @returnIndex int)
RETURNS varchar(8000)
AS
BEGIN

declare @keyfound bit

set @keyfound = 0

DECLARE @pos smallint,
@index smallint,
@i smallint,
@j smallint,
@s varchar(8000)

SET @pos = 1
set @index = 1

WHILE @pos <= LEN(@text)

BEGIN

       SET @i = CHARINDEX(' ', @text, @pos)
       SET @j = CHARINDEX(':', @text, @pos)

       IF @i > 0 OR @j > 0
       BEGIN
                   IF @i = 0 OR (@j > 0 AND @j < @i)
                   SET @i = @j

                             IF @i > @pos
                             BEGIN
                                          -- @i now holds the earliest delimiter in the string
                                          SET @s = SUBSTRING(@text, @pos, @i - @pos)

                                          if (@s = @searchKey)
                                          begin
                                                --found the key
                                                set @keyfound = 1
                                          end

                                          if (@keyfound = 1 and @index = @returnIndex)
                                          begin
                                                    RETURN @s
                                          end

                                set @index = @index + 1
                                if (@index > 4)
                                         set @index = 1
                     END

                               SET @pos = @i + 1
                               WHILE @pos < LEN(@text) AND SUBSTRING(@text, @pos, 1) IN (' ', ':')
                               SET @pos = @pos + 1
                   END
          ELSE
                   BEGIN

                              if (@s = @searchKey)
                              begin
                                        --found the key
                                        set @keyfound = 1
                              end

                              if (@keyfound = 1 and @index = @returnIndex)
                              begin
                                        RETURN @s
                              end

                              set @index = @index + 1
                              if (@index > 4)
                                        set @index = 1

                              SET @pos = LEN(@text) + 1
                 END
          END
    RETURN ''
end

Now that we have that, we can get the start index and length of the field we want and get it out of the table by using a substring:

 

select substring(propertyvaluesstring, convert(int, dbo.getprofileparameters(propertynames, 'FirstName', 3)) + 1, convert(int, dbo.getprofileparameters(propertynames, 'FirstName', 4))) as FieldValue from aspnet_Profile

That will give us all of the first names in the user profiles by doing an appropriate substring on the propertyvalues field, using the data provided in the propertynames field.

Happy querying!

 

Rob