DotNetNuke, ASP.NET, Web Development Blog

DotNetNuke: Getting User Profile Data through SQL

*** Updated: Added method for DNN versions 3.3 and 4.3 and greater ***
DNN 3.0 - 3.2
In DNN 3.0-3.2 and 4.0-4.1, the membership / profile stuff made heavy use of Microsoft's Membership provider module which stores most of the information in a hash table. While this is fine for getting a single user, it's a complete nightmare if you want to show a grid or list of many users or to do something with the data in SQL.

There's a good write up on how to get at this info through SQL at DNN Stuff.

DNN 3.3+ and 4.3+
The core team did a complete and much needed overhaul of the User Profile system for DNN 3.3 and 4.3. The grid on the User Accounts page is now a lot more flexible but it still won't help you if you want non-admin users to view user data or if you want to utilize profile data in SQL... Click on the Read More link to learn how to get at DNN 3.3+ profile data in SQL. This data could then be displayed using the Advanced Data Grid

There is most likely a more efficient and simpler way to do the following. Suggestions and improvements are gladly accepted...

First we need a function that will return a value given a UserID and a PropertyName:


If all you need to do is display the data and there's not a ton of users, You can then run a Select statement along lines of:


In our case, we were dealing with several thousand users and we needed to Join the User Data to another table so, we created a Stored Procedure that:
1.)to maximize performance, we had limit the number of records retrieved from the user profile table
2.)Then we did a SELECT INTO a temp table.
3.)We then joined the data with our other table.

The Stored Procedure ended up looking something like this:

Comments

Gravatar
Hoang Nguyen Says:
8/9/2006 1:34:57 PM
This is off topic but I was wondering if the advanced datagrid would work with 4.3.x or not? thanks.
Gravatar
Bill Lenherr Says:
8/16/2006 9:42:31 AM
I do have it working on 4.3.4.

I am using the function above and the simple query with the following changes :
Selecting email instead of "dbo.GetProfileElement2(N'Email', u.UserID) As Email" which was returning noting.

Appended :
INNER JOIN UserPortals ON u.UserID = UserPortals.UserId
WHERE (u.UserID NOT IN (24)) AND (UserPortals.PortalId = 6)
order by LastName

This selects from a specific portal, excludes admins and sorts by last name

It would be cool to also filter using users display preferences to hide their email address etc.
Leave a Comment
Gravatar
Name:
Email: (not displayed)
Comments:

CAPTCHA image
Enter the code shown above:
Website

Return to previous page