HOWTO: Accessing DNN Profile Data using T-SQL > DNN Stuff - Quality DotNetNuke Modules, Unrivaled Support

View Article

Current Articles | Categories | Search | Syndication

Wednesday, March 09, 2005

HOWTO: Accessing DNN Profile Data using T-SQL
By Richard Edwards @ 8:41 PM :: 6472 Views :: 11 Comments ::

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 instance, in the following example it shows us that the Region field is a string field (S) starting at postion 32 and is 7 characters long.

Website:S:0:23:TimeZone:S:23:4:PreferredLocale:S:27:5:Region:S:32:7:

FirstName:S:39:7:PostalCode:S:46:7:Street:S:53:12:LastName:S:65:7:

Unit:S:72:0:Telephone:S:72:12:Country:S:84:6:IM:S:90:6:Fax:S:96:6:

City:S:102:10:Cell:S:112:12:

Now that we know how the Region field is stored, we can look in another field named PropertyValuesString to find the data. Based on the previous example, we know if we start at position 32 and read for 7 characters we'll get the Region information which in this case is Ontario.

http://www.dnnstuff.com-480en-USOntarioRichardK8N 5W58 Woodley Ct

Edwards613 969-5259CanadaIM1234NO FAXBelleville613 827-5259

For the sake of this article, I'll only be talking about the string fields. The profile does have the ability to store data in a binary format as well, so in that case you would look in the PropertyValuesBinary field for the appropriate data.

Paging - Page: 1 of 4
	Previous Page \| Next Page

Comments

By 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!

By 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.

By Jeremiah Redekop @ Thursday, April 14, 2005 1:10 PM

Great!

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

By 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

By 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.

By 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

By 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.

By 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.

By 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!

By 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

By 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.

Click here to post a comment