使用 SQL Server 脚本语句生成 C# Models实体类，Mapping映射类

在C# 项目开发中，本人之前一直对创建 Models，Mapping 无聊又花时间的事有点不耐烦！！！或许码友们，也有像我这样的吧！在这开发之前我也有用过一些小工具生成！直接生成.cs 文件，也还是不错！今天给大家分享下自己用脚本语句生成的方法！！！

1、生成 Models实体类脚本语句

declare @TableName sysname = 'tb_UserInfo' --表名
declare @Result varchar(max) = ''

declare @PreResult varchar(max) = ''


  SELECT @PreResult=@PreResult +'
		' + PreResult +';'
  from
(
      select  'this.'+replace(col.name, ' ', '_')+'=' +
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'false'
            when 'char' then 'string'
            when 'date' then 'DateTime.Now'
            when 'datetime' then 'DateTime.Now'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then '0'
            when 'float' then '0'
            when 'image' then 'null'
            when 'int' then '0'
            when 'money' then 'decimal'
            when 'nchar' then 'string.Empty'
            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.Empty'
        end  as PreResult
    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
    
   
set @Result='
/// <summary>
///  ' +  @TableName +
    
'    
/// </summary>
public class ' + @TableName + '
{'
+
'
        public '+@TableName+'()
        {
'
+
@PreResult
+

          
          
+' }'
       

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

2、执行后，下方会输出文本，复制粘贴到vs项目.cs 文件就OK了；
(注:1、我的数据表中没有设置字段中文名，所以显示类属性“无”；2、在生成构造函数时，设置字段默认值可以根据自己的需要更改脚本)

3、生成Mapping映射类 ，操作如上1、2步骤，语句如下：

declare @TableName sysname = 'tb_table' --表名
declare @Result varchar(max) = ''
declare @KeyName varchar(max) = ''

 SELECT  @KeyName= case when LEN(COLUMN_NAME)>0 then ' HasKey(t => t.'+COLUMN_NAME+')' else '' end
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@TableName

set @Result='
public class ' + @TableName + 'Map: System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Domain.'+@TableName+'>
{'
+
'
 public '+@TableName+'Map()
  {
	 ToTable("'+@TableName+'", "dbo");
	'+@KeyName+';
'

select  @Result = 
@Result +'	Property(t => t.'+ColumnName+').HasColumnName("'+ColumnName+'").'+ 

(case when (ColumnType='int' or ColumnType='bool' or ColumnType='float' or ColumnType='DateTime') then '' else 'HasMaxLength('+ cast(MaxLength as varchar(100)) +').' end )

+'IsRequired();
'

from
(
    SELECT
        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,
        col.max_length as MaxLength

    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

set @Result = @Result  ++'	}'
--order by ColumnId

set @Result = @Result  + '
}'

print @Result