Generate C# Properties from SQL Table

Josh,

So, you like to design your models in the database but need them in C# as well. To make the recreation in C# quicker, you created a SQL script to convert the table columns into C# Properties.

Here's the main script:

DECLARE @SchemaName SYSNAME = 'dbo',
       @TableName SYSNAME = 'Contacts'
SELECT
   ss.name AS SchemaName,
   st.name AS TableName,
   sc.name AS ColumnName,
   sty.name AS ColumnType,
   sc.max_length AS ColumnMaxLength,
   sc.is_nullable AS ColumnIsNullable,
   CASE WHEN sty.name IN ('varchar','nvarchar') THEN '[MaxLength(' + CONVERT(VARCHAR,sc.max_length / (CASE WHEN sty.name = 'nvarchar' THEN 2 ELSE 1 END)) + ')]' + CHAR(10)ELSE '' END +
   CASE WHEN CHARINDEX('-',sc.name,0) + CHARINDEX('''',sc.name,0) > 0 THEN '[Column("' + sc.name + '")]' + CHAR(10) ELSE '' END +
   'public ' + toolbox.ConvertSqlTypeToCSharp(sty.name, sc.is_nullable) + ' ' + REPLACE(REPLACE(sc.name,'-','_'),'''','') + ' { get; set; }' AS CSharpProperty,
   '// ' + sc.name + ' ' + toolbox.ConvertSqlTypeToCSharp(sty.name, sc.is_nullable) AS CSharpPropertyComment
FROM
   sys.schemas ss
   INNER JOIN
   sys.tables st ON ss.schema_id = st.schema_id
   INNER JOIN
   sys.columns sc ON st.OBJECT_ID = sc.OBJECT_ID
   INNER JOIN
   sys.types sty ON sc.system_type_id = sty.system_type_id
WHERE
   ss.name = @SchemaName AND
   st.name = @TableName AND
   sty.name <> 'sysname'ORDER BY
   sc.column_id  


And Here's the script for the "toolbox.ConvertSqlTypeToCSharp":

CREATE FUNCTION [toolbox].[ConvertSqlTypeToCSharp](
   @ColumnType SYSNAME,
   @IsNullable bit)RETURNS NVARCHAR(500)AS
BEGIN
   DECLARE @TheResult NVARCHAR(500) = 'unknown';

   SET @TheResult = CASE
                       @ColumnType
                       WHEN 'bit' THEN 'bool'
                       WHEN 'tinyint' THEN 'Byte'
                       WHEN 'binary' THEN 'Byte[]'
                       WHEN 'rowversion' THEN 'Byte[]'
                       WHEN 'varbinary' THEN 'Byte[]'
                       WHEN 'date' THEN 'DateTime'
                       WHEN 'datetime' THEN 'DateTime'
                       WHEN 'datetime2' THEN 'DateTime'
                       WHEN 'DATETIMEOFFSET' THEN 'DateTimeOffset'
                       WHEN 'decimal' THEN 'decimal'
                       WHEN 'money' THEN 'decimal'
                       WHEN 'numeric' THEN 'decimal'
                       WHEN 'smallmoney' THEN 'decimal'
                       WHEN 'float' THEN 'double'
                       WHEN 'uniqueidentifier' THEN 'Guid'
                       WHEN 'int' THEN 'int'
                       WHEN 'smallint' THEN 'Int16'
                       WHEN 'bigint' THEN 'Int64'
                       WHEN 'real' THEN 'single'
                       WHEN 'char' THEN 'string'
                       WHEN 'nchar' THEN 'string'
                       WHEN 'ntext' THEN 'string'
                       WHEN 'nvarchar' THEN 'string'
                       WHEN 'sql_variant' THEN 'string'
                       WHEN 'text' THEN 'string'
                       WHEN 'timestamp' THEN 'string'
                       WHEN 'varchar' THEN 'string'
                       WHEN 'xml' THEN 'string'
                       WHEN 'time' THEN 'TimeSpan'
                       ELSE 'unknown'
                   END;

   IF (@IsNullable = 1 AND @TheResult NOT IN ('string', 'Byte[]', 'Object') )
       SET @TheResult = 'Nullable<' + @TheResult + '>'

   RETURN @TheResult;END