How to get asp.net profile value from MS SQL database using T-SQL?

The aspnet_Profile table contains the following fields: UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary,  and LastUpdatedDate. The PropertyNames field contains a string delimited with colons (:) that identify which profile fields are stored, what their datatype is and their offset and length.

For Example:

FirstName:S:1:7:PostalCode:S:8:5:Street:S:13:15:LastName:S:28:7:

Actual values stored in PropertyValuesString:

Viktar 601481336 Finley rd Karpach

ASP.Net profiles can store binary data as well, but usually your are interested in string data such as First and Last names. First lets create helper function, which helps to get position:length pair values:

CREATE FUNCTION dbo.fn_GetElement

(

@ord AS INT,

@str AS VARCHAR(8000),

@delim AS VARCHAR(1) )

 

RETURNS INT

AS

BEGIN

  -- If input is invalid, return null.

  IF @str IS NULL

      OR LEN(@str) = 0

      OR @ord IS NULL

      OR @ord < 1

      -- @ord > [is the] expression that calculates the number of elements.

      OR @ord > LEN(@str) - LEN(REPLACE(@str, @delim, '')) + 1

    RETURN NULL

  DECLARE @pos AS INT, @curord AS INT

  SELECT @pos = 1, @curord = 1

  -- Find next element's start position and increment index.

  WHILE @curord < @ord

    SELECT

      @pos    = CHARINDEX(@delim, @str, @pos) + 1,

      @curord = @curord + 1

  RETURN

  CAST(SUBSTRING(@str, @pos, CHARINDEX(@delim, @str + @delim, @pos) - @pos) AS INT)

END

 

And then code for the actual worker function:

CREATE FUNCTION dbo.fn_GetProfileElement

(

@fieldName AS NVARCHAR(100),

@fields AS NVARCHAR(4000),

@values AS NVARCHAR(4000))

 

RETURNS NVARCHAR(4000)

AS

BEGIN

  -- If input is invalid, return null.

  IF @fieldName IS NULL

      OR LEN(@fieldName) = 0

      OR @fields IS NULL

      OR LEN(@fields) = 0

      OR @values IS NULL

      OR LEN(@values) = 0

 

    RETURN NULL

 

-- locate FieldName in Fields

DECLARE @fieldNameToken AS NVARCHAR(20)

DECLARE @fieldNameStart AS INTEGER,

@valueStart AS INTEGER,

@valueLength AS INTEGER

 

-- Only handle string type fields (:S:)

SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)

 

-- If field is not found, return null

IF @fieldNameStart = 0 RETURN NULL

SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3

 

-- Get the field token which I've defined as the start of the

-- field offset to the end of the length

SET @fieldNameToken = SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)

 

-- Get the values for the offset and length

SET @valueStart = dbo.fn_getelement(1,@fieldNameToken,':')

SET @valueLength = dbo.fn_getelement(2,@fieldNameToken,':')

 

-- Check for sane values, 0 length means the profile item was

-- stored, just no data

IF @valueLength = 0 RETURN ''

 

-- Return the string

RETURN SUBSTRING(@values, @valueStart+1, @valueLength)

 

END

 

Now we can get first name and last name as following:

 

SELECT dbo.fn_GetProfileElement('FirstName',PropertyNames,PropertyValuesString) + ' ' +

 

dbo.fn_GetProfileElement('LastName',PropertyNames,PropertyValuesString) as FullName FROM aspnet_Profile

 

 Special thanks to the Richard Edwards at DNNStuff


Posted on Sunday, June 29, 2008 by | Comments (31) | Add Comment

Comments

Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Very nice! That's exactly what I was looking for.

Posted on 7/21/2008 7:58:15 AM by Valera #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Thanks!!!.. =) is a very good !!

Posted on 8/5/2008 9:36:16 AM by Augusto #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Thanks ....Very Very nice

Posted on 8/13/2008 3:36:27 AM by ThanhTung #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Thanks!

Posted on 9/24/2008 8:47:38 AM by BobC #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Thank You! I've been busting my brains out trying to get a dll that performs the same functions as ProfileCommon for a console application. No luck there. And I didn't want to program an interface. This was a great shortcut!

Posted on 2/10/2009 7:57:14 PM by Nick S. #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Thank you! It's cool (_)]

Posted on 2/18/2009 12:31:25 AM by telz #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Great blog
Thanks a lot, very useful

Posted on 6/5/2009 3:42:45 AM by Wallace740 #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

A very lucky day for me!
I found such a useful script!

Posted on 6/5/2009 1:54:51 PM by Atip C #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Here's another flavor of it that I made. Just uses a single function.

CREATE FUNCTION [dbo].[GetProperty]
(
@UserId uniqueidentifier,
@PropertyName varchar(50)
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @ret varchar(500)
DECLARE @PropPos int, @Names varchar(500), @Values varchar(500), @start tinyint, @length tinyint

SELECT @Names = PropertyNames, @Values = PropertyValuesString FROM aspnet_Profile WHERE UserId = @UserId
SELECT @PropPos = (PATINDEX('%'+ @PropertyName +':S%', @Names) + LEN(@PropertyName) + 3)
SELECT @Names = SUBSTRING(@Names, @PropPos, LEN(@Names) - @PropPos)
SELECT @start = CAST(SUBSTRING(@Names, 1, CHARINDEX(':', @Names) - 1) AS tinyint) + 1
SELECT @length = CAST(SUBSTRING(@Names, CHARINDEX(':', @Names) + 1, ((CHARINDEX(':', @Names, CHARINDEX(':', @Names) + 1)) - (CHARINDEX(':', @Names) + 1))) AS tinyint)
SELECT @ret = SUBSTRING(@Values, @start, @length)

RETURN @ret

END

Posted on 6/15/2009 7:58:02 AM by Ash #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Thank you! This is exactly what I was looking for.

Posted on 5/15/2010 7:56:01 AM by Gabor Adam #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Brilliant. Thanks a lot. Atip C single function worked for me too.

Posted on 5/25/2010 8:06:54 AM by nrg #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Actually, Atip C single function didn't work for me on all occassions. I reverted back to the two and that's working fine.

Posted on 5/26/2010 8:05:26 AM by nrg #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Well Atip C's solution works perfectly. Just the job. Remember to call as follows:

dbo.GetProperty(CustomerFeedback.CustomerID, 'LastName')

where 'LastName' is the property and is enclosed in quotes

Posted on 7/15/2010 9:18:05 PM by Phil #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

The original solution is fantastic but there is one problem that is easily resolved.

The problem arises if you have two fields similarly named. For example 'City' and 'DeliverCity'. In these cases the code will always win through with CHARINDEX finding the first match in the list of properties.

To resolve, simply get CHARINDEX to look for the preceeding character ':' so fieldName becomes

SET @fieldName = ':' + @fieldName + ':S'
SET @fieldNameStart = CHARINDEX((@fieldName),@fields,0)

As a result of the extra character in @fieldName, also change

SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 1

And that's it.

Posted on 9/16/2010 5:13:57 AM by Phil #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3
--New lines
IF CHARINDEX(@fieldName + ':S',@Fields,0) > 1
BEGIN
SET @fieldName = ':' + @fieldName + ':S'
SET @fieldNameStart = CHARINDEX((@fieldName),@fields,0)
IF @fieldNameStart = 0 RETURN NULL
SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 1
END
--end

Posted on 10/8/2010 7:35:56 AM by Murat YAVUZ #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Awesome, thanks so much.

Posted on 11/3/2010 1:18:02 PM by Andrew #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

OK, here's the function version based on everyone's work above but with the 2 issues fixed. This will return the value of the last property name in PropertyNames (the initial example above would throw an error). It also fixes the "similiar" names issue mentioned by Phil above. Also, if a PropertyName does not exist, it returns NULL - in which case the caller can search for and return the Default property value as defined in web.config (if a default has been defined).

I'm standing on the shoulders of giants (!) thanks to all the great work above :-)

CREATE FUNCTION [dbo].[GetProperty] (
@UserId UNIQUEIDENTIFIER,
@PropertyName VARCHAR(50)
)
RETURNS VARCHAR(500)
AS
BEGIN

DECLARE @RetVal VARCHAR(500)
DECLARE @PropPos INT, @Names VARCHAR(500), @Values VARCHAR(500), @start TINYINT, @length TINYINT

-- Note how we prefix the first property name with a colon. Now we know that EVERY property name will be bookended with colons:
SELECT @Names = ':' + CAST(PropertyNames AS VARCHAR(500)), @Values = PropertyValuesString FROM aspnet_Profile WHERE UserId = @UserId

SELECT @PropPos = PATINDEX('%:'+ @PropertyName +':S%', @Names)

IF @PropPos = 0
BEGIN
SET @RetVal = NULL
END
ELSE
BEGIN
SET @PropPos = @PropPos + LEN(@PropertyName) + 4
SELECT @Names = SUBSTRING(@Names, @PropPos, LEN(@Names))
SELECT @start = CAST(SUBSTRING(@Names, 1, CHARINDEX(':', @Names) - 1) AS tinyint) + 1
SELECT @length = CAST(SUBSTRING(@Names, CHARINDEX(':', @Names) + 1, ((CHARINDEX(':', @Names, CHARINDEX(':', @Names) + 1)) - (CHARINDEX(':', @Names) + 1))) AS tinyint)
SELECT @RetVal = SUBSTRING(@Values, @start, @length)
END

RETURN @RetVal
END

I'm sure this could be made more efficient. Feel free to improve!

Posted on 11/5/2010 3:57:22 PM by PapillonUK #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Nice post.helped me a lot

Posted on 11/11/2010 2:13:22 PM by Mohammad Iqbal #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

Thanks a lot..
It is really an helping hand form me.

Posted on 11/28/2010 11:13:42 PM by Waseem Ahmad #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

if (dropCurrency.SelectedValue == "1")
{
float val1 = Convert.ToInt32(dt.Rows[0]["hol_usdollar"].ToString());
float val2 = Convert.ToInt32(dt2.Rows[1]["hol_productprice"].ToString());
float val3 = (val1 * val2);
lblp.Text = val3.ToString();
lblLogo.Text = "$";
}

Posted on 12/30/2010 1:04:16 AM by Rajeesh #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

/*Thanks for the code - it has really helped. Here is a C# interpretation (quick hack - it should have more error checking but it works)*/

private String getValue(String Names, String Values, String Property)
{
String retVal = null;

String[] Properties = Names.Split(':');
for (int i = 0; i < Properties.Length; i++)
{
if (Properties[i].CompareTo(Property) == 0)
{
if (Properties[i + 1] == "S") // Not sure what happens for non string properties
{
int start = Int32.Parse(Properties[i + 2]);
int len = Int32.Parse(Properties[i + 3]);
if (len > 0)
{
retVal = Values.Substring(start, len);
}
}
break;
}
}
return retVal;
}
public String GetProfileProperty(String userid, String Property)
{
String RetVal = null;
String Names,Values;

//Note how we prefix the first property name with a colon. Now we know that EVERY property name will be bookended with colons:
String SQL = "SELECT ':' + CAST(PropertyNames AS VARCHAR(500)), PropertyValuesString FROM aspnet_Profile WHERE UserId = @UserId";
SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
SqlCommand cmd = new SqlCommand(SQL, conn);
cmd.CommandType = System.Data.CommandType.Text;

using (conn)
{
//open the connection
conn.Open();
cmd.Parameters.AddWithValue("@UserId", userid);
SqlDataReader r = cmd.ExecuteReader();
if (r.Read())
{
Names = r[0].ToString();
Values = r[1].ToString();
RetVal = getValue(Names, Values, Property);
}
r.Close();
}
return RetVal;
}

Posted on 5/8/2011 8:00:00 AM by Daniel Armstrong #
Gravatar

Re:How to get asp.net profile value from MS SQL database using T-SQL?

create or replace
FUNCTION SR_ORA_ASPNET_PROF_GETPROPERTY (
paramUserID RAW,
paramPropertyName VARCHAR2
)
RETURN VARCHAR2
AS

ReturnValue VARCHAR2(500);
PropNames VARCHAR2(500);
PropValues VARCHAR2(500);
PropPosition NUMBER;
IdxStart NUMBER(3);
PropLength NUMBER(3);

BEGIN

-- Note: Prefix the first property name with a colon. So we know that EVERY property name will be bookended with colons.
SELECT ':' || to_char(PropertyNames) into PropNames
FROM ora_aspnet_Profile
WHERE UserId = paramUserID;

SELECT to_char(PropertyValuesString) into PropValues
FROM ora_aspnet_Profile
WHERE UserId = paramUserID;

PropPosition := instr(PropNames, ':' || paramPropertyName || ':S');

IF PropPosition = 0 THEN
ReturnValue := null;
ELSE
PropPosition := PropPosition + LENGTH(paramPropertyName) + 4;
PropNames := substr(PropNames, PropPosition, length(PropNames));
IdxStart := substr(PropNames, 1, instr(PropNames, ':') - 1) + 1;
PropLength := substr(PropNames, instr(PropNames, ':') + 1, ((instr(PropNames, ':', instr(PropNames, ':') + 1)) - (instr(PropNames, ':') + 1)));
ReturnValue := substr(PropValues, IdxStart, PropLength);
END IF;

RETURN ReturnValue;

END SR_ORA_ASPNET_PROF_GETPROPERTY;

Posted on 10/3/2011 4:39:35 AM by Tareq Samy #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

Nice post, help alot, thx

Posted on 2/7/2012 5:00:51 AM by Max #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

great article , saved my time . thanx

Posted on 2/26/2012 2:17:48 PM by reza #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

How to get the propetyvaluesBinary from this

Posted on 3/7/2012 1:34:05 AM by vikas #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

thanks

Posted on 4/24/2012 8:45:44 AM by csharper #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

im new to this, so how do i tell this function in sql that i need a property of a especific user?

like this Profile.GetProfile(user).GetPropertyValue("FullName").ToString();

im using the first helper function, and im getting the results by the sql. but its not dynamic, for example i have a username "mike" and i want it to get the fullname "mike the man"..

like in this schema

username: "mike" Fullname: "Mike the man"
text:"hello"
data:02-05-2012

and now the same for any other user. the username has to decide the full name...

im using this sql

SELECT Wall.Para, Wall.De, Wall.Texto, Wall.Data, dbo.GetProfilePropertyValue('FullName', aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesString) AS Expr1 FROM Wall CROSS JOIN aspnet_Profile WHERE (dbo.GetProfilePropertyValue('FullName', aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesString) = @fullname) AND (Wall.Para = @para)

But its not dynamic :((((((

Posted on 5/1/2012 10:55:58 PM by Bruno Martins #
Gravatar

Try something like this:

SELECT Wall.Para, Wall.De, Wall.Texto, Wall.Data, dbo.GetProfilePropertyValue('FullName', aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesString) AS Expr1 FROM Wall CROSS JOIN aspnet_Profile WHERE (dbo.GetProfilePropertyValue('UserName', aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesString) = 'mike') AND (Wall.Para = @para)

Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

genius, just genuis! Thank you!! now i can build a view of members & profile data and list is easily in a gridview with paging and sorting and everything... thank you!!!!

Posted on 11/23/2012 1:06:00 AM by Stuart #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

Thanks

Posted on 1/1/2013 2:56:28 AM by hashemi #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

Great! Thanks for sharing1

Posted on 1/29/2013 5:00:06 AM by Yaqub Ahmad #
Gravatar

Re: How to get asp.net profile value from MS SQL database using T-SQL?

Thanks, Helped me out a lot!

Posted on 3/9/2015 10:33:36 AM by Joshua Philpott #

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5