Paul Brown

Ever wanted to include the profile information from an Asp.Net profile in a query? It’s not that hard once you understand the structure. I’ve written a little function that does all the work. Note: I’m using Sql Server as my repository. First we need to understand how the profile data is stored. Looking at the aspnet_Profile table, we can see that it stores the information in two columns: PropertyNames and PropertyValuesString. Looking at PropertyNames we can see that it has a basic structure of Property Name, Data Type, Starting Position and Length. For example, in the string “FirstName:S:0:4:Phone:S:4:10:LastName:S:14:5:” we can see that FirstName is of type string, starts at position 0 and has a length of 4. Notice the zero base for the starting position, we need to correct for that in our function. This means in the PropertyValuesString “John2175551212Smith”, we would start with the first position and proceed 4 characters to get the name.

In the never ending debate between using datasets and using custom business objects (CBOs), one of the main knocks on CBOs is that they take more time to code. You have to write the properties and create getters and setters for each, code the DB calls, etc. Of course, you could always get yourself a code generator but those cost money. On the other hand, Sql Server 2005 has made this a little easier with some System Views. Look at the Information_Schema views and you’ll find a whole bunch of really nice info. The one we’re going to use today is the Columns view. The first steps we need is to be able to convert from Sql datatypes to .Net types. This handy little Sql function will do our conversion for us. You can simply delete the function when our done generating. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Create date: 03/15/2007 -- Description: Returns the correct Net datatype -- from Sql datatype

Wow, my first blog post. Need to start it off right!! The Steelers Rule!!