用SQL语句从aspnet_profile表里取用户的Profile值

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