Hello... My T-SQL is not the best, however, I needed a shortcut to get access to User Profile data via SQL. What I wanted is a simple Member Directory pulled from User data while I work on a Module of my own design. You can see it in action at www.letipbuxmont.com/dnn/MemberDirectory/tabid/68/Default.aspx. The display of the data was performed by SqlGridSelectedView from www.tressleworks.ca.
So I created two SPs: 1) a simple procedure that would search the property index and value string to extra a single field worth of data and 2) write another SP that would join the right tables togethier and return the any required Fields from various User Tables and the DATA burried in the membership Profile. My simple exec statement used by SqlGridSelectedView was: epfw_aspnet_user_membership_all '0', 10.
If any one has thoughts on a better T-SQL code I would like to here your input. Also, I know that this Script does not have traps for many of the possible error conditions so keep that in mind when using this script...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[epfw_aspnet_user_membership_all]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[epfw_aspnet_user_membership_all]
GO
CREATE PROCEDURE epfw_aspnet_user_membership_all
@MyApplicationName nvarchar(256),
@MyRoleID int = 1
AS
DECLARE @LeftColumn nvarchar(2000)
DECLARE @RightColumn nvarchar(2000)
DECLARE @PropertyNames nvarchar(2000)
DECLARE @PropertyValuesString nvarchar(2000)
DECLARE @PropertyFieldName nvarchar(100)
DECLARE @UserID int
DECLARE @GuidUserID uniqueidentifier
DECLARE @GuidApplicationID uniqueidentifier
DECLARE @ApplicationName nvarchar(100)
DECLARE @RoleID int
DECLARE @UserName nvarchar(100)
DECLARE @eMail nvarchar(200)
DECLARE @IsSuperUser bit
DECLARE @IsApproved bit
DECLARE @IsLockedOut bit
DECLARE @PropDataFirstName nvarchar(100)
DECLARE @PropDataLastName nvarchar(100)
DECLARE @PropDataUnit nvarchar(100)
DECLARE @PropDataStreet nvarchar(100)
DECLARE @PropDataCity nvarchar(100)
DECLARE @PropDataRegion nvarchar(100)
DECLARE @PropDataPostalCode nvarchar(20)
DECLARE @PropDataTelephone nvarchar(25)
DECLARE @PropDataFax nvarchar(25)
DECLARE @PropDataWebsite nvarchar(100)
If @MyRoleID <> -1
BEGIN
DECLARE DnnUserCursor CURSOR FOR
SELECT DnnUsers.UserID As UserID, MemberUsers.UserID As GuidUserID, MemberApplications.ApplicationID As GuidApplicationID,
MemberApplications.ApplicationName As ApplicationName, UserRoles.RoleID As RoleID, MemberUsers.LoweredUserName As UserName,
Membership.LoweredEmail As eMail, MemberProfile.PropertyNames As PropertyNames, MemberProfile.PropertyValuesString As PropertyValuesString,
DnnUsers.IsSuperUser As IsSuperUser, Membership.IsApproved As IsApproved, Membership.IsLockedOut As IsLockedOut
FROM (((((Users As DnnUsers INNER JOIN aspnet_Users AS MemberUsers ON DnnUsers.UserName = MemberUsers.LoweredUserName)
INNER JOIN UserRoles ON DnnUsers.UserID = UserRoles.UserID)
INNER JOIN aspnet_Applications AS MemberApplications ON MemberUsers.ApplicationID = MemberApplications.ApplicationID)
INNER JOIN aspnet_Profile AS MemberProfile ON MemberUsers.UserID = MemberProfile.UserID)
INNER JOIN aspnet_Membership AS Membership ON MemberUsers.UserID = Membership.UserID)
WHERE MemberApplications.ApplicationName = @MyApplicationName
AND UserRoles.RoleID = @MyRoleID
AND MemberUsers.LoweredUserName <> 'admin'
ORDER By MemberUsers.LoweredUserName
FOR READ ONLY
END
ELSE
BEGIN
DECLARE DnnUserCursor CURSOR FOR
SELECT DnnUsers.UserID As UserID, MemberUsers.UserID As GuidUserID, MemberApplications.ApplicationID As GuidApplicationID,
MemberApplications.ApplicationName As ApplicationName, UserRoles.RoleID As RoleID, MemberUsers.LoweredUserName As UserName,
Membership.LoweredEmail As eMail, MemberProfile.PropertyNames As PropertyNames, MemberProfile.PropertyValuesString As PropertyValuesString,
DnnUsers.IsSuperUser As IsSuperUser, Membership.IsApproved As IsApproved, Membership.IsLockedOut As IsLockedOut
FROM (((((Users As DnnUsers INNER JOIN aspnet_Users AS MemberUsers ON DnnUsers.UserName = MemberUsers.LoweredUserName)
INNER JOIN UserRoles ON DnnUsers.UserID = UserRoles.UserID)
INNER JOIN aspnet_Applications AS MemberApplications ON MemberUsers.ApplicationID = MemberApplications.ApplicationID)
INNER JOIN aspnet_Profile AS MemberProfile ON MemberUsers.UserID = MemberProfile.UserID)
INNER JOIN aspnet_Membership AS Membership ON MemberUsers.UserID = Membership.UserID)
WHERE MemberApplications.ApplicationName = @MyApplicationName
AND MemberUsers.LoweredUserName <> 'admin'
ORDER By MemberUsers.LoweredUserName
FOR READ ONLY
END
--
-- Extra User Data from various Tables are shown to demonstrate
-- the possibility of extracting all of the various "User" Fields
-- and returning the information to the caller of the stored procedure.
--
CREATE TABLE #MyDnnUserTable (
UserID int,
GuidUserID uniqueidentifier,
GuidApplicationID uniqueidentifier,
ApplicationName nvarchar(100),
RoleID int,
UserName nvarchar(100),
eMail nvarchar(200),
IsSuperUser bit,
IsApproved bit,
IsLockedOut bit,
FirstName nvarchar(100),
LastName nvarchar(100),
Unit nvarchar(100),
Street nvarchar(100),
City nvarchar(100),
Region nvarchar(100),
PostalCode nvarchar(20),
Telephone nvarchar(25),
Fax nvarchar(25),
Website nvarchar(100),
LeftColumn nvarchar(1000),
RightColumn nvarchar(1000)
)
OPEN DnnUserCursor
FETCH NEXT FROM DnnUserCursor INTO
@UserID, @GuidUserID, @GuidApplicationID,
@ApplicationName, @RoleID, @UserName,
@eMail, @PropertyNames, @PropertyValuesString,
@IsSuperUser, @IsApproved, @IsLockedOut
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PropertyFieldName = 'FirstName'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataFirstName OUTPUT
SET @PropertyFieldName = 'LastName'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataLastName OUTPUT
SET @PropertyFieldName = 'Unit'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataUnit OUTPUT
SET @PropertyFieldName = 'Street'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataStreet OUTPUT
SET @PropertyFieldName = 'City'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataCity OUTPUT
SET @PropertyFieldName = 'Region'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataRegion OUTPUT
SET @PropertyFieldName = 'PostalCode'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataPostalCode OUTPUT
SET @PropertyFieldName = 'Telephone'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataTelephone OUTPUT
SET @PropertyFieldName = 'Fax'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataFax OUTPUT
SET @PropertyFieldName = 'Website'
exec epfw_aspnet_membership_property_get @PropertyNames, @PropertyValuesString, @PropertyFieldName, @PropDataWebsite OUTPUT
SET @LeftColumn = @PropDataLastName + ', ' +@PropDataFirstName
IF LEN(@PropDataTelephone) > 0
SET @LeftColumn = @LeftColumn + '<br>' + @PropDataTelephone + ' (Phone)'
IF LEN(@PropDataFax) > 0
SET @LeftColumn = @LeftColumn + '<br>' + @PropDataFax + ' (Fax)'
IF LEN(@eMail) > 0
SET @LeftColumn = @LeftColumn + '<br>' + '<A href="mailto:' + @eMail + '">' + @eMail + '</a>'
IF LEN(@PropDataWebsite) > 0
SET @LeftColumn = @LeftColumn + '<br>' + '<A target="_blank" href="http://' + @PropDataWebsite + '">' + @PropDataWebsite + '</a>'
SET @RightColumn = ''
IF LEN(@PropDataUnit) > 0
SET @RightColumn = @PropDataUnit
IF LEN(@RightColumn) > 0
SET @RightColumn = @RightColumn + '<br>'
SET @RightColumn = @RightColumn + @PropDataStreet + '<br>'
SET @RightColumn = @RightColumn + @PropDataCity + ', ' + @PropDataRegion + ' ' + @PropDataPostalCode + '<br>'
INSERT INTO #MyDnnUserTable (UserID, GuidUserID, GuidApplicationID,
ApplicationName, RoleID, UserName,
eMail, IsSuperUser, IsApproved, IsLockedOut,
FirstName, LastName, Unit, Street, City, Region,
PostalCode, Telephone, Fax, Website, LeftColumn, RightColumn)
VALUES (@UserID, @GuidUserID, @GuidApplicationID,
@ApplicationName, @RoleID, @UserName,
@eMail, @IsSuperUser, @IsApproved, @IsLockedOut,
@PropDataFirstName, @PropDataLastName, @PropDataUnit,
@PropDataStreet, @PropDataCity, @PropDataRegion,
@PropDataPostalCode, @PropDataTelephone,
@PropDataFax, @PropDataWebsite, @LeftColumn, @RightColumn)
FETCH NEXT FROM DnnUserCursor INTO
@UserID, @GuidUserID, @GuidApplicationID,
@ApplicationName, @RoleID, @UserName,
@eMail, @PropertyNames, @PropertyValuesString,
@IsSuperUser, @IsApproved, @IsLockedOut
END
CLOSE DnnUserCursor
DEALLOCATE DnnUserCursor
SELECT LeftColumn As 'Name, Email and Phone Number(s)', RightColumn As 'Address'
FROM #MyDnnUserTable
ORDER BY LastName
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[epfw_aspnet_membership_property_get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[epfw_aspnet_membership_property_get]
GO
CREATE PROCEDURE epfw_aspnet_membership_property_get
@PropNames nvarchar(2000),
@PropValues nvarchar(2000),
@PropFieldName nvarchar(200),
@PropData nvarchar(200) OUTPUT
AS
DECLARE @strPropChar nvarchar(1)
DECLARE @strPropPos nvarchar(200)
DECLARE @intPropPos int
DECLARE @strPropLen nvarchar(200)
DECLARE @intPropLen int
DECLARE @LEN int
DECLARE @POS int
DECLARE @IDX int
SET @LEN = LEN(@PropNames)
Select @POS=PATINDEX ('%' + @PropFieldName + '%', @PropNames) + LEN(@PropFieldName) + 3
SET @strPropPos = ''
SET @IDX = @POS
WHILE @IDX <= @LEN
BEGIN
SET @strPropChar = SUBSTRING(@PropNames, @IDX, 1)
IF @strPropChar = ':' BREAK
SET @strPropPos = @strPropPos + @strPropChar
SET @IDX = @IDX + 1
END
SET @intPropPos = CAST(@strPropPos As Integer) +1
SET @IDX = @IDX + 1
SET @strPropLen = ''
WHILE @IDX <= @LEN
BEGIN
SET @strPropChar = SUBSTRING(@PropNames, @IDX, 1)
IF @strPropChar = ':' BREAK
SET @strPropLen = @strPropLen + @strPropChar
SET @IDX = @IDX + 1
END
SET @intPropLen = CAST(@strPropLen As Integer)
IF @intPropLen <> 0
SET @PropData = SUBSTRING(@PropValues, @intPropPos, @intPropLen)
ELSE
SET @PropData = ''
GO