DotNetNuke, ASP.NET, Web Development Blog

DotNetNuke: Getting User Profile Data through SQL

Categories: DotNetNuke | Author: gait | Posted: 5/11/2006 | Views: 39893

*** 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:
CREATE FUNCTION [dbo].[GetProfileElement2] ( @fieldName AS NVARCHAR(100), @userid AS INT ) RETURNS NVARCHAR(2000) AS BEGIN DECLARE @ret NVARCHAR(2000) -- If input is invalid, return null. IF @fieldName IS NULL OR LEN(@fieldName) = 0 OR @userid IS NULL OR LEN(@userid) = 0 RETURN NULL -- Return the string SELECT @ret = PropertyValue FROM dbo.UserProfile up INNER JOIN dbo.ProfilePropertyDefinition ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID WHERE UserID = @userid AND PropertyName = @fieldName RETURN @ret END GO

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:
SELECT dbo.GetProfileElement2(N'Company', u.UserID) AS Company, dbo.GetProfileElement2(N'FirstName', u.UserID) AS FirstName, dbo.GetProfileElement2(N'LastName', u.UserID) AS LastName, dbo.GetProfileElement2(N'Street', u.UserID) AS Street, dbo.GetProfileElement2(N'Unit', u.UserID) AS Unit, dbo.GetProfileElement2(N'City', u.UserID) AS City, dbo.GetProfileElement2(N'Region', u.UserID) AS State, dbo.GetProfileElement2(N'PostalCode',u.UserID) AS Postal, dbo.GetProfileElement2(N'Country', u.UserID) AS Country, dbo.GetProfileElement2(N'Telephone', u.UserID) AS Phone, dbo.GetProfileElement2(N'Cell', u.UserID) AS Phone2, dbo.GetProfileElement2(N'Fax', u.UserID) AS Fax, dbo.GetProfileElement2(N'Email', u.UserID) As Email, dbo.GetProfileElement2(N'AlternateEmail', u.UserID) AS Email2, u.UserName, u.UserID FROM dbo.Users u

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:
CREATE PROCEDURE [dbo].[Sales_GetRegisteredUsers] @SaleCode VarChar(50) AS DECLARE @SaleID int SELECT @SaleID = SaleID FROM Sale WHERE SaleCode = @SaleCode SELECT '<a href="MySales.aspx?userid=' + LTRIM(STR(u.UserID)) + '" >Regs</a>' AS Regs, dbo.GetProfileElement2(N'Company', u.UserID) AS Company, dbo.GetProfileElement2(N'FirstName', u.UserID) AS First, dbo.GetProfileElement2(N'LastName', u.UserID) AS Last, dbo.GetProfileElement2(N'Street', u.UserID) AS Add1, dbo.GetProfileElement2(N'Unit', u.UserID) AS Add2, dbo.GetProfileElement2(N'City', u.UserID) AS City, dbo.GetProfileElement2(N'Region', u.UserID) AS State, dbo.GetProfileElement2(N'PostalCode',u.UserID) AS Postal, dbo.GetProfileElement2(N'Country', u.UserID) AS Country, dbo.GetProfileElement2(N'Telephone', u.UserID) AS Phone, dbo.GetProfileElement2(N'Cell', u.UserID) AS Phone2, dbo.GetProfileElement2(N'Fax', u.UserID) AS Fax, dbo.GetProfileElement2(N'Email', u.UserID) As Email, dbo.GetProfileElement2(N'AlternateEmail', u.UserID) AS Email2, u.UserName, u.UserID INTO #tempProfile FROM dbo.Users u WHERE UserID IN (Select UserID from SaleUsers where SaleID = @SaleID) SELECT su.BidderNumber, su.UserID, su.CreatedDate, s.SaleCode, p.* FROM dbo.SaleUsers su INNER JOIN #tempProfile p ON su.UserID = p.UserID INNER JOIN dbo.Sale s ON su.SaleID = s.SaleID WHERE su.SaleID = @SaleID DROP TABLE #tempProfile GO

Comments

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.

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.