Tuesday, March 20, 2007

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.
Enjoy.

Technorati Tags: ,,

Subscribe in a reader

12 comments:

PretzelSteelersFan said...

Creating a view using the function is a great way to get a "table" of the user information.

Hiren said...

Thaks

Anonymous said...

I know this post is old but I thought I'd publish the function I made with your help. The changes I made was that the input is the UserID and the name of the property you're interested in. I also added a check for invalid length because of null values. Thanks a lot for the post!

-- =============================================
-- Author: Jay Hiilden
-- Create date: 09-NOV-2012
-- Description: the idea was stolen from http://pretzelsteelersfan.blogspot.com/2007/03/get-aspnet-profile-properties-from-sql.html and tweaked.
-- =============================================
CREATE FUNCTION [dbo].[f_GetUserProfileValue]
(
@UserID uniqueidentifier,
@PropertyName varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @PropertyNamesString as varchar(max),
@PropertyValuesString as varchar(max),
@StartIndex int,
@EndIndex int,
@StartPos int,
@Length int;

SELECT @PropertyNamesString = p.PropertyNames,
@PropertyValuesString = p.PropertyValuesString
FROM aspnet_Profile p
WHERE p.UserId = @UserID;



-- 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)

if (@Length < 1)
BEGIN
RETURN NULL;
END;

-- Now we get the value we want
RETURN SUBSTRING(@PropertyValuesString, @StartPos + 1, @Length);
END

PretzelSteelersFan said...

@Jay Glad to see it was useful!!!

David D said...

Nice function. Thanks. Im puzzled why it wont work in SQL server v11 (2012 Express)

Invalid length parameter passed to the RIGHT function.

RedMouse said...

Hi..it's nice post.really help me.

but when i am trying with.

,dbo.GetProfilePropertyValue('Phone', PropertyNames, PropertyValuesString) as InboundIncrease.

it's showing me error.

"Invalid length parameter passed to the LEFT or SUBSTRING function."

also, is there any way to get all property value?

Unknown said...

Excellent! Just what I was looking for.

Thanks!!!

Anonymous said...

Thanks a bunch!

Dana said...

Thanks for posting this, very helpful!

But there's an error if PropertyName is not found. To fix, replace this line:

Set @StartIndex = PatIndex('%' + @PropertyName + ':%', @PropertyNamesString) + LEN(RTRIM(@PropertyName)) + 3

With this:

Set @StartIndex = PatIndex('%' + @PropertyName + ':%', @PropertyNamesString)

IF @StartIndex = 0
RETURN NULL

Set @StartIndex = @StartIndex + LEN(RTRIM(@PropertyName)) + 3

Guillaume I Ska said...

Hi,

Thanks for the script, I've added
- The Application parameter (in the aspnet_Profile, we have 2 columns for the PK)
- Some check on @StartPos & @Length
- Type change to keep same metadata as aspnet_Profile

------

CREATE FUNCTION [dbo].[f_GetProfileValue]
(
@UserID UNIQUEIDENTIFIER,
@ApplicationID UNIQUEIDENTIFIER,
@PropertyName NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE
@PropertyNamesString AS NVARCHAR(MAX),
@PropertyValuesString AS NVARCHAR(MAX),
@StartIndex INT,
@EndIndex INT,
@StartPos INT,
@Length INT

SELECT
@PropertyNamesString = p.PropertyNames,
@PropertyValuesString = p.PropertyValuesString
FROM
aspnet_Profile p
WHERE
p.UserId = @UserID AND
p.ApplicationId = @ApplicationID

-- First we find the starting position
SET @StartIndex = PATINDEX('%' + @PropertyName + ':%', @PropertyNamesString) + LEN(RTRIM(@PropertyName)) + 3
SET @EndIndex = PATINDEX('%:%', RIGHT(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex))
IF ISNUMERIC(SUBSTRING(@PropertyNamesString, @StartIndex, @EndIndex)) = 1
BEGIN
SET @StartPos = CAST(SUBSTRING(@PropertyNamesString, @StartIndex, @EndIndex) AS INT)
END
ELSE
BEGIN
RETURN NULL;
END

-- Now we need to know how long it is
SET @StartIndex = @StartIndex + @EndIndex + 1
SET @EndIndex = PATINDEX('%:%', RIGHT(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex))
IF ISNUMERIC(SUBSTRING(@PropertyNamesString, @StartIndex, @EndIndex)) = 1
BEGIN
SET @Length = CAST(SUBSTRING(@PropertyNamesString, @StartIndex, @EndIndex) AS INT)
END
ELSE
BEGIN
RETURN NULL;
END

-- Security Check
IF (@Length < 1)
BEGIN
RETURN NULL;
END;

-- Now we get the value we want
RETURN SUBSTRING(@PropertyValuesString, @StartPos + 1, @Length);

END

Moch Yusup Soleh said...

Please insert the code below

IF @Length < 1
RETURN NULL;

before

-- Now we get the value we want
RETURN SUBSTRING(@PropertyValuesString, @StartPos + 1, @Length)

To prevent error 'Invalid length parameter passed to the LEFT or SUBSTRING function.'

Anonymous said...

I might be wrong, but
it would seem logical to remove the UserId argument altogether and instead pass the property bag names column and the property values column as arguments to the function. It would seem with that function SQL server has to fetch a row from aspnet_Profile for each function call. I would say in most cases you are calling that function from a query which has aspNet_Profile joined with some other table where UserId is stored as a foreign key.
I would say you just need to declare function as:
CREATE FUNCTION [dbo].[GetProfilePropertyValueFromRow]
(
@PropertyName NVARCHAR(MAX),
@PropertyNamesString NVARCHAR(MAX),
@PropertyValuesString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
-- etc
then just remove the DECLARE @PropertyNamesString and @PropertyValuesString lines.
Sample query with aforesaid function:

SELECT t1.OrderId,t1.OrderDate, t1.UserId, dbo.GetProfilePropertyValueFromRow ('FistName',p.PropertyNames,p.PropertyValuesString) AS FirstName,dbo.GetProfilePropertyValueFromRow ('LastName',p.PropertyNames,p.PropertyValuesString)
AS LastName, ('CompanyName',p.PropertyNames,p.PropertyValuesString) AS CompanyName
FROM AnyTableWithUserIdFK t1
INNER JOIN aspnet_Profile p
ON t1.UserId=p.UserId