Selecting profile properties the easy way

Every once and a while you probably find yourself needing to create an overview of DNN users and some of their profile properties. For instance when your client wants to have that data in an excel spreadsheet….

Profile properties in DNN are both a blessing and a curse. Although they allow you to quickly add attributes to a user, such as Companyname, Picture, etc., they are hard to use in SQL. That’s because of the structure of the data: there’s a table that holds all property definitions, and there’s another table that holds the data.

In the past, I have been using a quick and dirty way: just creating a specific view for each profile property I wanted to include in my select. That would work like this: let’s suppose I have a profile property named “CompanyName”. I can then create a view like this:

CREATE VIEW [dbo].[vwCompanyName]
ASSELECT     UP.UserID, PPD.PortalID, UP.PropertyValue AS CompanyNameFROM         dbo.ProfilePropertyDefinition AS PPD INNER JOIN
dbo.UserProfile AS UP ON PPD.PropertyDefinitionID = UP.PropertyDefinitionIDWHERE     (PPD.PropertyName = 'CompanyName') AND (PPD.PortalID = 0)

This allows me to create a new view that includes the companyname with the user’s info:

CREATE VIEW [dbo].[vw_UsersComplete]
ASSELECT     dbo.vw_Users.UserId, dbo.vw_Users.PortalId, dbo.vw_Users.Username, dbo.vw_Users.FirstName, dbo.vw_Users.LastName, 
dbo.vw_Users.DisplayName, dbo.vw_Users.IsSuperUser, dbo.vw_Users.Email, dbo.vw_Users.AffiliateId, dbo.vw_Users.UpdatePassword, 
dbo.vw_Users.Authorised, dbo.vwCompanyName.CompanyNameFROM         dbo.vw_Users INNER JOIN
dbo.vwCompanyName ON dbo.vw_Users.PortalId = dbo.vwCompanyName.PortalID AND dbo.vw_Users.UserId = dbo.vwCompanyName.UserID

(vw_Users is a DotNetNuke core SQL View, which joins the tables Users and UserPortals, resulting in a view with user information AND information about which portal users belong to.)

I now have a view of all user information including the company name.

Doing this for one profile property is not so much work, however, if there are many profile properties to include, creating a seperate view for all of them is rather tedious. Creating a user defined function (UDF) that returns a table is much less work. The UDF I created subsequently is this:

CREATE FUNCTION [dbo].[udf_UserProfileField]
(
@PropertyName NVARCHAR(50)
)
RETURNS @ProfileFieldTable TABLE
(
PortalId INT,
UserID INT,
PropertyName NVARCHAR(50),
PropertyValue NVARCHAR(3750)
)
AS BEGIN
INSERTINTO @ProfileFieldTableSELECT  PPD.PortalID,
UP.UserID,
PPD.PropertyName,
UP.PropertyValueFROM    dbo.Users AS UINNER JOIN dbo.UserProfile AS UP ON U.UserID = UP.UserIDINNER JOIN dbo.ProfilePropertyDefinition AS PPD ON UP.PropertyDefinitionID = PPD.PropertyDefinitionIDWHERE   ( PPD.PropertyName = @PropertyName )
RETURN 
END

This UDF works essentially the same as the previously created view vwCompanyName. The function takes the parameter PropertyName. This allows us to easily use this UDF in combination with vw_Users. Just start creating a view like you would create any view in SQL Server, using SQL Server Management Studio. Start by adding the view “vw_Users”:

next, add the function udf_UserProfileField (in fact, add it for as many profile fields you want to include in your view…):

For this sample I added the function twice. For clarity i then changed the names of the 2 table functions to “UserCompany” and “UserCity”. Next, i added the relationships to the vw_Users view. Remember that the relationships are definined by both PortalId and UserId (So we know for sure that the profile properties we link to users are coming from the right portal!). Also, I selected the relationship option to show all records from the vw_Users view, since not every user might have values for all profile properties. This way, the view will return empty values for those users. The final view looks like this:

The quickest way to repeat this result is to just copy and run the below sql statement:

CREATE VIEW [dbo].[vwUsersComplete]
ASSELECT     U.UserId, U.PortalId, U.Username, U.FirstName, U.LastName, U.DisplayName, U.IsSuperUser, U.Email, U.AffiliateId, U.UpdatePassword, U.Authorised, 
UserCompany.PropertyValue AS Company, UserCity.PropertyValue AS CityFROM         dbo.udf_UserProfileField('City') AS UserCity RIGHT OUTER JOIN
dbo.vw_Users AS U ON UserCity.PortalId = U.PortalId AND UserCity.UserID = U.UserId LEFT OUTER JOIN
dbo.udf_UserProfileField('Company') AS UserCompany ON U.UserId = UserCompany.UserID AND U.PortalId = UserCompany.PortalId

As you can see the above view uses the same UDF 2 times, and that’s where the advantage is. You can now easily create views that link your users to their profile properties. The posibilities are endless, eg, you might want to use the DotNetNuke Reports module to render the results of this view to a DNN page, while using the Reports Module’s powerful rendering capabilities.