The Quick and Dirty EF 4.1 Code Generator

An article by ColinBlair No Comments

Have you ever wanted a fast, easy way to generate EF 4.1 Code First POCO objects from a database without using a lot of resources? Here is my attempt at solving that problem. You simply run this query in SQL Server (with a table name set) and SQL Server will generate code to populate your POCO object as fast as you can push the execute button.

declare  @tableName varchar(50)
set @tableName = 'TABLE NAME HERE'
declare @ConvertTable table
(
        SqlType varchar(50),
        ClrType varchar(50),
        IsNullable char(3)
)
INSERT INTO @ConvertTable
VALUES ('bigint', 'Int64', 'NO')
, ('binary', 'Byte[]', 'NO')
, ('bit', 'Boolean', 'NO')
, ('char', 'char', 'NO')
, ('date', 'DateTime', 'NO')
, ('datetime', 'DateTime', 'NO')
, ('datetime2', 'DateTime', 'NO')
, ('DATETIMEOFFSET', 'DateTimeOffset', 'NO')
, ('decimal', 'Decimal', 'NO')
, ('float', 'Double', 'NO')
, ('int', 'Int32', 'NO')
, ('money', 'Decimal', 'NO')
, ('nchar', 'String', 'NO')
, ('numeric', 'Decimal', 'NO')
, ('nvarchar', 'string', 'NO')
, ('real', 'Single', 'NO')
, ('rowversion', 'Byte[]', 'NO')
, ('smallint', 'Int16', 'NO')
, ('smallmoney', 'Decimal', 'NO')
, ('sql_variant', 'Object', 'NO')
, ('time', 'TimeSpan', 'NO')
, ('timestamp', 'Byte[]', 'NO')
, ('tinyint', 'Byte', 'NO')
, ('uniqueidentifier', 'Guid', 'NO')
, ('varbinary', 'Byte[]', 'NO')
, ('varchar', 'string', 'NO')
, ('xml', 'string', 'NO')
, ('bigint', 'Int64?', 'YES')
, ('binary', 'Byte[]', 'YES')
, ('bit', 'Boolean?', 'YES')
, ('char', 'char?', 'YES')
, ('date', 'DateTime?', 'YES')
, ('datetime', 'DateTime?', 'YES')
, ('datetime2', 'DateTime?', 'YES')
, ('DATETIMEOFFSET', 'DateTimeOffset?', 'YES')
, ('decimal', 'Decimal?', 'YES')
, ('float', 'Double?', 'YES')
, ('int', 'Int32?', 'YES')
, ('money', 'Decimal?', 'YES')
, ('nchar', 'String', 'YES')
, ('numeric', 'Decimal?', 'YES')
, ('nvarchar', 'string', 'YES')
, ('real', 'Single?', 'YES')
, ('rowversion', 'Byte[]', 'YES')
, ('smallint', 'Int16?', 'YES')
, ('smallmoney', 'Decimal?', 'YES')
, ('sql_variant', 'Object', 'YES')
, ('time', 'TimeSpan?', 'YES')
, ('timestamp', 'Byte[]', 'YES')
, ('tinyint', 'Byte?', 'YES')
, ('uniqueidentifier', 'Guid?', 'YES')
, ('varbinary', 'Byte[]', 'YES')
, ('varchar', 'string', 'YES')
, ('xml', 'string', 'YES')
 
SELECT
 'public '+ ConvertTable.ClrType + ' ' + column_name + ' { get; set; }'
FROM
  information_schema.columns
 INNER JOIN @ConvertTable ConvertTable
 ON information_schema.columns.DATA_TYPE = ConvertTable.SqlType
 AND information_schema.columns.IS_NULLABLE = ConvertTable.IsNullable
WHERE
 TABLE_NAME = @tableName
order by ordinal_position