April 27, 2015 | Posted in: SQL Server

I wanted to overcome the repetitive task of creating a new class and associated properties every time a new table is introducted in to the system, so I’ve written a stored procedure to do exactly that:

create procedure [dbo].[CreateClassStructureFromSQLTable]
(
@VB bit,
@TableName varchar(255),
@PluraliseClassName bit
)
as

/*
exec [dbo].[CreateClassStructureFromSQLTable] 1, ‘tblSomeTable’, 1
*/

declare @prop varchar(max)
declare @field varchar(255)
declare @field_lower varchar(255)
declare @type varchar (255)

declare @class_prefix varchar(10)
set @class_prefix = ‘X’

if @VB = 0
begin

PRINT ‘public class ‘ + replace(@TableName,’tbl’,@class_prefix) + case when @PluraliseClassName = 1 then ‘s’ else ” end + ‘ : IDisposable’
PRINT ‘{‘

declare cprops cursor for
select distinct ‘public ‘ + ColumnType + NullableSign + ‘ ‘ + ColumnName AS prop
from
(
select
replace(col.name, ‘ ‘, ‘_’) ColumnName,
column_id ColumnId,
case typ.name
when ‘bigint’ then ‘long’
when ‘binary’ then ‘byte[]‘
when ‘bit’ then ‘bool’
when ‘char’ then ‘string’
when ‘date’ then ‘System.DateTime’
when ‘datetime’ then ‘System.DateTime’
when ‘datetime2′ then ‘System.DateTime’
when ‘datetimeoffset’ then ‘DateTimeOffset’
when ‘decimal’ then ‘decimal’
when ‘float’ then ‘float’
when ‘image’ then ‘byte[]‘
when ‘int’ then ‘int’
when ‘money’ then ‘decimal’
when ‘nchar’ then ‘char’
when ‘ntext’ then ‘string’
when ‘numeric’ then ‘decimal’
when ‘nvarchar’ then ‘string’
when ‘real’ then ‘double’
when ‘smalldatetime’ then ‘System.DateTime’
when ‘smallint’ then ‘short’
when ‘smallmoney’ then ‘decimal’
when ‘text’ then ‘string’
when ‘time’ then ‘TimeSpan’
when ‘timestamp’ then ‘System.DateTime’
when ‘tinyint’ then ‘byte’
when ‘uniqueidentifier’ then ‘System.Guid’
when ‘varbinary’ then ‘byte[]‘
when ‘varchar’ then ‘string’
else ‘UNKNOWN_’ + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in (‘bigint’, ‘bit’, ‘date’, ‘datetime’, ‘datetime2′, ‘datetimeoffset’, ‘decimal’, ‘float’, ‘int’, ‘money’, ‘numeric’, ‘real’, ‘smalldatetime’, ‘smallint’, ‘smallmoney’, ‘time’, ‘tinyint’, ‘uniqueidentifier’)
then ‘?’
else ”
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by prop

open cprops
FETCH NEXT FROM cprops INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN

set @field = replace(substring(replace(@prop, ‘public ‘, ”), charindex(‘ ‘,replace(@prop, ‘public ‘, ”)),100),’ ‘,”)
set @field_lower = lower(left(@field,1)) + right(@field, len(@field)-1)
set @type =  left(replace(@prop, ‘public ‘, ”), charindex(‘ ‘, replace(@prop, ‘public ‘, ”)))

–select @field, @field_lower, @type

print ‘    private ‘ + @type + ‘_’ + @field_lower + ‘;’
print ‘    ‘+@prop
print ‘    {‘
print ‘        get’
print ‘        {‘
print ‘            return this._’ + @field_lower + ‘;’
print ‘        }’
print ‘        set’
print ‘        {‘
print ‘            this._’ + @field_lower + ‘ = value;’
print ‘        }’
print ‘    }’
print ”

FETCH NEXT FROM cprops INTO @prop
END
close cprops
DEALLOCATE cprops
PRINT ‘}’

end
else
begin

PRINT ‘Public Class ‘ + replace(@TableName,’tbl’,@class_prefix) + case when @PluraliseClassName = 1 then ‘s’ else ” end
PRINT ‘    Implements IDisposable’
PRINT ”
declare props cursor for
select distinct ‘Public Property ‘ + ColumnName + ‘ as ‘ + ColumnType AS prop
from (
select
replace(col.name, ‘ ‘, ‘_’) ColumnName,  column_id,
case typ.name
when ‘bigint’ then ‘Long’
when ‘binary’ then ‘Byte[]‘
when ‘bit’ then ‘Boolean’
when ‘char’ then ‘String’
when ‘date’ then ‘DateTime’
when ‘datetime’ then ‘DateTime’
when ‘datetime2′ then ‘DateTime’
when ‘datetimeoffset’ then ‘DateTimeOffset’
when ‘decimal’ then ‘Decimal’
when ‘float’ then ‘Float’
when ‘image’ then ‘Byte[]‘
when ‘int’ then ‘Integer’
when ‘money’ then ‘Decimal’
when ‘nchar’ then ‘Char’
when ‘ntext’ then ‘String’
when ‘numeric’ then ‘Decimal’
when ‘nvarchar’ then ‘String’
when ‘real’ then ‘Double’
when ‘smalldatetime’ then ‘DateTime’
when ‘smallint’ then ‘Short’
when ‘smallmoney’ then ‘Decimal’
when ‘text’ then ‘String’
when ‘time’ then ‘TimeSpan’
when ‘timestamp’ then ‘DateTime’
when ‘tinyint’ then ‘Byte’
when ‘uniqueidentifier’ then ‘Guid’
when ‘varbinary’ then ‘Byte[]‘
when ‘varchar’ then ‘String’
end ColumnType
from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id
where object_id = object_id(@TableName)
) t
order by prop

open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN

set @field = replace(left(replace(@prop, ‘Public Property ‘, ”), charindex(‘ ‘, replace(@prop, ‘Public Property ‘, ”))),’ ‘,”)
set @field_lower = lower(left(@field,1)) + right(@field, len(@field)-1)
set @type =  replace(replace(@prop, ‘Public Property ‘, ”), @field + ‘ as ‘,”)

print ‘    Private _’ + lower(left(replace(@prop, ‘Public Property ‘, ”),1)) + substring(replace(@prop, ‘Public Property ‘, ”), 2,100)
print ‘    ‘ +@prop
print ‘    Get’
print ‘        Return Me._’ + @field_lower
print ‘    End Get’
print ‘    Set(value As ‘ + @type + ‘)’
print ‘        Me._’ + @field_lower + ‘ = value’
print ‘    End Set’
print ‘    End Property’
print ”
FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT ‘End Class’

end