Last active
January 9, 2021 20:02
-
-
Save jdmallen/2e6a54030d4ddf76659c07598c0b8153 to your computer and use it in GitHub Desktop.
Script to generate C# entities (POCOs, optionally) from a SQL Server table -- best run from SSMS
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @TableName varchar(50) | |
| DECLARE @SchemaName varchar(50) | |
| DECLARE @ClassNamespace varchar(512) | |
| DECLARE @ClassPrefix varchar(50) | |
| DECLARE @ClassExtends varchar(512) | |
| DECLARE @EnableAnnotations bit | |
| DECLARE @AddOnModelCreating bit | |
| DECLARE @NL varchar(2) -- newline character(s) | |
| SET @TableName = 'User' | |
| SET @SchemaName = 'dbo' | |
| SET @ClassNamespace = 'Root' | |
| SET @ClassPrefix = null -- optional, set null if omitted | |
| SET @ClassExtends = null -- optional, set null if omitted | |
| SET @EnableAnnotations = 0 -- add Table and Key annotations | |
| SET @AddOnModelCreating = 0 -- add OnModelCreating method to each entity model to be read via reflection | |
| SET @NL = CHAR(13) -- @NL == 'CR'. SSMS will complete with CRLF in output. | |
| DECLARE @result varchar(max) = '' | |
| SET @result = @result + 'using System;' + @NL | |
| IF @EnableAnnotations = 1 | |
| BEGIN | |
| SET @result = @result + 'using System.ComponentModel.DataAnnotations;' + @NL | |
| SET @result = @result + 'using System.ComponentModel.DataAnnotations.Schema;' + @NL | |
| END | |
| IF @AddOnModelCreating = 1 | |
| SET @result = @result + 'using Microsoft.EntityFrameworkCore;' + @NL | |
| SET @result = @result + 'namespace ' + @ClassNamespace + @NL + '{' + @NL | |
| IF @EnableAnnotations = 1 | |
| SET @result = @result + ' [Table("' + @TableName + '")]' + @NL | |
| SET @result = @result + ' public class ' + (CASE WHEN @ClassPrefix IS NOT NULL | |
| THEN @ClassPrefix | |
| ELSE '' END) + @TableName | |
| SET @result = @result + CASE WHEN @ClassExtends IS NOT NULL | |
| THEN ' : ' + @ClassExtends | |
| ELSE '' END | |
| + @NL + ' {' + @NL | |
| SELECT @result = @result + | |
| ( | |
| CASE WHEN KeyType = 'PRIMARY KEY' AND @EnableAnnotations = 1 | |
| THEN ' [Key]' + @NL + ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; }' | |
| ELSE ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; }' END | |
| ) + @NL + @NL | |
| FROM | |
| ( | |
| SELECT c.COLUMN_NAME AS ColumnName | |
| ,CASE c.DATA_TYPE | |
| WHEN 'bigint' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'long?' ELSE 'long' END | |
| WHEN 'binary' THEN 'byte[]' | |
| WHEN 'bit' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'bool?' ELSE 'bool' END | |
| WHEN 'char' THEN 'String' | |
| WHEN 'date' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END | |
| WHEN 'datetime' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END | |
| WHEN 'datetime2' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END | |
| WHEN 'datetimeoffset' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END | |
| WHEN 'decimal' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END | |
| WHEN 'float' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'float?' ELSE 'float' END | |
| WHEN 'image' THEN 'byte[]' | |
| WHEN 'int' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'int?' ELSE 'int' END | |
| WHEN 'money' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END | |
| WHEN 'nchar' THEN 'string' | |
| WHEN 'ntext' THEN 'string' | |
| WHEN 'numeric' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END | |
| WHEN 'nvarchar' THEN 'string' | |
| WHEN 'real' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'double?' ELSE 'double' END | |
| WHEN 'smalldatetime' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END | |
| WHEN 'smallint' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'short?' ELSE 'short' END | |
| WHEN 'smallmoney' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END | |
| WHEN 'text' THEN 'string' | |
| WHEN 'time' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END | |
| WHEN 'timestamp' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END | |
| WHEN 'tinyint' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'byte?' ELSE 'byte' END | |
| WHEN 'uniqueidentifier' THEN | |
| CASE C.IS_NULLABLE | |
| WHEN 'YES' THEN 'Guid?' ELSE 'Guid' END | |
| WHEN 'varbinary' THEN 'byte[]' | |
| WHEN 'varchar' THEN 'string' | |
| ELSE 'object' | |
| END AS ColumnType | |
| , c.ORDINAL_POSITION | |
| , CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType | |
| FROM INFORMATION_SCHEMA.COLUMNS c | |
| LEFT JOIN ( | |
| SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME | |
| FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc | |
| INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku | |
| ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' | |
| AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME | |
| ) pk ON c.TABLE_CATALOG = pk.TABLE_CATALOG | |
| AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA | |
| AND c.TABLE_NAME = pk.TABLE_NAME | |
| AND c.COLUMN_NAME = pk.COLUMN_NAME | |
| WHERE c.TABLE_NAME = @TableName AND c.TABLE_SCHEMA = @SchemaName | |
| ) t | |
| ORDER BY t.ORDINAL_POSITION | |
| IF @AddOnModelCreating = 1 | |
| SET @result = @result + | |
| ' public void OnModelCreating(ModelBuilder modelBuilder)' + @NL | |
| + ' {' + @NL | |
| + ' throw new NotImplementedException();' + @NL | |
| + ' }' + @NL | |
| SET @result = @result + ' }' + @NL | |
| SET @result = @result + '}' + @NL | |
| PRINT @result |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment