SQL aspnet_profile

Question

any idea how I can get user FirstName and LastName from the aspnet_profile table based on UserID using SQL becasue I would like to use in Telerik Reporting as a user parameter.

Sample row (FirstName is George, LastName is Test):

UserID: 06b24b5c-9aa1-426e-b7e4-0771c5f85e85

PropertyName: MobilePhone:S:0:0:Initials:S:0:1:City:S:1:14:FirstName:S:15:6:PostalCode:S:21:7:‌​WorkPhone:S:28:12:LastName:S:40:5:Address1:S:45:17:Address2:S:62:0:Province:S:62:‌​2:Organization:S:64:4:ClinicId:S:68:1:Country:S:69:6:Fax:S:75:0:MSPNumber:S:75:0:‌​ 

PropertyValuesString: HEast HustonEASGeorgeT7D 1N8604-111-2222Test5555 Beddtvue AveDCHCNL2Canada

PropertyValuesBinary: <Binary data>

LastUpdateDate: 2010-01-02 22:22:03.947

Answer 1

If you insist on using SQL, I'm sure a large number of SUBSTRINGs and PATINDEXes will get you there but it won't be a clean solution.

Update: user373721 found a great resource and posted a comment about it, but it can be easily missed, so I decided to add it to the answer, too - How to get asp.net profile value from MS SQL database using T-SQL?

The built-in dbo.aspnet_Profile_GetProperties stored procedure returns the PropertyValuesString value that is later parsed in the ParseDataFromDB function.

private void GetPropertyValuesFromDatabase(string userName, SettingsPropertyValueCollection svc)
{
    if (HostingEnvironment.IsHosted && EtwTrace.IsTraceEnabled(4, 8))
    {
        EtwTrace.Trace(EtwTraceType.ETW_TYPE_PROFILE_BEGIN, HttpContext.Current.WorkerRequest);
    }
    HttpContext current = HttpContext.Current;
    string[] names = null;
    string values = null;
    byte[] buffer = null;
    if (current != null)
    {
        if (!current.Request.IsAuthenticated)
        {
            string anonymousID = current.Request.AnonymousID;
        }
        else
        {
            string name = current.User.Identity.Name;
        }
    }
    try
    {
        SqlConnectionHolder connection = null;
        SqlDataReader reader = null;
        try
        {
            connection = SqlConnectionHelper.GetConnection(this._sqlConnectionString, true);
            this.CheckSchemaVersion(connection.Connection);
            SqlCommand command = new SqlCommand("dbo.aspnet_Profile_GetProperties", connection.Connection) {
                CommandTimeout = this.CommandTimeout,
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.Add(this.CreateInputParam("@ApplicationName", SqlDbType.NVarChar, this.ApplicationName));
            command.Parameters.Add(this.CreateInputParam("@UserName", SqlDbType.NVarChar, userName));
            command.Parameters.Add(this.CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
            reader = command.ExecuteReader(CommandBehavior.SingleRow);
            if (reader.Read())
            {
                names = reader.GetString(0).Split(new char[] { ':' });
                values = reader.GetString(1);
                int length = (int) reader.GetBytes(2, 0L, null, 0, 0);
                buffer = new byte[length];
                reader.GetBytes(2, 0L, buffer, 0, length);
            }
        }
        finally
        {
            if (connection != null)
            {
                connection.Close();
                connection = null;
            }
            if (reader != null)
            {
                reader.Close();
            }
        }
        ProfileModule.ParseDataFromDB(names, values, buffer, svc);
        if (HostingEnvironment.IsHosted && EtwTrace.IsTraceEnabled(4, 8))
        {
            EtwTrace.Trace(EtwTraceType.ETW_TYPE_PROFILE_END, HttpContext.Current.WorkerRequest, userName);
        }
    }
    catch
    {
        throw;
    }
}

 

internal static void ParseDataFromDB(string[] names, string values, byte[] buf, SettingsPropertyValueCollection properties)
{
    if (((names != null) && (values != null)) && ((buf != null) && (properties != null)))
    {
        try
        {
            for (int i = 0; i < (names.Length / 4); i++)
            {
                string str = names[i * 4];
                SettingsPropertyValue value2 = properties[str];
                if (value2 != null)
                {
                    int startIndex = int.Parse(names[(i * 4) + 2], CultureInfo.InvariantCulture);
                    int length = int.Parse(names[(i * 4) + 3], CultureInfo.InvariantCulture);
                    if ((length == -1) && !value2.Property.PropertyType.IsValueType)
                    {
                        value2.PropertyValue = null;
                        value2.IsDirty = false;
                        value2.Deserialized = true;
                    }
                    if (((names[(i * 4) + 1] == "S") && (startIndex >= 0)) && ((length > 0) && (values.Length >= (startIndex + length))))
                    {
                        value2.SerializedValue = values.Substring(startIndex, length);
                    }
                    if (((names[(i * 4) + 1] == "B") && (startIndex >= 0)) && ((length > 0) && (buf.Length >= (startIndex + length))))
                    {
                        byte[] dst = new byte[length];
                        Buffer.BlockCopy(buf, startIndex, dst, 0, length);
                        value2.SerializedValue = dst;
                    }
                }
            }
        }
        catch
        {
        }
    }
}

Answer 2

http://www.karpach.com/Get-asp-net-profile-value-MS-SQL-database-using-T-SQL.htm

this helped me tremendously!

following the steps in that link allowed me to fetch any particular data in the PropertyValueString from the aspnet_profile table.

copying and pasting- First Function:

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

Second 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

SQL Query can be modded to your needs

SELECT dbo.fn_GetProfileElement('FirstName',PropertyNames,PropertyValuesString)
     , dbo.fn_GetProfileElement('LastName',PropertyNames,PropertyValuesString)
FROM aspnet_Profile

Answer 3

For those who are still looking for a method to parse the aspnet_Profile table using pure SQL. Here is what I use:

First you need a Tally table. If you do not know what this is, read this article by Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/62867/

For you to generate the tally table use this script:

SELECT TOP 11000 IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1,  Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

Now on to parsing the ProfileData: The process below is the fastest way I found to do this after lots of testing on my specific data. I have tested parsing the complete table in one go, but that runs slower than using the function below and parsing one user at a time with a CROSS APPLY.

So to call the function, use something like:

SELECT bla, bla
FROM aspnet_Users u CROSS APPY dbo.ProfileProperties(u.UserID)

The only thing you need to do is to update 3 things to contain the Profile Properties that you use: 1) the return table 2) the PIVOT statement, and 3) the insert statement copying the data from the PIVOT into the return table

Here is the function, Enjoy!

/** =============================================
** Author:      Francois Grobler
** Create date: 2013-04-25
** Description: This function extracts all 
**    Profile Properties for a given UserId, 
**    and returns them as a table
** Change History: 
** Date:  Author:  Change:  
** 
** ============================================= **/
CREATE FUNCTION dbo.ProfileProperties
(
    @UserID UNIQUEIDENTIFIER
)
RETURNS @returnTable TABLE(
            FirstName nvarchar(200)
            , LastName nvarchar(200)
            , PassportNumber nvarchar(100)
            , PositionCode int
            , CellNumber nvarchar(20)
            , Telephone nvarchar(30)
            , FaxNumber nvarchar(20)
            , Email nvarchar(200)
            , PersalNumber nvarchar(10)
            , SouthAfricanIdentityNumber nchar(13)
            , ContractNumber nvarchar(20)
            , DepartmentName nvarchar(200)
            , SiteName nvarchar(200)
            , DepartmentCode int
            , SiteCode int
            , UserAccessCode int
            , ApproverCode int
                        )
WITH SCHEMABINDING
AS
BEGIN
    WITH Properties(PropertyNo, PropertyType, UserId, Value)
    AS
    (
            SELECT (ROW_NUMBER() OVER(ORDER BY UserId) - 1) / 4 PropertyNo
            , (ROW_NUMBER() OVER(PARTITION BY p.UserId ORDER BY UserId) - 1) % 4 PropertyType
            , p.UserId
            , SUBSTRING(':' + CONVERT(nvarchar(4000), p.PropertyNames), n + 1, CHARINDEX(':', ':' + CONVERT(nvarchar(4000), p.PropertyNames), n + 1) - n - 1) Value
            FROM dbo.Tally, dbo.aspnet_Profile p
            WHERE n < LEN(':' + CONVERT(nvarchar(4000), p.PropertyNames))
                and SUBSTRING(':' + CONVERT(nvarchar(4000), p.PropertyNames), n, 1) = ':'
                and p.UserId = @UserID
    )
    , FlatProperties(UserId, Property, ValueType, StartIndex, ValueLength)
    AS
    (
        SELECT UserId
        , MAX(CASE WHEN PropertyType = 0 THEN Value ELSE '' END) Property
        , MAX(CASE WHEN PropertyType = 1 THEN Value ELSE '' END) ValueType
        , MAX(CASE WHEN PropertyType = 2 THEN CONVERT(int, Value) + 1 ELSE 0 END) StartIndex
        , MAX(CASE WHEN PropertyType = 3 THEN CONVERT(int, Value) ELSE 0 END) ValueLength
        FROM
            Properties
        GROUP BY UserID, PropertyNo
    )
    , PropertyValues(UserID, PropertyName, PropertyValue)
    AS
    (
        SELECT p.UserID, fp.Property
        , CASE fp.ValueType
            WHEN 'S' THEN SUBSTRING(p.PropertyValuesString, fp.StartIndex, fp.ValueLength)
            ELSE SUBSTRING(p.PropertyValuesBinary, fp.StartIndex, fp.ValueLength) END Value
        FROM dbo.aspnet_Profile p INNER JOIN flatProperties fp ON p.UserId = fp.UserId
        WHERE p.UserId = @UserID
    )
    , PropertyTable
    AS
    (
        SELECT 
            UserID
            , pvt.[FirstName]
            , pvt.[LastName]
            , pvt.[PassportNumber]
            , pvt.[PositionCode]
            , pvt.[CellNumber]
            , pvt.[Telephone]
            , pvt.[FaxNumber]
            , pvt.[Email]
            , pvt.[PersalNumber]
            , pvt.[SouthAfricanIdentityNumber]
            , pvt.[ContractNumber]
            , pvt.[DepartmentName]
            , pvt.[SiteName]
            , pvt.[DepartmentCode]
            , pvt.[SiteCode]
            , pvt.[UserCode] UserAccessCode
            , pvt.[ApproverCode]
        FROM PropertyValues
         PIVOT (
         MAX(PropertyValue) FOR PropertyName IN ([FirstName],[LastName],[PassportNumber],[PositionCode],[CellNumber],[Telephone],[FaxNumber],[Email],[PersalNumber],[SouthAfricanIdentityNumber],[ContractNumber],[DepartmentName],[SiteName],[DepartmentCode],[SiteCode],[UserCode],[ApproverCode]) 
             ) AS pvt
    )
    INSERT INTO @returnTable
    (
        FirstName
        , LastName
        , PassportNumber
        , PositionCode
        , CellNumber
        , Telephone
        , FaxNumber
        , Email
        , PersalNumber
        , SouthAfricanIdentityNumber
        , ContractNumber
        , DepartmentName
        , SiteName
        , DepartmentCode
        , SiteCode
        , UserAccessCode
        , ApproverCode
    )
    SELECT TOP 1
        FirstName
        , LastName
        , PassportNumber
        , PositionCode
        , CellNumber
        , Telephone
        , FaxNumber
        , Email
        , PersalNumber
        , SouthAfricanIdentityNumber
        , ContractNumber
        , DepartmentName
        , SiteName
        , DepartmentCode
        , SiteCode
        , UserAccessCode
        , ApproverCode
    FROM PropertyTable;

    RETURN;
END
GO