Get Asp.Net Profile properties from Sql
Ever wanted to include the profile information from an Asp.Net profile in a query? It’s not that hard once you understand the structure. I’ve written a little function that does all the work. Note: I’m using Sql Server as my repository.
First we need to understand how the profile data is stored. Looking at the aspnet_Profile table, we can see that it stores the information in two columns: PropertyNames and PropertyValuesString.
Looking at PropertyNames we can see that it has a basic structure of Property Name, Data Type, Starting Position and Length. For example, in the string “FirstName:S:0:4:Phone:S:4:10:LastName:S:14:5:” we can see that FirstName is of type string, starts at position 0 and has a length of 4. Notice the zero base for the starting position, we need to correct for that in our function. This means in the PropertyValuesString “John2175551212Smith”, we would start with the first position and proceed 4 characters to get the name.
You might be thinking you can simply use Right(PropertyValuesString, 4) to get all of the first names. Unfortunately, no. First, there are a lot of names longer than 4 so we know that length is an issue. Second, neither PropertyNames or PropertyValuesString are consistently ordered. You might see the FirstName as the start for one while the next one has the Phone and the next LastName.
The following Sql function finds the property we’re looking for, gets the starting position and length and then returns the value as a varchar.
-- ============================================= -- Create date: 09/01/2006 -- Description: Gets the property value from -- the Asp.Net profile. -- @PropertyName – The property to -- be found. -- @PropertyNamesString – The -- property names information. -- @PropertyValuesString – The -- property values information -- ============================================= CREATE FUNCTION [dbo].[GetProfilePropertyValue] ( @PropertyName as varchar(max) , @PropertyNamesString as varchar(max) , @PropertyValuesString as varchar(max)) RETURNS varchar(max) AS BEGIN DECLARE @StartIndex int DECLARE @EndIndex int DECLARE @StartPos int DECLARE @Length int -- First we find the starting position Set @StartIndex = PatIndex('%' + @PropertyName + ':%', @PropertyNamesString) + LEN(RTRIM(@PropertyName)) + 3 Set @EndIndex = PatIndex('%:%', Right(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex)) Set @StartPos = Cast(Substring(@PropertyNamesString, @StartIndex, @EndIndex) As Int) -- Now we need to know how long it is Set @StartIndex = @StartIndex + @EndIndex + 1 Set @EndIndex = PatIndex('%:%', Right(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex)) Set @Length = Cast(Substring(@PropertyNamesString, @StartIndex, @EndIndex) As int) -- Now we get the value we want RETURN SUBSTRING(@PropertyValuesString, @StartPos + 1, @Length) END
That was easy, now all we need to do is run a query that gets the info.
SELECT dbo.GetProfilePropertyValue('LastName', PropertyNames, PropertyValuesString) , dbo.GetProfilePropertyValue('FirstName', PropertyNames, PropertyValuesString) , dbo.GetProfilePropertyValue('Phone', PropertyNames, PropertyValuesString) FROM aspnet_Profile
Joining with the aspnet_Users on UserID will give you the user name and email.