I’ve used the ASP.Net Membership on a few projects and worked through the struggles of making it a standard in our environments. As many of you know, I use Blogengine.Net as my common base. The consistency allows me to lower my investment in each client I support.
I had to create some reports using the profile data in a back-end batch job. I had a stored proc and the requirement was to join a few variables from the profile.
There are a few ways to accomplish this. In my first attempt, I hard coded the parsing into the stored proc. This is a pain, since the code is messy. After some discussion, John developed a User Defined Function to handle this. It was a simple solution and allowed others to quickly use it for many data warehouse chores.
Please download the aspnet_Profile_GetProfile UDF from John’s site and review the sample below. Add the UDF getprofile to any stored proc to include the profile fields (like 'LASTNAME' profile field).
Thank You for listening.
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]'
Alternate download: UDF_aspnet_Profile_GetProfile_v1.zip (1.19 kb)