ASP.NET 2.0: How SqlProfileProvider Stores its Data...


This probably isn't entirely correct since I am just looking at the output (e.g. the database rows) to determine this.  For each object in profile (i.e. Users), there is a single row in the aspnet_Profile table.  This table is made up like so:

CREATE TABLE [aspnet_Profile] (
  [UserId] [uniqueidentifier] NOT NULL,
  [PropertyNames] [ntext] NOT NULL ,
  [PropertyValuesString] [ntext] NOT NULL ,
  [PropertyValuesBinary] [image] NOT NULL ,
  [LastUpdatedDate] [datetime] NOT NULL )

The three middle columns are used to store the properties and their values.  PropertyNames stores a list of properties with hints on how to retrieve it from the String or Binary column.  For example:

IsClient:S:0:4:Parent:B:0:-1:LogoUrl:S:4:39:Expired:B:0:-1:Row:B:0:-1:CssUrl:S:43:28:Services:S:71:239:

Each section of this string is the metadata about the property:

  • First is the name of a property.  This needs to map directly to the property names in the web.config file. 
  • Then the place its to be stored (S = String, B = Binary).  Note that the placement in String or Binary form is based on the datatype or the serialization format.  XML Serialization ends up in Strings and binary serialization ends up in the binary area.  It seems that numbers also end up in binary format.
  • Start Position.  For a string it's where in the string to start looking for that value.  Understand that this will change as the size of the property changes.  Using -1 in a Binary stored property seems to indicate that it is not stored at all.  For example, the IsClient is a Boolean, but is stored as a string here, but Expired is also a boolean, but since it has a -1 we know it isn't actually stored.
  • Length.  If the start position is -1, there will be no length.  Otherwise this is used to substring out the actual data in the string and binary fields.

As you would expect, the string field may be a mix of XML serialized data and string data.  For example, the string field here is stored like so:

True//wilderminds.blob.core.windows.net/img/adoguylogo.gifhttp://wildermuth.com/adoguy.css
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <string>Cell Manager</string>
  <string>Cell Manager</string>
</ArrayOfString>

You'll notice that the first four characters are the word "True" which corresponds to the PropertyNames "IsClient:S:0:4:".  Likewise I have a property called Services which is the XML Serialized piece at the end that corresponds to "Services:S:71:239:".

So far I like the profile stuff and luckily it is using the common provider model because the one glaring weakness here is searching.  Finding all Users with a specific Property value is going to be very very difficult.  My plan is to write my own provider that is a specialization of the SqlProfileProvider but normalizing the data into a PropertyName and Value table.  I have not dug deep into the ramifications as it has to do with Groups or custom serialized types, but I don't think they will get in the way.

If anyone has any comment on this, I am very open to hear what you have to say.



Shawn
Shawn Wildermuth
Author, Teacher, and Coach




My Courses

Wilder Minds Training
Vue.js by Example (New Lower Price)
Bootstrap 4 by Example (New Lower Price)
Intro to Font Awesome 5 (Free Course)
Pluralsight
Building an API with ASP.NET Core (New Course)
Building a Web App with ASP.NET Core, MVC6, EF Core, Bootstrap and Angular (updated for 2.2)
Less: Getting Started (New)
Using Visual Studio Code for ASP.NET Core Projects
Implementing ASP.NET Web API

Application Name WilderBlog Environment Name Production
Application Ver v4.0.30319 Runtime Framework x86
App Path D:\home\site\wwwroot\ Runtime Version .NET Core 4.6.27817.01
Operating System Microsoft Windows 10.0.14393 Runtime Arch X86