c# poco generator from database table using sql script

This is a simple SQL  script that will generate a c# poco class that you can use as a model in your Visual Studio projects.

I like to generate my tables manually when creating a new project. Having the tables setup with foreign keys along with a database diagram in SQL Server gives me the visual that I need when determining my database storage options.

If you’re wondering if I’m using “Migrations”, the answer is NO. I  find that it can get in my way but it’s very cool. I  haven’t figured out a way to incorporate “Migrations”  into my process yet but then again, I have a process that works for me to be productive and that’s the key, to be productive.

The only editing you need to do to this sql script is to change the table name you want to use at the top.

I did find this sql script somewhere on the interwebs and if anyone knows where, please share it with me so I can give credit to the person who wrote this. I couldn’t find it myself so at least I know it’s here for safe keeping.

Copy the code below into a new query window in your Microsoft SQL  Server Management Studio.

DECLARE @TableName sysname = 'YOUR_DATABASE_TABLE_NAME_GOES_HERE';
DECLARE @Result VARCHAR(MAX) = 'public class ' + @TableName + '
{';

SELECT @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
FROM ( SELECT REPLACE(col.name, ' ', '_') ColumnName ,
column_id ColumnId ,
CASE typ.name
WHEN 'bigint' THEN 'long'
WHEN 'binary' THEN 'byte[]'
WHEN 'bit' THEN 'bool'
WHEN 'char' THEN 'string'
WHEN 'date' THEN 'DateTime'
WHEN 'datetime' THEN 'DateTime'
WHEN 'datetime2' THEN 'DateTime'
WHEN 'datetimeoffset' THEN 'DateTimeOffset'
WHEN 'decimal' THEN 'decimal'
WHEN 'float' THEN 'float'
WHEN 'image' THEN 'byte[]'
WHEN 'int' THEN 'int'
WHEN 'money' THEN 'decimal'
WHEN 'nchar' THEN 'string'
WHEN 'ntext' THEN 'string'
WHEN 'numeric' THEN 'decimal'
WHEN 'nvarchar' THEN 'string'
WHEN 'real' THEN 'double'
WHEN 'smalldatetime' THEN 'DateTime'
WHEN 'smallint' THEN 'short'
WHEN 'smallmoney' THEN 'decimal'
WHEN 'text' THEN 'string'
WHEN 'time' THEN 'TimeSpan'
WHEN 'timestamp' THEN 'DateTime'
WHEN 'tinyint' THEN 'byte'
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'varbinary' THEN 'byte[]'
WHEN 'varchar' THEN 'string'
ELSE 'UNKNOWN_' + typ.name
END ColumnType ,
CASE WHEN col.is_nullable = 1
AND typ.name IN ( 'bigint', 'bit', 'date',
'datetime', 'datetime2',
'datetimeoffset', 'decimal',
'float', 'int', 'money',
'numeric', 'real',
'smalldatetime', 'smallint',
'smallmoney', 'time',
'tinyint', 'uniqueidentifier' )
THEN '?'
ELSE ''
END NullableSign
FROM sys.columns col
JOIN sys.types typ ON col.system_type_id = typ.system_type_id
AND col.user_type_id = typ.user_type_id
WHERE object_id = OBJECT_ID(@TableName)
) t
ORDER BY ColumnId;

SET @Result = @Result + '
}';

PRINT @Result;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.