Skip to main content

Generate Custom Business Object Properties from SQL Server Tables

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.

-- =============================================
-- Create date: 03/15/2007
-- Description: Returns the correct Net datatype
-- from Sql datatype
-- =============================================
    @SqlDataType nvarchar(128)
RETURNS nvarchar(128)
-- Declare the return variable here
    DECLARE @NetDataType nvarchar(128)
    SELECT @NetDataType = CASE
        WHEN @SqlDataType = 'varbinary' THEN 'Byte[]'
        WHEN @SqlDataType = 'binary' THEN 'Byte[]'
        WHEN @SqlDataType = 'varchar' THEN 'String'
        WHEN @SqlDataType = 'char' THEN 'String'
        WHEN @SqlDataType = 'nvarchar' THEN 'String'
        WHEN @SqlDataType = 'nchar' THEN 'String'
        WHEN @SqlDataType = 'text' THEN 'String'
        WHEN @SqlDataType = 'ntext' THEN 'String'
        WHEN @SqlDataType = 'uniqueidentifier' THEN 'Guid'
        WHEN @SqlDataType = 'rowversion' THEN 'Byte[]'
        WHEN @SqlDataType = 'bit' THEN 'Boolean'
        WHEN @SqlDataType = 'tinyint' THEN 'Byte'
        WHEN @SqlDataType = 'smallint' THEN 'Int16'
        WHEN @SqlDataType = 'int' THEN 'Integer'
        WHEN @SqlDataType = 'bigint' THEN 'Int64'
        WHEN @SqlDataType = 'smallmoney' THEN 'Decimal'
        WHEN @SqlDataType = 'money' THEN 'Decimal'
        WHEN @SqlDataType = 'numeric' THEN 'Decimal'
        WHEN @SqlDataType = 'decimal' THEN 'Decimal'
        WHEN @SqlDataType = 'real' THEN 'Single'
        WHEN @SqlDataType = 'float' THEN 'Double'
        WHEN @SqlDataType = 'smalldatetime' THEN 'DateTime'
        WHEN @SqlDataType = 'datetime ' THEN 'DateTime'
    RETURN @NetDataType

Now we can run a query against Information_Schema.Columns to get the columns of our table and gen the code for the properties. I’ve included VB and C# codes. It may be easier to get the code from the Text (Ctrl-T) of the result window rather than the grid view (Ctrl-D).

DECLARE @CRLF as varchar(2)
Set @CRLF = CHAR(13) + CHAR(10)
DECLARE @TAB as varchar(2)
Set @TAB = CHAR(9)
-- This produces VB code
SELECT 'private m' + Column_Name + ' as ' + dbo.GetNetDataType(data_type) + @CRLF
    + 'Public Property ' + Column_Name + ' as ' + + dbo.GetNetDataType(data_type) + @CRLF
    + 'Get' + @CRLF
    + 'return m' + Column_Name + @CRLF
    + 'End Get' + @CRLF
    + 'Set(value as ' + dbo.GetNetDataType(data_type) + ')' + @CRLF
    + 'm' + Column_Name + ' = value ' + @CRLF
    + 'End Set' + @CRLF
    + 'End Property' + @CRLF + @CRLF
FROM information_schema.columns
WHERE Table_Name = 'MyTable'
ORDER BY Column_Name

-- This produces C# code
SELECT @TAB + 'private ' + dbo.GetNetDataType(data_type) + ' m' + Column_Name + ';' + @CRLF
    + @TAB + 'public ' + dbo.GetNetDataType(data_type) + ' ' + Column_Name + ' {' + @CRLF
    + @TAB + @TAB + 'get {' + @CRLF
    + @TAB + @TAB + @TAB + 'return m' + Column_Name + ';' + @CRLF
    + @TAB + @TAB +  '}' + @CRLF
    + @TAB + @TAB + 'set { ' + @CRLF
    + @TAB + @TAB + @TAB + 'm' + Column_Name + ' = value;' + @CRLF
    + @TAB + @TAB + '}' + @CRLF
    + @TAB + '}' + @CRLF  + @CRLF
FROM information_schema.columns
WHERE Table_Name = 'MyTable'
ORDER BY Column_Name
Next time I’ll look at how to generate the parameters for your stored procedures.


Popular posts from this blog

JavaScript function to automatically add slashes to date

In converting an old Windows app to a browser app, the user wanted to be able to enter dates without the slashes. Here's a simple jscript: 1:// Function to convert short date string (MMddyy) 2:// or (MMddyyyy) to a date string (mm/dd/yyyy). 3:// txtBox is the actual textbox control 4:// with the value to be processed. 5:function FixShortDate(txtBox) { 6:if (txtBox == null) { 7:return'' } 8: 9:var re = new RegExp(/(\d{6})(\d{2})?/); 10: 11:if (re.test(txtBox.value)) 12: { 13:if (txtBox.value.length == 8) { 14: txtBox.value = txtBox.value.substring(0, 2) + '/' + txtBox.value.substring(2, 4) + '/' + txtBox.value.substring(4, 8) 15: } 16: 17:if (txtBox.value.length == 6) { 18:if (txtBox.value.substring(4, 6) < 20)

Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.UserProfileContains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.webpages_MembershipStores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.webpages_OAuthMembershipStor…

Get Asp.Net Profile properties from Sql

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.

You might be thinking …