Recently, I was working on a project that used ASP.NET Membership and Profiles. The
application worked great, but there was a problem with the user profile data and
reporting.
Using aspnet Membership and Profiles is great. It's quite easy to retrieve,
update, and insert profile data using .NET. However, our reporting team was
having difficulty retrieving the user profile data for their reporting cubes and
data warehouse. This wouldn't be a problem using CLR in SQL Server 2005 or
2008, but for this particular project we were still on SQL Server 2000.
After an exhaustive web search I was unable to find any quick and easy way to
retrieve the data using anything other than .NET code. This simply would not
do. Surely there is a way to do this, right? Perhaps a
User Defined
Function?
So, I set about writing my own User Defined Function for
this project.
CREATE FUNCTION [dbo].[aspnet_Profile_GetProfile]
(
@UserID UNIQUEIDENTIFIER,
@PropertyName varchar(100),
@ApplicationName NVARCHAR(256) = NULL
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @ApplicationID UNIQUEIDENTIFIER
DECLARE @StartPosition INT
DECLARE @PropertyLength INT
DECLARE @Return VARCHAR(100)
IF @ApplicationName IS NULL
SELECT TOP 1 @ApplicationName = LoweredApplicationName, @ApplicationID = ApplicationID FROM aspnet_Applications ORDER BY ApplicationId
ELSE
SELECT @ApplicationID = ApplicationID FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
DECLARE @RowData nvarchar(2000)
DECLARE @SplitOn nvarchar(5)
DECLARE @PropertyTable table
(
Id int identity(1,1),
Property nvarchar(100),
DataType CHAR(1),
Start INT,
Length INT
)
SELECT @RowData = PropertyNames
FROM aspnet_Profile
WHERE UserID = @UserID
AND PropertyNames LIKE '%'+@PropertyName+'%'
SET @SplitOn = ':'
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @PropertyTable (Property)
Select ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
UPDATE @PropertyTable SET DataType = (
Select ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))))
WHERE Id = @@IDENTITY
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
UPDATE @PropertyTable SET Start = (
Select ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))))
WHERE Id = @@IDENTITY
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
UPDATE @PropertyTable SET Length = (
Select ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))))
WHERE Id = @@IDENTITY
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
END
SELECT @StartPosition = Start, @PropertyLength = Length FROM @PropertyTable WHERE Property = @PropertyName
SELECT @Return = SUBSTRING(PropertyValuesString, @StartPosition + 1, @PropertyLength)
FROM aspnet_Profile
WHERE UserID = @UserID
AND PropertyNames LIKE '%'+@PropertyName+'%'
RETURN @Return
END
GO
This was simple enough and seems to work rather well. All you have to do is
pass in the UserID, PropertyName, and ApplicationName and you get back the
ProperyValue of the user profile. Here's the usage information:
SELECT dbo.aspnet_Profile_GetProfile(UserID, PropertyName, ApplicationName)
I also included a default NULL value for ApplicationName, for the cases where
there is only one application and/or the ApplicationName is unknown to the
user. In this instance it will grab the first application from the
aspnet_Applications table. Here's an example retrieving FirstName and LastName
for all aspnet_users:
SELECT userid, username,
dbo.aspnet_Profile_GetProfile(userid, 'FirstName', DEFAULT) AS FirstName,
dbo.aspnet_Profile_GetProfile(userid, 'LastName', DEFAULT) AS LastName
FROM aspnet_users
Here's another example passing in the ApplicationName, joining to the
aspnet_Applications table. Please note that the syntax is the same as any
standard SQL query. Return as little or as much information as you like.
SELECT LU.UserID, LU.UserName,
dbo.aspnet_Profile_GetProfile(LU.UserID, 'FirstName', AP.ApplicationName) AS FirstName,
dbo.aspnet_Profile_GetProfile(LU.UserID, 'LastName', AP.ApplicationName) AS LastName
FROM dbo.aspnet_Users LU
INNER JOIN dbo.aspnet_Applications AP ON LU.ApplicationID = AP.ApplicationID
WHERE AP.ApplicationName = '[ApplicationName]'
Hopefully this will help those that may be struggling with how to retrieve
profile data from the aspnet_Profiles table. This is done quite easily through
.NET, but somewhat combersome with SQL. The User Defined Function has the added
benefit of a single point for code change, should Microsoft change the way the
data is stored in future releases.
More resources:
If you have any questions, comments, or concerns please use the Contact
Form.
Download
UDF_aspnet_Profile_GetProfile_v1.zip (1.19 kb)