Skip to content

Instantly share code, notes, and snippets.

@rvegajr
Last active March 20, 2019 18:30
Show Gist options
  • Save rvegajr/afb0836d140bd46c6d780b10f04fd4ea to your computer and use it in GitHub Desktop.
Save rvegajr/afb0836d140bd46c6d780b10f04fd4ea to your computer and use it in GitHub Desktop.
Script to generate C# POCO Classes
declare @tableName varchar(200)
declare @schemaName 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, TABLE_SCHEMA
FROM [INFORMATION_SCHEMA].[TABLES]
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @tableName, @schemaName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'public class ' + REPLACE(@schemaName, 'dbo', '') + REPLACE(@tableName, 'tbl_', '') + ' {'
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 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'bit' then 'bool'
when 'float' then 'double'
else 'string'
END
--If (@nullable = 'NO')
-- PRINT '[Required]'
if (@sType != 'string' and @nullable = 'YES') SET @sType = @sType + '?'
--Print '[MaxLength(' + convert(varchar(4),@maxLen) + ')]'
SELECT @sProperty = ' public ' + @sType + ' ' + @columnName + ' { get; set;}'
PRINT @sProperty
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, @schemaName
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