Monday, May 23, 2011

Display DNN user profile properties in column format

Hi All,

A few days back in one of the DNN portals that I manage I was required to provide functionality to download user info including user profile properties in column format. Initially what I did was using DNNs built in API for getting users and then iterate through their profile properties by using UserInfo.Profile.ProfileProperties

This worked fine initially but once the number of users started growing, this started tooking great deal of time and ultimately started timing out. So I thought that I should design my own procedure for doing this and hence I came up with below solution. Probably this may save a few hours of your time.


            SELECT u.UserID, u.Username,
            upo2.PropertyValue as [ContactType],
upo3.PropertyValue AS [Title],
upo4.PropertyValue AS [FirstName],
            upo5.PropertyValue AS [LastName],
upo6.PropertyValue  AS [Position],
UPO7.PropertyValue  AS [Company],
UPO8.PropertyValue  AS [MembershipPeriod], UPO9.PropertyValue  AS [MembershipJoinDate],
UPO10.PropertyValue  AS [MembershipExpiryDate], UPO11.PropertyValue  AS [Address1],
            UPO12.PropertyValue  AS [Address2],
UPO13.PropertyValue  AS [Address3],
UPO14.PropertyValue  AS [Town],
            UPO15.PropertyValue  AS [County],
UPO16.PropertyValue  AS [Post Code],
UPO17.PropertyValue  AS [Country],
            UPO18.PropertyValue  AS [MainPhone],
UPO19.PropertyValue  AS [AlternativePhone],
            UPO20.PropertyValue  AS [Email],
UPO21.PropertyValue  AS [MembershipNumber]

            FROM Users u

            LEFT OUTER JOIN UserProfile UPO1
            ON upo1.UserID = u.UserID AND upo1.PropertyDefinitionID = 394

            LEFT OUTER JOIN UserProfile UPO2
            ON upo2.UserID = u.UserID AND upo2.PropertyDefinitionID = 398

            LEFT OUTER JOIN UserProfile UPO3
            ON upo3.UserID = u.UserID AND upo3.PropertyDefinitionID = 393

            LEFT OUTER JOIN UserProfile UPO4
            ON upo4.UserID = u.UserID AND upo4.PropertyDefinitionID = 362

            LEFT OUTER JOIN UserProfile UPO5
            ON upo5.UserID = u.UserID AND upo5.PropertyDefinitionID = 364

            LEFT OUTER JOIN UserProfile UPO6
            ON upo6.UserID = u.UserID AND upo6.PropertyDefinitionID = 382

            LEFT OUTER JOIN UserProfile UPO7
            ON upo7.UserID = u.UserID AND upo7.PropertyDefinitionID = 381

            LEFT OUTER JOIN UserProfile UPO8
            ON upo8.UserID = u.UserID AND upo8.PropertyDefinitionID = 395

            LEFT OUTER JOIN UserProfile UPO9
            ON upo9.UserID = u.UserID AND upo9.PropertyDefinitionID = 396

            LEFT OUTER JOIN UserProfile UPO10
            ON UPO10.UserID = u.UserID AND UPO10.PropertyDefinitionID = 397

            LEFT OUTER JOIN UserProfile UPO11
            ON upo11.UserID = u.UserID AND UPO11.PropertyDefinitionID = 383

            LEFT OUTER JOIN UserProfile UPO12
            ON UPO12.UserID = u.UserID AND UPO12.PropertyDefinitionID = 384

            LEFT OUTER JOIN UserProfile UPO13
            ON UPO13.UserID = u.UserID AND UPO13.PropertyDefinitionID = 385

            LEFT OUTER JOIN UserProfile UPO14
            ON UPO14.UserID = u.UserID AND UPO14.PropertyDefinitionID = 386

            LEFT OUTER JOIN UserProfile UPO15
            ON UPO15.UserID = u.UserID AND UPO15.PropertyDefinitionID = 502

            LEFT OUTER JOIN UserProfile UPO16
            ON UPO16.UserID = u.UserID AND UPO16.PropertyDefinitionID = 391

            LEFT OUTER JOIN UserProfile UPO17
            ON UPO17.UserID = u.UserID AND UPO17.PropertyDefinitionID = 370

            LEFT OUTER JOIN UserProfile UPO18
            ON UPO18.UserID = u.UserID AND UPO18.PropertyDefinitionID = 392

            LEFT OUTER JOIN UserProfile UPO19
            ON UPO19.UserID = u.UserID AND UPO19.PropertyDefinitionID = 388

            LEFT OUTER JOIN UserProfile UPO20
            ON UPO20.UserID = u.UserID AND UPO20.PropertyDefinitionID = 389

            LEFT OUTER JOIN UserProfile UPO21
            ON UPO21.UserID = u.UserID AND UPO21.PropertyDefinitionID = 399

In above query, numbers in red are PropertyDefinitionIds for respective profile properties. You will need to change these numbers as per your database. To get PropertyDefinitionID, you can use below query in your dnn database.

select * from profilepropertydefinition where portalid= order by propertyName

Take special care of PortalId while retrieving propertiesDefinitionIds as their can be several versions of profile property, i.e., one for each portal.

2 comments:

  1. Great post. Thanks for sharing. This is exactly what I was looking for. Ir really works.
    Thanks

    ReplyDelete