Skip to content

Instantly share code, notes, and snippets.

@WangJi
Forked from joey-qc/TSQL-to-POCO
Created October 13, 2020 11:23
Show Gist options
  • Save WangJi/7810710a255685e867e82c213574f877 to your computer and use it in GitHub Desktop.
Save WangJi/7810710a255685e867e82c213574f877 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 'Int32'
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 'Boolean'
else 'String'
END
If (@nullable = 'NO')
PRINT '[Required]'
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