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 :: 7039 Views :: 11 Comments :: :: DotNetNuke, How-To

Well that should do it. Now you have the ability to grab that profile data just like before and use it in your queries. To show how you might put this together with some other tables from the membership information, here's another sample which shows the proper joins to get at most of the information, plus a call to our new function to grab the profile data field named PostalCode.

select users.userid, users.username, users.firstname, users.lastname, roles.rolename, aspnet_membership.email,
dbo.fn_GetProfileElement('PostalCode',PropertyNames,PropertyValuesString) PostalCode
from users

inner join userroles on users.userid = userroles.userid inner join roles on userroles.roleid = roles.roleid inner join aspnet_users on users.username = aspnet_users.username inner join aspnet_membership on aspnet_users.userid = aspnet_membership.userid inner join aspnet_profile on aspnet_profile.userid = aspnet_membership.userid

Paging - Page: 4 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