Generate class from SQL database table
declare @TableName sysname = 'BANKACCOUNT'
declare @Result varchar(max) = 'public class ' + UPPER(LEFT(@TableName,1))+LOWER(SUBSTRING(@TableName,2,LEN(@TableName))) + 'Entity
{'
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
public class BankaccountEntity
{
public int ID { get; set; }
public string BANK { get; set; }
public string ROUNTINGNUM { get; set; }
public string CURRENCY { get; set; }
public string ACCOUNTNUM { get; set; }
public int? MAINACCOUNTID { get; set; }
public string IBAN { get; set; }
public string SWIFT { get; set; }
public string BRANCH { get; set; }
public string CONTACTPERSON { get; set; }
public string CONTACTPHONE { get; set; }
public string CONTACTEMAIL { get; set; }
public bool? STATUS { get; set; }
}
