Skip to main content

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

Comments

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

Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.UserProfileContains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.webpages_MembershipStores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.webpages_OAuthMembershipStor…

JavaScript function to automatically add slashes to date

In converting an old Windows app to a browser app, the user wanted to be able to enter dates without the slashes. Here's a simple jscript: 1:// Function to convert short date string (MMddyy) 2:// or (MMddyyyy) to a date string (mm/dd/yyyy). 3:// txtBox is the actual textbox control 4:// with the value to be processed. 5:function FixShortDate(txtBox) { 6:if (txtBox == null) { 7:return'' } 8: 9:var re = new RegExp(/(\d{6})(\d{2})?/); 10: 11:if (re.test(txtBox.value)) 12: { 13:if (txtBox.value.length == 8) { 14: txtBox.value = txtBox.value.substring(0, 2) + '/' + txtBox.value.substring(2, 4) + '/' + txtBox.value.substring(4, 8) 15: } 16: 17:if (txtBox.value.length == 6) { 18:if (txtBox.value.substring(4, 6) < 20)