Solved

SQL Query for DotNetNuke Profile Fields

Posted on 2008-10-31
5
1,769 Views
Last Modified: 2012-08-13
Greeting.  I am in the process of creating membership reports for a DotNetNuke portal with a few thousand members.  For the life of me, I can't seem to be able to add the entended profile fields to my query (attached).  I want to call members by roles and display extended profile fields like address and such.  The attached querry works perfectly with the caveat that it only pulls data from the default user table.  Any ideas on how to modify this code?  Thanks.

FOUND THE ANSWER ON THE FORUM.  For others with a similar question, follows is the sample query.
Select U.Username As [Krewe Number], U.FirstName As [First Name],
  R.RoleName As [Member Level], AM.Email As [E-Mail], (Select UP.PropertyValue
  From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 40) As [Costume Size]
From Users U Inner Join
  UserRoles UR On U.UserID = UR.UserID Inner Join
  Roles R On UR.RoleID = R.RoleID Inner Join
  Aspnet_users AU On U.Username = AU.UserName Inner Join
  Aspnet_membership AM On AU.UserId = AM.UserId
Where R.RoleName = 'Registered Users'

Open in new window

0
Comment
Question by:MarkNewOrleans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 22856138
this code is calling by Where R.RoleName = 'Registered Users'
what other roles are in the table?

0
 

Author Comment

by:MarkNewOrleans
ID: 22856402
For my purposes -- A Mardi Gras Krewe in New Orleans with over 3,000 members -- I use the above query to display extended profile fields from the core DNN profile tables and filter the results by Role.  I am actually using the above query in SQL Reporting Services to generate table relationships for reports. Works like a charm.  Attached is a better example of how I am using this statement.

Note that DotNetNuke stores profile data with a lot of complex table relationships. It's not for the weak at heart and can be very difficult for the "unitiated."

The attached SQL simplifies this for you.  But, you will still need to open the DNN ProfilePropertyDefenitions table and find the numeric value for each desired profile field.  I open the table once, sort the values one time using the TextName and the NumericFields and paste them into Notepad so I have an easy reference and just cut and paste the values I want to add to my reports.

To add profile fields use  the following expession:

 (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 28) As Costume Size,

Lines to note in the above code snippet:
PropertyDefinitionID = 28  

CHANGE "28" to the desired profile field from the values found in your DNN ProfilePropertyDefinition table.

Change the last line to the "Name of Role " you want use as a filter ----  CASE SENSITIVE ----

EXAMPLE: Where R.RoleName = 'Any User Role'

Best way for newbies to see how this works is to get a nice SQL Reporting tool and run the querry against their SQL table.  I actually started like this to speed up the learning curve in respect to all the table relationships in DotNetNuke.  It can be a lot to think about if your just getting started.  

I always say, It's "easy when you know how."

Happy Mardi Gras!

Select U.Username As [Krewe Number], U.LastName As [Last Name], U.FirstName As
  [First Name], AM.Email As [E-Mail], (Select UP.PropertyValue
  From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID =
    40) As [Costume Size], (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 28) As State,
  (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 27) As City,
  (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 26) As Address1,
  (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 45) As Address2,
  (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 39) As [Float Name],
  (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 30) As [Postal Code],
  (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID =
    70) As [Member Class], R.RoleName, (Select UP.PropertyValue
  From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 54) As [Member Level]
From Users U Inner Join
  UserRoles UR On U.UserID = UR.UserID Inner Join
  Roles R On UR.RoleID = R.RoleID Inner Join
  Aspnet_users AU On U.Username = AU.UserName Inner Join
  Aspnet_membership AM On AU.UserId = AM.UserId, vw_Users
Where (Select UP.PropertyValue From UserProfile UP
  Where U.UserID = UP.UserID And UP.PropertyDefinitionID = 70) =
  'Signature Float w/ Coronation 09' And R.RoleName = 'Krewe Member'

Open in new window

0
 
LVL 1

Expert Comment

by:cnelson67
ID: 23099213
I am also hunting down the a quick & easy DataType = Description reference table, I also must 'infer' the data type based on data in the table. I have tried searching for data type enums everywhere I can think of, but have yet to come up with this one. Please let me know should you find the answer,

In the mean time, for your report:

There are a number of stored procedures and functions used to operate on the ProfilePropertyDefinition table, including dbo.getUserProfile (UserID)

encapsulate the getUserProfile procedure by an outer query supplying userid and you will have all the profiles to match the userid for the report.

note - to discover other sp's etc, right click on any table and choose View Dependecies from Management Studio, all attached dependecies are noted along with related dependencies.

I will post the DataType reference should I discover one.
0
 
LVL 1

Accepted Solution

by:
cnelson67 earned 500 total points
ID: 23148011
FOUND - DNN ProfilePropertyDefinition.DataType enumeration.

This is implemented as a DNN List, It can be found on the Host tab under the List selection.

from the database, the following query will extract the list from dbo.Lists

Select * from dbo.lists where listname = 'DataType'

The value in the 'Text' field is a control type to be associated with the datatype for dynamic rendering.

Hope this helps.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question