Generating C# Classes from SQL Tables

I’ve recently started a new project that is very database heavy, and I couldn’t help but find myself writing what can only be described as a ridiculous amount of boilerplate code to model entities. You might say, erm, Dan? Use an ORM? Entity Framework will take care of it for you… Now before I get into the details of this post, I want to state that this is not a post comparing Entity Framework to ADO.NET. I simply prefer to use ADO.NET and keep the flexibility that comes with it.

So, using ADO.NET, how can I generate C# Classes using my preexisting SQL Tables? Well, I found this utter GEM of an answer to this very question on our beloved StackOverFlow. (Check out the question here). We can use the following Query in SQL Server to return a C# class structure for our table:


declare @TableName sysname = '<TABLENAME>'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case
            when col.is_nullable = 1 and typ.name in ('bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
            then '?'
            else ''
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result

Lets give it a try…

I’m going to test this code using a fairly basic table as an example. This table is called “OrderItems”, and is responsible for holding data about items relating to a customers order. Consider a table that has the following fields:

ID    int
OrderItemID    nvarchar(50)
SKU    nvarchar(50)
Quantity    int
ItemPrice    money
ShippingPrice    money
OrderID    int
ClientID    int

Pretty simple! So lets run the query against this table, and we get the following output:


public class OrderItems
{
    public int ID { get; set; }

    public string OrderItemID { get; set; }

    public string SKU { get; set; }

    public int Quantity { get; set; }

    public decimal ItemPrice { get; set; }

    public decimal ShippingPrice { get; set; }

    public int OrderID { get; set; }

    public int ClientID { get; set; }

}

Just what we wanted!

Don’t forgot to change the <TABLENAME> with the name of your table, eg:

declare @TableName sysname = 'OrderItems'

 

I hope that by sharing this, it will help as many of you as possible – in the same way that it has helped me.

Credit: Alex Aza on StackOverFlow.com.