Create C# Class from SQL

I hate doing plumbing code. Especially entity model classes that are exact replicas of SQL tables. I do this all the time when using Dapper. So I did some googling and found some SQL statements that you can run in your SQL Server Management Studio and have a table as a C# class in the output. I combined the best ones into this chunk of SQL:

-- This is the schema name
DECLARE @Schema VARCHAR(MAX) = 'Dbo' 
-- This is the table name. Write your own table name here
DECLARE @Table VARCHAR(MAX) = 'TableName' 

DECLARE @result varchar(max) = ''
SET    @result = @result + 'namespace ' + @Schema  + CHAR(13) + '{' + CHAR(13) 
SET    @result = @result + '    public class ' + @Table + CHAR(13) + '    {' + CHAR(13) 
SELECT @result = @result + '        public ' + DataType + ' ' + PropertyName + ' { get; set; } ' + CHAR(13)
FROM (SELECT
    UPPER(left(c.COLUMN_NAME,1))+SUBSTRING(c.COLUMN_NAME,2,LEN(c.COLUMN_NAME)) AS PropertyName,
    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 'double?' ELSE 'double' 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 'Byte[]'
        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 DataType, c.ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = @Table
    AND ISNULL(@Schema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result  + '    }' + CHAR(13)
SET @result = @result + '}' 

PRINT @result

HOW TO USE IT:

Replace the @Schema with your schema value (usually Dbo) and the @Table value with the name of the table and run the statement. It will then output the table as a C# model class.

EXAMPLE:

This is my table:

CREATE TABLE [dbo].[Favorites](
	[rowId] [int] IDENTITY(1,1) NOT NULL,
	[userKey] [int] NOT NULL,
	[favoriteId] [nvarchar](255) NULL,
	[lastModified] [datetime] NULL,
	[isDeleted] [bit] NULL,
	[created] [datetime] NULL,
 CONSTRAINT [PK_Favorites] PRIMARY KEY CLUSTERED 
(
	[rowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I set the 2 variables in the first SQL statement:

-- This is the schema name
DECLARE @Schema VARCHAR(MAX) = 'Dbo' 
-- This is the table name. Write your own table name here
DECLARE @Table VARCHAR(MAX) = 'Favorites' 

And run it, and voila! The table is now a C# Dto class. It will even uppercase the first letter in the property name for you:

namespace Dbo
{
    public class Favorites
    {
        public int RowId { get; set; } 
        public int UserKey { get; set; } 
        public string FavoriteId { get; set; } 
        public DateTime? LastModified { get; set; } 
        public bool? IsDeleted { get; set; } 
        public DateTime? Created { get; set; } 
    }
}

That’s it. You are now not only a C# expert, but also a SQL shark. Happy plumbing. And coding.

MORE TO READ:

• Dapper
• CodVerter, Create Entity Class from SQL Database Table
• Generate class from database table from Stackoverflow
• SQL to c# class | table to class c# from DecatechLabs
• C# Using Dapper as your SQL framework in .NET Core by briancaos