Rob Hope

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

If you are ever in a scenario where you are going to be doing tons of repetitive inserts, consider using the new SqlBulkCopy command that’s found in .NET 2.0.   It uses an insert bulk to do all of your inserts in a single round trip, and has settings which you can use to batch up or break up the inserts if needed, and a timeout setting as well.  The potential performance savings are huge, especially if you are doing things like importing data from outside vendors or processing data submissions from third parties via FTP or web services.

Here’s an example of how it’s used.

First you would create a bulk copy object given the connection string.

SqlBulkCopy oBulk = new SqlBulkCopy(yourconnectionstring);

Then you would create a datatable to import…in this case I’ll call some DB method
DataTable dtBulk = GetAllZipsOutMyDatabase();

Next set the destination table name where you want the bulk copy to go:

oBulk.DestinationTableName = “MyDestinationTable”

Now this next step is important, IF the columns in your datatable do not exactly match the column structure in the destination table.  If your local datatable is structured exactly like the destination table, meaning all of the columns are represented and they are the correct datatype, you can do without this step.  However, if your table has an identity column, or some columns are not represented, you MUST map your columns to the destination table.  In this example I am using the names of the local datatable columns and mapping them onto the destination table, since I named my local datatable columns the same as they are in the destination table.

oBulk.ColumnMappings.Clear();
foreach(DataColumn oCol in dtBulk.Columns)

{
         oBulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(oCol.ColumnName, oCol.ColumnName));

}

Now that that’s done I can set the batch size and the timeout:

oBulk.BatchSize = 1000;
oBulk.BulkCopyTimeout = 6000;

Now I’m all set up and I can write to the server.

oBulk.WriteToServer(dtBulk);

And that’s it.  Rather than looping on a bunch of inserts, I can do all of the inserts in a single round trip.

Happy bulk copying,

Rob