How To Create Class Structure With Properties From SQL Table In VB.Net Or C#.Net

sql_server_logo

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

Read More →

Disable Specific Triggers Based On SYS.SQL_MODULES DEFINITION

sql_server_logo

April 7, 2015 | Posted in SQL Server, Uncategorized

declare @temp table
(table_name varchar(100), trigger_name varchar(100))
insert into @temp
select    object_name(parent_id), name
from    sys.triggers
where    name in
(
select object_name(object_id) from sys.sql_modules where definition like ‘%SEARCH%STRING%’
)
and is_disabled = 0

declare @sql varchar(255)

declare db_cursor cursor for
select ‘ALTER TABLE ‘ + table_name + ‘ DISABLE TRIGGER [' + trigger_name + ']‘ from @temp x

open db_cursor
fetch next from db_cursor into @sql

while @@FETCH_STATUS = 0
begin

exec (@sql);

fetch next from db_cursor into @sql
end

close db_cursor
deallocate db_cursor

Read More →