How to Convert a SQL Table to C# Class

Run the following SQL query and it will print the SQL table in a formatted C# Class:

———————————————————–

DECLARE @tbl sysname = ‘doc360_User_Role’

DECLARE @ClassStr varchar(MAX) = ‘public class ‘ + @tbl + ‘
{‘

SELECT @ClassStr = @ClassStr + ‘
public ‘ + ColumnType + NullableSign + ‘ ‘ + ColumnName + ‘ { get; set; }

FROM
( SELECT replace(systemColumns.name, ‘ ‘, ‘_’) ColumnName,
column_id ColumnId,
CASE systemTypes.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_’ + systemTypes.name
END ColumnType,
CASE
WHEN systemColumns.is_nullable = 1
AND systemTypes.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 systemColumns
JOIN sys.types systemTypes ON systemColumns.system_type_id = systemTypes.system_type_id
AND systemColumns.user_type_id = systemTypes.user_type_id
WHERE object_id = object_id(@tbl) ) t
ORDER BY ColumnId

SET @ClassStr = @ClassStr + ‘
}’

print @ClassStr

———————————————————–