We can create a c# entity or dto class from a database table. Here is the script to generate an entity class in the SQL server. we will learn to Create a Script Generate C# Entity Class from Database Table. There is video at the end of page that walk you through on how to do it.
I wrote this article as answer to this msdn post
Let’s generate entity class from database table c#
I used the Sql Server , open and select the database
- create a new query and paste the script content below in it
- change the value of the @TableName variable with the Table name you want to generate the class for
- when running query make sure you select the correct database and click on execute to get c# class representation.
sql to c# class
--Updated by @Sean on 23th,December 2022
declare @TableName sysname = 'MyTableName' // Replace this with your database table name.
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 'string'
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(@TableName)
) t
order by ColumnId
set @Result = @Result + '
}'
print @Result
in the output window you will see the c# class representation .
It takes lot of effort to write things please do provide feedback by commenting or sharing.
Below is the Video Version if you prefer to watch.
REMEMBER SOMEONE spent time to write it . It takes lot of effort to create a quality write up please do provide feedback. As a action of courtesy you can subscribe the youtube channel in the video above.
Azure is trending topic and if you need to pass az-900 please refer to this Cheatsheet.
wow excellent work solved my query in 2 sec. I appreciate the work. Thanks a lot
It is not working for the table having more than 165 columns
Smashing wee bit of software. Thank you
Many Thanks. It helps a lot!
[…] SQL to c# class | table to class c# from DecatechLabs […]