{ Duane.Wingett }

Create VB.Net class from Sql Server table

Have you ever wanted to be able to create VB.Net classes that have fields that emulate the columns in your SQL server tables?

Well if you have, then read on...

A little project I am working on at home has a table with a lot of columns, and the architecture I am writing the application to requires an entity which will emulate this table. I couldn't bear the idea of writing all those private fields and public properties out, even with Visual Studios "Property" snippet.

So I got to thinking, can I query the database and output a class structure in VB.Net language. Well with the help of SQL Server Cursors, yes I can.

----------------------------------
-- DW: 11/04/2011
-- This script will build a class file with 
-- private fields and public properties from
-- the table specified in the @TableName variable.
----------------------------------

DECLARE @TableName varchar(50)
SET @TableName = 'MyTable'

SET NOCOUNT ON; -- Hide row count so printed output is not affected

DECLARE @DataTypeName varchar(50)
DECLARE @NewLine char
DECLARE @ColumnName varchar(50)
DECLARE @DataType varchar(50)
DECLARE @FieldName varchar(50)

--SET @NewLine = char(13)

-- Start Output
--PRINT '' + @NewLine;
PRINT 'Public Class ' + @TableName;
PRINT '';
PRINT '#Region "Declarations"';
PRINT '';

-- Declarations
DECLARE DeclarationCursor CURSOR SCROLL FOR 
    SELECT
        columns.name [ColumnName],
        CASE 
            WHEN columns.system_type_id = 34    THEN 'Byte[]'
            WHEN columns.system_type_id = 35    THEN 'String'
            WHEN columns.system_type_id = 36    THEN 'System.Guid'
            WHEN columns.system_type_id = 48    THEN 'Byte'
            WHEN columns.system_type_id = 52    THEN 'Short'
            WHEN columns.system_type_id = 56    THEN 'Integer'
            WHEN columns.system_type_id = 58    THEN 'System.DateTime'
            WHEN columns.system_type_id = 59    THEN 'float'
            WHEN columns.system_type_id = 60    THEN 'Decimal'
            WHEN columns.system_type_id = 61    THEN 'System.DateTime'
            WHEN columns.system_type_id = 62    THEN 'double'
            WHEN columns.system_type_id = 98    THEN 'Object'
            WHEN columns.system_type_id = 99    THEN 'String'
            WHEN columns.system_type_id = 104   THEN 'Boolean'
            WHEN columns.system_type_id = 106   THEN 'Decimal'
            WHEN columns.system_type_id = 108   THEN 'Decimal'
            WHEN columns.system_type_id = 122   THEN 'Decimal'
            WHEN columns.system_type_id = 127   THEN 'long'
            WHEN columns.system_type_id = 165   THEN 'Byte[]'
            WHEN columns.system_type_id = 167   THEN 'String'
            WHEN columns.system_type_id = 173   THEN 'Byte[]'
            WHEN columns.system_type_id = 175   THEN 'string'
            WHEN columns.system_type_id = 189   THEN 'Long'
            WHEN columns.system_type_id = 231   THEN 'String'
            WHEN columns.system_type_id = 239   THEN 'String'
            WHEN columns.system_type_id = 241   THEN 'String'
            WHEN columns.system_type_id = 241   THEN 'String'
        END [DataType]
FROM              sys.tables tables
    INNER JOIN    sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
    INNER JOIN    sys.columns columns ON (columns.object_id = tables.object_id) 
WHERE
    tables.name = @TableName
ORDER BY 
    columns.object_id ASC;


OPEN DeclarationCursor;

FETCH NEXT FROM DeclarationCursor 
INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FieldName = '_' + LOWER(SUBSTRING(@ColumnName, 1,1)) + SUBSTRING(@ColumnName, 2, LEN(@ColumnName)-1)
    PRINT '    Private ' + @FieldName + ' As ' + @DataType;

    FETCH NEXT FROM DeclarationCursor 
    INTO @ColumnName, @DataType;
END
PRINT ''; 
PRINT '#End Region';
PRINT ''; 
PRINT '#Region "Properties"';
PRINT '';

FETCH FIRST FROM DeclarationCursor 
INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FieldName = '_' + LOWER(SUBSTRING(@ColumnName, 1,1)) + SUBSTRING(@ColumnName, 2, LEN(@ColumnName)-1)

    PRINT '    Public Property ' + @ColumnName + ' As ' + @DataType;
    PRINT '        Get';
    PRINT '            Return ' + @FieldName;
    PRINT '        End Get';
    PRINT '        Set';
    PRINT '            ' + @FieldName + ' = value';
    PRINT '        End Set';
    PRINT '    End Property';
    PRINT ''; 

    FETCH NEXT FROM DeclarationCursor 
    INTO @ColumnName, @DataType;
END

PRINT ''; 
PRINT '#End Region';
PRINT ''; 


DECLARE @FIELDS varchar(max)
SET @FIELDS = ''

FETCH FIRST FROM DeclarationCursor 
INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FieldName = LOWER(SUBSTRING(@ColumnName, 1,1)) + SUBSTRING(@ColumnName, 2, LEN(@ColumnName)-1)
    SET @FIELDS = @FIELDS + 'byval ' + @FieldName + ' as ' + @DataType + ', '

    FETCH NEXT FROM DeclarationCursor 
    INTO @ColumnName, @DataType;
END

SET @FIELDS = SUBSTRING(@FIELDS, 1, (LEN(@FIELDS) - 1))

PRINT '#Region "Constructors"';
PRINT '';
PRINT '    Public Sub New()';
PRINT '        ';
PRINT '    End Sub';
PRINT '';
PRINT '    Public Sub New(' + @FIELDS + ')'
PRINT '        Me.New()';
PRINT '    End Sub';
PRINT '';
PRINT '#End Region';
PRINT ''; 

CLOSE DeclarationCursor;
DEALLOCATE DeclarationCursor;

-- End of Class
PRINT 'End Class';

The class also includes a parametricised public constructor, too.

( Feel free to use all or any of this code how ever you choose. Feel free to post it on your own blog, as long as you credit this page with a back link. )

I hope that this snippet of SQL will help you as much as it has helped me today.

Best regards,
Duane.

Tuesday, April 12, 2011 7:29:18 PM (GMT Standard Time, UTC+00:00)