HOWTO: Accessing DNN Profile Data using T-SQL

Richard Edwards posted on March 09, 2005 20:41

Ok, from the previous page we now know where the data is and how it's stored, but how do we get at that from T-SQL. SQL Server 2000 brought us UDF's (User Defined Functions) which allow us to bundle complex logic into reusable functions which we can use directly in SQL queries. I'm not going to go into the nitty gritty of UDF's here but we are going to be using two different ones to get at the data.

The first one we'll need is one that will allow us to grab fields of delimited data from a string. Once we have the field offset and length data string such as 32:7, we will need this function to parse this into two separate integers, namely 32 and 7 so we can use the SUBSTRING function to grab the data from the PropertyValuesString field. The function to do that is here:

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

This function takes 3 parameters; @ord, @str and @delim. @ord is the field number you wish to return, @str is the string that you wish to parse, and @delim is the delimiter you are folding the string with. This is a very general UDF that can be used to return delimited fields for any t-sql and isn't just handy for this task. I found the source for this function at the Windows IT Pro site in an article by Itzik Ben-Gan. We'll be calling this function in our next function.

Pages: 2 of 4 Previous Page Next Page

Posted in: DotNetNuke, How-To

Actions:E-mail | Permalink | Comments (11)

| Kick it! | DZone it! | del.icio.us

Comments

# erik van ballegoij

Thursday, March 10, 2005 4:45 AM

wew,

thanx for this clear presentation Richard... This would have taken me ages to find out myself!

# Murat Isik

Friday, March 11, 2005 10:49 AM

Wow,

Richard, you were just on time... We were planning to create the functions by ourselves today. Thanks for your neat presentation and functions.

# Jeremiah Redekop

Thursday, April 14, 2005 1:10 PM

Great!

I was just about to give up on finding a solution for this! Thanks!

# Jean-Sylvain Boige

Thursday, May 26, 2005 1:10 PM

Hi,

Thanks for all those clues, though I must admit I would have much preferred to keep with the membership provider, but it seems the httpcontext-less scheduler won't do with it.

Just wondering. Shouldn't the join include the portal guid, since theoretically the same username can be used by different users in several portals

# Duncan Watson

Wednesday, June 08, 2005 9:34 PM

Great article - this will save me a lot of time redoing a lot of SQL queries which grad User data. Thanks.

# Josh Gough

Friday, July 08, 2005 4:13 PM

This is a really great article. My coworker and I were walking around at lunch thinking about how to address this, so we googled it and found your article.

Here is a view based on your code:

CREATE VIEW dbo.vw_ProfileProperties
AS
SELECT
Users_1.userid,
dbo.fn_GetProfileElement('Street', PropertyNames, PropertyValuesString) AS Street,
dbo.fn_GetProfileElement('City', PropertyNames, PropertyValuesString) AS City,
dbo.fn_GetProfileElement('Region', PropertyNames, PropertyValuesString) AS Region,
dbo.fn_GetProfileElement('Country', PropertyNames, PropertyValuesString) AS Country,
dbo.fn_GetProfileElement('PostalCode', PropertyNames, PropertyValuesString) AS PostalCode,
dbo.fn_GetProfileElement('Telephone', PropertyNames, PropertyValuesString) AS Telephone,

FROM
dbo.Users Users_1 INNER JOIN
dbo.aspnet_Users aspnet_Users_1 ON Users_1.username = aspnet_Users_1.username INNER JOIN
dbo.aspnet_Membership aspnet_Membership_1 ON aspnet_Users_1.userid = aspnet_Membership_1.userid INNER JOIN
dbo.aspnet_Profile aspnet_Profile_1 ON aspnet_Profile_1.userid = aspnet_Membership_1.userid

# Jim Frazee

Friday, July 22, 2005 12:32 AM

Thanks, Richard and Josh. I use the contributions from both of you on one of my sites. By the way, Josh, you left off Unit in your view, but it was easy enough to add.

My question is, how would one go about updating the profile fields? It would be better to not have to go to the user page to update the fields I'm using on another page.

# Justin Ritzman

Tuesday, July 26, 2005 6:30 PM

I would also like to know how to update the profile fields. This is beyond me because of the binary field in the profile table.

If anyone has done this, please post it.

# Christopher Palmer

Tuesday, April 25, 2006 12:57 AM

You rock!

We just upgraded a huge web app to the ASP.Net 2.0 user, profile, and role management components in a single day. The one last piece was a page that contained a grid showing a mix of info that was in a single table in our old system, but was scattered through the user, profile, and roles tables in the new system. We were about to write code to iterate through everything when we found your functions. Instead, we were able to re-write a single stored procedure and use our existing grid code!

And, we removed over 1,000 lines of code in the course of the changeover. It's been a long day...

Thanks again!

# prejesh

Thursday, April 05, 2007 2:04 AM

Its works fine for selection but it returns empty rows when we have a where condition
please help

# Richard Edwards

Thursday, April 05, 2007 8:17 AM

Prejesh, open a new post in the forum with your query and I'll try to help you out.

HOWTO: Accessing DNN Profile Data using T-SQL

Post Rating

Comments

Post Comment