Skip to content

Instantly share code, notes, and snippets.

@ajai8085
Forked from joey-qc/TSQL-to-POCO
Created August 21, 2017 01:16
Show Gist options
  • Save ajai8085/d91f3c17e677bbe6ac43b3ee277a97bf to your computer and use it in GitHub Desktop.
Save ajai8085/d91f3c17e677bbe6ac43b3ee277a97bf 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