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 60148 1336 Finley rd Karpach
ASP.Net profiles can store binary data as well, but usually, you are interested in string data such as First and Last names. First let’s create a 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 the following:
SELECT dbo.fn_GetProfileElement('FirstName',PropertyNames,PropertyValuesString) + ' ' +
dbo.fn_GetProfileElement('LastName',PropertyNames,PropertyValuesString) as FullName FROM aspnet_Profile
Special thanks to Richard Edwards at DNNStuff
