Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

How can I generate a class from a table at a SQL Server?

I'm not talking about using some ORM. I just need to create the entities (simple class). Something like:

public class Person 

{

    public string Name { get;set; }

    public string Phone { get;set; }

}

1 Answer

0 votes
by (40.4k points)

Try setting @TableName to the name of your table as follows:

Query:

declare @TableName sysname = 'TableName'

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 'double'

            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 'float'

            when 'smalldatetime' then 'DateTime'

            when 'smallint' then 'short'

            when 'smallmoney' then 'decimal'

            when 'text' then 'string'

            when 'time' then 'TimeSpan'

            when 'timestamp' then 'long'

            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

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...