Skip to content

Instantly share code, notes, and snippets.

@osmyn
Forked from joey-qc/TSQL-to-POCO
Last active August 14, 2018 20:31
Show Gist options
  • Save osmyn/82fc34e483f4ff4c38711020fdb6cbdd to your computer and use it in GitHub Desktop.
Save osmyn/82fc34e483f4ff4c38711020fdb6cbdd to your computer and use it in GitHub Desktop.
A simple TSQL script to quickly generate c# POCO classes from SQL Server tables and views. You may tweak the output as needed. Not all datatypes are represented but this should save a bunch of boilerplate coding. USAGE: Run this query against the database of your choice. The script will loop through tables, views and their respective columns. Re…
declare @tableName varchar(200)
declare @columnName varchar(200)
declare @nullable varchar(50)
declare @datatype varchar(50)
declare @maxlen int
declare @sType varchar(50)
declare @sProperty varchar(200)
DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES]
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'public class ' + @tableName + ' {'
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, isnull(CHARACTER_MAXIMUM_LENGTH,'-1')
from [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
order by [ORDINAL_POSITION]
OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @columnName, @nullable, @datatype, @maxlen
WHILE @@FETCH_STATUS = 0
BEGIN
-- datatype
select @sType = case @datatype
when 'int' then 'int'
when 'decimal' then 'decimal'
when 'money' then 'decimal'
when 'char' then 'string'
when 'nchar' then 'string'
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'uniqueidentifier' then 'guid'
when 'datetime' then 'DateTime'
when 'bit' then 'bool'
when 'smallint' then 'short'
when 'date' then 'DateTime'
else 'NOTFOUND'
END
If (@nullable = 'NO')
begin
PRINT '[Required]'
end
else
begin
if(@sType <> 'string')
set @sType = @sType + '?'
end
if (@sType = 'string' and @maxLen <> '-1')
Print '[MaxLength(' + convert(varchar(4),@maxLen) + ')]'
SELECT @sProperty = 'public ' + @sType + ' ' + @columnName + ' { get; set;}'
PRINT @sProperty
print ''
FETCH NEXT FROM column_cursor INTO @columnName, @nullable, @datatype, @maxlen
END
CLOSE column_cursor
DEALLOCATE column_cursor
print '}'
print ''
FETCH NEXT FROM table_cursor
INTO @tableName
END
CLOSE table_cursor
DEALLOCATE table_cursor
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment