How to Generate C# Classes from Microsoft SQL Server

Background

If you are not using any ORM tool to generate classes from the database, this process can consume a lot of time and resources.

This SQL code frees you from wasting time on such data manipulation, and lets you focus on your code development.

Now you can easily generate a C# object class from common SQL databases types.

Using the Code

To use this code, simply highlight the database you would like to work with and open a New Query. Paste the snippet, execute and copy the generated classes into Visual Studio or VS Code.

// SET NOCOUNT ON;  
  
DECLARE @table_name NVARCHAR(250), @message VARCHAR(80);  
  
DECLARE table_cursor CURSOR FOR   
select distinct tab.name as table_name
from sys.tables as tab
    inner join sys.columns as col on tab.object_id = col.object_id
    left join sys.types as t on col.user_type_id = t.user_type_id
order by table_name;  
  
OPEN table_cursor  
  
FETCH NEXT FROM table_cursor   
INTO @table_name  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ' '  
    --SELECT @message = 'Table Name: ' +   
    --    @table_name  
  
    --PRINT @message  
  
    --declare @TableName sysname = 'TableName'
    declare @Result varchar(max) = 'public class ' + @table_name + '
    {'

    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 ('bigint', 'bit', 'date', _
    'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', _
    '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(@table_name)
    ) t
    order by ColumnId

    set @Result = @Result  + '
    }'

    print @Result
  
    FETCH NEXT FROM table_cursor   
    INTO @table_name  
END   
CLOSE table_cursor;  
DEALLOCATE table_cursor;  
//

History

• 11^th June, 2021: Initial version