Virtual-Essential's DotNetNuke, .NET, and SQL Tips For the Developer

First, I created a function to map SQL data types to VB.NET data types. Again, this isn’t a full collection, just enough for my testing.

-- =============================================
-- Author:        Briana Tarrance
-- Create date: 03/10/2010
-- Description:    Fun way to use SQL to generate code for you.
-- copyright(c) 2010. Virtual-Essentials.com and HowToDotNetNuke.com
-- =============================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getVBDataType] 
(
        @coltype varchar(25)
)
RETURNS varchar(25)
AS
BEGIN
        DECLARE @Result varchar(25)
        SELECT @Result = 
            CASE Lower(@coltype)
                WHEN 'int' THEN 'Integer' 
                WHEN '%char%' THEN 'String' 
                WHEN '%text%' THEN 'String' 
                WHEN 'money' THEN 'Decimal'
                WHEN 'datetime' THEN 'DateTime'
                WHEN 'bit' THEN 'Boolean'
                ELSE 'String'
            END
            
        RETURN @Result
END

Then, I created a script that would loop through the tables in my database and create a class for each of them.

DECLARE @Database as varchar(100)
DECLARE tables CURSOR LOCAL FAST_FORWARD FOR 

SELECT  TABLE_NAME, TABLE_CATALOG
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME;

DECLARE @@tablename as varchar(100)
OPEN tables
        FETCH FROM tables INTO @@tablename,  @Database
WHILE @@Fetch_Status  = 0
BEGIN
        PRINT 'Namespace ' + @Database
        PRINT 'Public Class ' + @@tablename


DECLARE @@column varchar(100)
DECLARE @@coltype varchar(100)
DECLARE @@position int
DECLARE @@catalog varchar(100)
DECLARE @@table varchar(100)


PRINT ''
PRINT '#Region "Structs" '
PRINT '  Public Sub New()'
PRINT '  End Sub'
PRINT '  '
PRINT '  Public Sub New()'

DECLARE structs CURSOR LOCAL FAST_FORWARD FOR 
SELECT  
COLUMN_NAME, DATA_TYPE
FROM  
   INFORMATION_SCHEMA.COLUMNS 
WHERE  
   TABLE_NAME = @@tablename
OPEN structs
        FETCH FROM structs INTO @@column, @@coltype
WHILE @@Fetch_Status  = 0
BEGIN 
        PRINT '     Me.' + @@column + '= _' + lower(@@column)

FETCH FROM structs INTO @@column, @@coltype
END

CLOSE structs
DEALLOCATE structs
PRINT '  End Sub'
PRINT '#End Region'
PRINT ''


PRINT '#Region "Public Properties" '
DECLARE members CURSOR LOCAL FAST_FORWARD FOR 
SELECT COLUMN_NAME, DATA_TYPE
FROM  
   INFORMATION_SCHEMA.COLUMNS 
WHERE  
   TABLE_NAME = @@tablename
OPEN members
        FETCH FROM members INTO @@column, @@coltype
WHILE @@Fetch_Status  = 0
BEGIN 
        PRINT 'Private _' + lower(@@column) + ' as ' + dbo.getVBDataType(@@coltype)
        PRINT 'Public Property ' + @@column + '() as ' + dbo.getVBDataType(@@coltype)
        PRINT '   Get'
        PRINT '      ' + @@column + ' = _' + Lower(@@column)
        PRINT '   End Get'
        PRINT '   Set(ByVal value as ' + dbo.getVBDataType(@@coltype) + ')'
        PRINT '      _' + Lower(@@column) + ' = value '
        PRINT '   End Set'
        PRINT 'End Property'
        PRINT ' ' 

FETCH FROM members INTO @@column, @@coltype
END
Print '#End Region'
CLOSE members
DEALLOCATE members



        PRINT 'End Class'
        PRINT 'End Namespace'
PRINT '''-------------------------------------------------------------------------'
FETCH FROM tables INTO @@tablename,  @Database
END

CLOSE tables
DEALLOCATE tables

Obviously, more can be done with this code to make it work better, but I thought it was a fun start. I’d love to hear the feedback. Holla! :-)

**DISCLAIMER** This code is not intended to be used in a production environment and is not guaranteed in any way. Use at your own risk.