Shortcut with retrieving ASP.NET profile values from DB

Working on a project that is using ASP.NET membership and with that feature one can store profile/user info in a hash form in the database. I’ve tended to not utilize this packaged stuff so I didn’t have a shortcut built to retrieve these values for a report. First time I used excel and when the second request came I happily googled and found something from what I think are DotNetNuke folks at DNN. Gracias for saving me 20 minutes!

[sql]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

CREATE FUNCTION dbo.fn_GetProfileElement

(

@fieldName AS NVARCHAR(100),

@fields AS NVARCHAR(4000),

@values AS NVARCHAR(4000))

RETURNS NVARCHAR(4000)

AS

BEGIN

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

DECLARE @fieldNameToken AS NVARCHAR(20)

DECLARE @fieldNameStart AS INTEGER,

@valueStart AS INTEGER,

@valueLength AS INTEGER

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

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)

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

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

IF @valueLength = 0 RETURN ”

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

END[/sql]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s