Monday, January 26, 2009

fetching user profile information of aspnet users database in dotnetnuke

well for this purpose we need to have a function that fetches the required information from the database tables and then we need views where we can actually execute the function to bring the required records.

=======function (used to access user profile property values)


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_GetProfilePropertyValue]
(
@UserID as Int,
@ProfilePropertyName AS NVARCHAR(100)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @PPValue NVARCHAR(4000)
-- If input is invalid, return null.
IF @UserID IS NULL
OR LEN(@UserID) = 0
OR @ProfilePropertyName IS NULL
OR LEN(@ProfilePropertyName) = 0
RETURN NULL

SELECT @PPValue = dbo.UserProfile.PropertyValue
FROM
dbo.ProfilePropertyDefinition INNER JOIN
dbo.UserProfile ON dbo.ProfilePropertyDefinition.PropertyDefinitionID = dbo.UserProfile.PropertyDefinitionID
WHERE (dbo.ProfilePropertyDefinition.PropertyName = @ProfilePropertyName) AND (dbo.UserProfile.UserID = @UserID)
Return @PPValue
END



===================================================

View (where the above mentioned function is used to get the required information)

SELECT dbo.Users.UserID, dbo.Users.Username, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'FirstName') AS FirstName, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'LastName') AS LastName, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'Company') AS Company, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'MyPhoto') AS MyPhoto, dbo.Users.Email, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'Telephone') AS Telephone, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'Street') AS Street, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'City') AS City, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'PostalCode') AS PostalCode, dbo.fn_GetProfilePropertyValue(dbo.Users.UserID, N'Country') AS CountryFROM dbo.Users INNER JOIN dbo.vuRSA ON dbo.vuRSA.UserID = dbo.Users.UserID





1 comment: