Creating Entity Class from Database Table in SQL Server

Hello to everyone,

In this article, I will try to give information about creating Entity Class from database table in SQL Server.

In SQL Server, in some cases you may want to create Entity Class from database table.

You can easily do this using the code below.

DECLARE @TableName sysname = 'Categories';

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 ( '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;

When you run the above code, you will see a result similar to the one below.

As you can see, Entity Class has been created from the database table.

Good luck to everyone in business and life.

77 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!