直接使用sql生成
declare @TableName varchar(500)
set @TableName='表名'
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 'double'
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
创建存储过程,每次调用存储过程生成
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE Table_Model @TableName NVARCHAR(500) --表名
AS
BEGIN
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
'double'
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;
END;
GO
在生成实体时,将数据表里面的float类型转为double类型,
避免在代码里面将Datatable时,如果将tabletable里面的float类型转为实体的float类型会出现
“类型System.Double的对象无法转换为类型System.Single”错误
float -> System.Single (单精度浮点型,占 4 个字节)
double -> System.Double (双精度浮点型,占 8 个字节)
改为double就不会出现