We help IT Professionals succeed at work.

How do I get aspnet_Profile info out in a regular SQL statement?

I have a grid that lists sales. I load the grid with a SQL statement and get the username. Then once I have the username I have to use the ItemDataBound event of the grid and on each row grab the username and use that to get the First and Last name of the user.

Dim userProfile As ProfileBase = ProfileBase.Create(username, True)
userProfile.GetPropertyValue("FirstName") & " " & userProfile.GetPropertyValue("LastName")

This works, but slows down the loading of the grid tremendously. Is there a way in my original SQL statement to retrieve the First and Last Name of the user instead of UserName?

SELECT dbo.Deal.DealNumber, dbo.Customer.DisplayName, dbo.Deal.DealDate, SUM(dbo.DealProduct.Profit) AS Profit, dbo.aspnet_Users.UserName
FROM  dbo.aspnet_Users INNER JOIN
dbo.Deal ON dbo.aspnet_Users.UserId = dbo.Deal.SalesUserID INNER JOIN
dbo.Customer ON dbo.Deal.CustomerID = dbo.Customer.CustomerID INNER JOIN
dbo.DealProduct ON dbo.Deal.DealID = dbo.DealProduct.DealID
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You are already selecting from  dbo.aspnet_Users table. Does this table contain the other properties that you want? If not there would be another table that you would need to join.

Author

Commented:
no... aspnet_Users only has the username. The aspnet_Profile table holds the First Name and Last Name but as Properties. So that table has two fields PropertyNames & PropertyValueStrings. In the first field it holds the PropertyName:FirstName and LastName and the PropertyValueStrings field holds the values: John Doe.

example below
PropertyName
CompanyID:S:0:2:DeptName:S:2:8:FirstName:S:10:4:LastName:S:14:7:EmployeeNumber:S:21:3:DeptID:S:24:2:SkinName:S:26:7:
PropertyValueStrings
60Sales DeptJohnNichols82117WebBlue

Then Microsoft gives us the
userProfile.GetPropertyValue("FirstName") & " " & userProfile.GetPropertyValue("LastName")
to get the info out...

I was just hoping there was a different way to get the info...
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Are there any views or stored procedures you may be able to use?

http://forums.asp.net/t/1548928.aspx/1

http://blogs.teamb.com/craigstuntz/2010/03/05/38558/

Author

Commented:
I ended up just adding "FirstName" and "LastName" to the aspnet_Users table and running a proc to update all of the users... then have it save those fields when new users are created. My grid now runs about 4 times faster.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.