Generate C# Class from SQL

It’s something I find myself doing constantly, especially when using Dapper. Instead of writing these models by hand, I decided to find a way to automate the process.

After some research, I stumbled upon a useful SQL query that, when executed in SQL Server Management Studio, will generate a C# class from any given table. I combined the best parts of several SQL scripts to create this handy snippet, which outputs a table as a C# model class:

Generate C# Model Classes from SQL Tables

-- Declare schema and table name
DECLARE @Namespace VARCHAR(MAX) = 'YourNameSpace';
DECLARE @Schema VARCHAR(MAX) = 'dbo';
DECLARE @Table VARCHAR(MAX) = 'YourTableName';

DECLARE @result VARCHAR(MAX) = '';

-- Build the beginning of the class definition
SET @result = 'namespace ' + @Namespace + CHAR(13) + '{' + CHAR(13) + 
              '    public class ' + @Table + CHAR(13) + '    {' + CHAR(13);

-- Generate the property definitions based on the columns in the table
SELECT @result = @result + '        public ' 
                         + 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 
                         + ' ' 
                         + c.COLUMN_NAME
                         + ' { get; set; }' + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @Table
AND ISNULL(@Schema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA
ORDER BY c.ORDINAL_POSITION;

-- Add the closing braces for the class
SET @result = @result + '    }' + CHAR(13) + '}';

-- Output the result
PRINT @result;

How to Use the Script

1. Set the Schema, NameSpace and Table: Replace the @Schema variable with your schema name (usually dbo) and the @Table variable with the name of the table you're working with, @NameSpace with your namespace.
2. Run the Query: After executing the query, you’ll get a C# class in the output window. This class will have properties that match the columns of your SQL table, with the correct data types and nullable annotations.

For example, Consider this SQL table definition for an Orders table:

CREATE TABLE [dbo].[Orders](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [ShippedDate] [datetime] NULL,
    mount] [decimal](18, 2) NOT NULL,
    [IsPaid] [bit] NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
        [OrderId] ASC
    )
) ON [PRIMARY]

Now, in the first SQL script, set the schema and table name as follows:

DECLARE @Namespace VARCHAR(MAX) = 'FoxLearn';
DECLARE @Schema VARCHAR(MAX) = 'dbo';
DECLARE @Table VARCHAR(MAX) = 'Orders';

After running the query, the output will be a C# class that mirrors the structure of the Orders table:

namespace FoxLearn
{
    public class Orders
    {
        public int OrderId { get; set; }
        public int CustomerId { get; set; }
        public DateTime OrderDate { get; set; }
        public DateTime? ShippedDate { get; set; }
        public string Status { get; set; }
        public decimal TotalAmount { get; set; }
        public bool IsPaid { get; set; }
    }
}

Using the SQL script, we generate the corresponding C# class for the Orders table. The column names are converted into C# properties (with capitalized first letters), and appropriate C# data types are assigned based on the SQL data types (e.g., nvarchar becomes string, bit becomes bool, decimal becomes decimal, and datetime becomes DateTime? for nullable types).

The resulting C# class represents the Orders table structure and can be used in your application to interact with the database easily.