First, I just wanted to say THANK YOU! to all of the core team members who worked on returning the User Profile storage back into a human & more easily machine readable form !!! The pre-3.3 provider was a nightmare.

While developing a custom module last year, I had the requirement to develop a search-able list of DNN users, and happened across the excellent article by Richard Edwards on DNNStuff.com : "Where is it stored: aspnet_Profile table ." which described in detail how to write to SQL Functions to return the value of a given profile property. Using these functions, it was a snap to write another view which passed in named properties, ultimately allowing a query-able view of extended profile properties such as Address, City, State, etc.

My question is, has anybody written a similar SQL View for the much better and much improved DNN 3.3 membership structure? Just curious, I suppose I could adapt my older procedure to work with the new format without to much sweat.

Again, thank you to everyone who contribute your time to making DotNetNuke some of the most satisfying software I get to work with, you rock!

I ended up doing something similar to the solution I referenced:

Listing 1:

CREATE  FUNCTION dbo.GetUsersProfilePropertyByName

       (

       @UserID                                  int,

       @PortalID                         int,

       @PropertyName              nvarchar(50)

       )

RETURNS nvarchar(50)

AS

       BEGIN

       DECLARE @DefinitionID int

       SET @DefinitionID = (dbo.GetProfilePropertyDefinitionID(@PortalID, @PropertyName))

       Declare @Value nvarchar(50)

       Select @Value = (

              Select Top 1 PropertyValue

              FROM dbo.UserProfile

              WHERE UserID = @UserID

              AND PropertyDefinitionID = @DefinitionID

              )

       RETURN ISNULL(@Value, '')

       END

Listing 2:

Create  VIEW dbo.vw_Users_AF_Membership

AS

SELECT     U.UserID, U.Username, dbo.GetUsersProfilePropertyByName(U.UserID, UP.PortalId, N'FirstName') AS FirstName,

                      dbo.GetUsersProfilePropertyByName(U.UserID, UP.PortalId, N'LastName') AS LastName, UP.PortalId, '' AS Location,

                      dbo.GetUsersProfilePropertyByName(U.UserID, UP.PortalId, N'WebSite') AS WebSite, 0 AS Posts, 0 AS UserDetailsID,

                      dbo.GetUsersProfilePropertyByName(U.UserID, UP.PortalId, N'City') AS City, dbo.GetUsersProfilePropertyByName(U.UserID, UP.PortalId, N'Region')

                      AS Region, dbo.GetUsersProfilePropertyByName(U.UserID, UP.PortalId, N'Country') AS Country, UP.PortalId AS ApplicationName, GETDATE()

                      AS LastActivityDate, GETDATE() AS CreateDate

FROM         dbo.Users U INNER JOIN

                      dbo.UserPortals UP ON U.UserID = UP.UserId

WHERE     (UP.PortalId = 0)

My posted solution should really suffice: I even took the time to nicely format the SQL: Copying and pasting the SQL from Query Analyzer into Visual Studio, then copying and pasting the resultant color coded SQL into Office Word 2003, then pasting that into the window, and when I look at the final posted form, it still looks nicely color coded (thanks forum admin for enabling this functionality btw as it significantly enhances the ever important ability to exchange information).

That being said, you should be able to copy and paste the Create Function statement from my earlier post into query analyzer to create the function, then modify the view to match your own needs, all the pieces parts are there. If the SQL Server is accessible, I would be happy to do it for you for a modest donation to my paypal account.

-Kenneth S. Courtney