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

Paul G Brown said…
Creating a view using the function is a great way to get a "table" of the user information.
September 19, 2007 at 12:18 PM
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
November 9, 2012 at 12:40 PM
Paul G Brown said…
@Jay Glad to see it was useful!!!
November 9, 2012 at 3:16 PM
Warren Dodsworth said…
Nice function. Thanks. Im puzzled why it wont work in SQL server v11 (2012 Express)

Invalid length parameter passed to the RIGHT function.
January 12, 2013 at 8:56 AM
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?
August 15, 2013 at 12:49 AM
Unknown said…
Excellent! Just what I was looking for.

Thanks!!!
November 6, 2013 at 9:15 PM
Anonymous said…
Thanks a bunch!
November 13, 2013 at 9:43 AM
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
November 13, 2013 at 4:01 PM
Unknown 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
February 26, 2014 at 11:14 AM
Unknown 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.'
September 4, 2014 at 9:03 PM
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
February 5, 2015 at 5:33 AM
Post a Comment

Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

-
Image
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. UserProfile Contains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table. webpages_Membership Stores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table. webpage...
Read more

Windows 8 Keyboard Shortcuts

-
Image
There are numerous posts on the keyboard shortcuts in Windows 8. I’m not going to regurgitate those. Here are the common things you’ll want to do. Shut Your Computer Off, Network / Wi-Fi, Volume, Keyboard Press Windows+I which brings up the Settings bar (below left). Search, Change Application Settings, Manage Devices Windows+C which brings up the Charms bar (above right). Want to find a song or artist in Music, use The rest of the story If you want to read about other short-cuts and print a handy-dandy cheat sheet, visit the Windows Experience Blog . IE 10 Want to get the most out of IE10, then How-To Geek’s The Best Tips and Tricks for Getting the Most out of Internet Explorer 10 is exactly what you need. There’s also Internet Explorer 10 Shortcut Keys In Windows 8 which has some Windows 8 stuff also.
Read more