Click here to Skip to main content
15,664,435 members
Articles / Programming Languages / C#
Tip/Trick
Posted 11 Jun 2021

Tagged as

Stats

19.5K views
17 bookmarked

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
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
hkswan14-Jun-21 8:19
hkswan14-Jun-21 8:19 
QuestionA good start Pin
Middle Manager14-Jun-21 7:11
Middle Manager14-Jun-21 7:11 
This is of course a very basic example of class generator and its usefulness will be limited for the average C# developer. Others have mentioned things like schema and annotation, which is part of the PIA aspect of doing this by hand. Navigation properties would be super handy. Marking column as keys and allowing for name transformations (underscores for spaces, PascalCase, etc.) would be helpful time savers. And of course this solution would be best served by being robustly backed by a set of config tables that the SQL could reference. that makes it safely re-playable. The best parts of an ORM is having configurability as to what you want included, and being able to regenerate without having post-generation work blown out or needing to be redone.

But this is not really an "ORM" solution - there is no mapping going on as an interface between the DB model and the .NET model. Rather this is more a one-way "translator". An ORM will provide the means to perform transformations in both directions. Having the code generation in SQL is an intriguing idea and could be useful for certain kinds of projects. So overall I applaud the ingenuity. I suspect that this is not really the best hammer in the toolbox for this specific kind of nail - but hey, sometimes the nearest rock gets the job done just as well Big Grin | :-D
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 
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.