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.

DNN cache problem

Hi All,

Today I faced a very strange problem on one of the DNN sites that I manage. The problem was reported by my client and as per him some profile properties shown on user accounts screen were showing incorrect data sometimes. At first I thought that, a few changes that we made to display extra profile properties on user accounts screen might be posing problems, so I rechecked my code twice. But all went to wane. Then I thought it may be due to DNN cache, so I placed a single line code to clear cache everytime a new user is created. And lol!, the problem was resolved.

Code I used to clear cache

DataCache.ClearCache()

Store complete data table in database

Hi All,

Below is a simple method that can store complete datatable to database in a go.

private static bool StoreInDataBase(DataTable dtDetails, string connectionString, string tableName)
    {
        if (dtDetails != null && dtDetails.Rows.Count > 0)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
                {
                    bulkcopy.DestinationTableName = tableName;
                    try
                    {
                        bulkcopy.WriteToServer(dtDetails);
                    }
                    catch (Exception ex)
                    {

                                        //Log Exception
                        return false;
                    }
                }
                connection.Close();
            }
        }
        return true;
    }

Its better than iterating complete dataTable and writing one record at a time.

More than one view or edit controls in a custom DNN module

In DNN custom modules, we can have multiple view/edit controls.

Below are the steps to do so:

• Add a new control to your module, to do so go to

Host Menu >> Module Definition >> Edit module by clicking pencil icon on left

• On clicking “Add Module Control”, you will get a screen as below
• Enter a key; this will serve as id when you will like to traverse to this control from some other control.
• Select control from drop down list (of course the control needs to already exist in modules folder which in turn is under desktop modules folder). Things will look somewhat like below image.

• Adding a new landing control
• Edit your previous landing control by editing it and assigning it a control key.

At this point of time if you will visit any page with this module added, you will get a blank page as there is no specified landing control for your module.

• Now add a new control in similar way as above with just a single difference. This time you don’t have to assign the control query. A control with no control key serves as a landing page.

• Now its all set, put up your required logic on controls and access your newly added controls. To access these, you can use below code snippet

hlnkNewView.NavigateUrl = Globals.NavigateURL(PortalSettings.ActiveTab.TabID, "newView", "mid=" + ModuleId.ToString());

o       You can find Globals.NavigateURL under DotNetNuke.Common class.

o       First parameter of NavigateURL method is TabId of current page.

o       Second parameter is control id given by us ("newView" in our case here).

o       Third parameter is moduleId of current module.

Here hlnkNewView is an ASP Hyperlink control; you can use some other also ;)

This is all that needs to be done for having more than one view/edit controls in a single custom DNN Module.

Apart from this approach you can use ASP.NET MultiView control to manage multiple views. Rafe Kemmis has a great post about it at Managing Views In Your DotNetNuke Module