Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C#
Tip/Trick

How to Generate C# Classes from Microsoft SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (16 votes)
11 Jun 2021CPOL 25.5K   17   12
Generate C# object class from common SQL databases types
This snippet helps any software developer working with databases who probably needed to create object classes that represent tables from databases.

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.

SQL
// 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

  • 11th June, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO Tshumisano Consulting
South Africa South Africa
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionwork Pin
zatanax16-Jun-21 6:59
zatanax16-Jun-21 6:59 
QuestionWorks like it says Pin
Kent Swan14-Jun-21 8:19
Kent Swan14-Jun-21 8:19 
QuestionA good start Pin
Middle Manager14-Jun-21 7:11
Middle Manager14-Jun-21 7:11 
QuestionExcellent Time Saver Pin
victorbos14-Jun-21 5:31
victorbos14-Jun-21 5:31 
GeneralMy vote of 5 Pin
Carl Edwards In SA14-Jun-21 4:33
professionalCarl Edwards In SA14-Jun-21 4:33 
GeneralWell, this is quite lame but it is a start... where are your annotations, keys, field lengths... go get the work done proper... Pin
tatran.eu@gmail.com14-Jun-21 3:33
tatran.eu@gmail.com14-Jun-21 3:33 
QuestionOnly dbo Pin
Member 1009213714-Jun-21 0:13
Member 1009213714-Jun-21 0:13 
While it lists all tables the code shows columns only for columns of [dbo]. What about other schemas?
AnswerRe: Only dbo Pin
Salam Y. ELIAS14-Jun-21 8:02
professionalSalam Y. ELIAS14-Jun-21 8:02 
QuestionNot quite there yet Pin
Alex (RSA)13-Jun-21 23:54
professionalAlex (RSA)13-Jun-21 23:54 
QuestionVery useful Pin
Tony Hill12-Jun-21 1:47
mveTony Hill12-Jun-21 1:47 
QuestionNice! Pin
Juba11-Jun-21 10:29
Juba11-Jun-21 10:29 
QuestionExample Pin
LightTempler11-Jun-21 9:14
LightTempler11-Jun-21 9:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.