SQL Server數據庫表生成C# Model實體類SQL語句
C# 實例格式
/// <summary>
/// T1
/// </summary>
public class T1
{
/// <summary>
/// 主鍵
/// </summary>
public int ID { get; set; }
/// <summary>
/// 姓名
/// </summary>
public string NameLijhs { get; set; }
}
SQL Server代碼
declare @TableName sysname = 'T1'
declare @Result varchar(max) = '
/// <summary>
/// ' + @TableName +
'
/// </summary>
public class ' + @TableName + '
{'
select @Result = @Result + '
/// <summary>
/// ' + CONVERT(NVARCHAR(500), ISNULL(ColName, '無')) +
'
/// </summary>
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
SELECT
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
prop.value ColName,
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
LEFT JOIN sys.extended_properties prop ON col.object_id = prop.major_id AND col.column_id = prop.minor_id
where object_id = object_id(@TableName)
) t
--order by ColumnId
set @Result = @Result + '
}'
print @Result
實例二：
declare @TableName sysname = 'Business_goal' --表名
declare @Result varchar(max) = '
/// <summary>
/// ' + @TableName +
'
/// </summary>
public class ' + @TableName + '
{'
select @Result = @Result + '
[Description("' + CONVERT(NVARCHAR(500), ISNULL(ColName, '無')) + '")]'+
'
[Column("' + ColumnName + '")]'+
'
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
SELECT
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
prop.value ColName,
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
LEFT JOIN sys.extended_properties prop ON col.object_id = prop.major_id AND col.column_id = prop.minor_id
where object_id = object_id(@TableName)
) t
--order by ColumnId
set @Result = @Result + '
}'
print @Result
實例三：
public int ID { get; set; }
public int AID { get; set; }
public string ProjectNM { get; set; }
SQL Server代碼如下：
declare @TableName sysname = 'AUTHSYSTEMCHANGE' --表名
select *, 'public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }' as AutoCode
from
(
SELECT
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
prop.value ColName,
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
LEFT JOIN sys.extended_properties prop ON col.object_id = prop.major_id AND col.column_id = prop.minor_id
where object_id = object_id(@TableName)
) t
浙公網安備 33010602011771號