Skip to content

Instantly share code, notes, and snippets.

@plaisted
Forked from joey-qc/TSQL-to-POCO
Last active April 3, 2021 20:07
Show Gist options
  • Save plaisted/a6836ba8c3391b4ee3427d56fd6d838b to your computer and use it in GitHub Desktop.
Save plaisted/a6836ba8c3391b4ee3427d56fd6d838b to your computer and use it in GitHub Desktop.
Modified original GIST to accommodate c# nullable types (?).
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] where table_name = 'TABLE NAME'
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
case when @nullable = 'YES' then 'int?'
else 'int'
END
when 'smallint' then
case when @nullable = 'YES' then 'Int16?'
else 'Int16'
END
when 'decimal' then
case when @nullable = 'YES' then 'decimal?'
else 'decimal'
END
when 'money' then
case when @nullable = 'YES' then 'decimal?'
else 'decimal'
END
when 'char' then 'string'
when 'nchar' then 'string'
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'uniqueidentifier' then
case when @nullable = 'YES' then 'Guid?'
else 'Guid'
END
when 'datetime' then
case when @nullable = 'YES' then 'DateTime?'
else 'DateTime'
END
when 'bit' then
case when @nullable = 'YES' then 'bool?'
else 'bool'
END
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