SQL语句生成实体类

--DictParam，表名称
declare @TableName sysname = 'DictParam'
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 = 'DictPublic'
declare @Result varchar(max) = '
/// <summary>
/// ' + @TableName +
 
'
/// </summary>
public class ' + @TableName + '
{'
    select @Result = @Result + '
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

根据实际工作更改如下(生成部分代码)：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

-------------------------------生成实体类-------------------------------
declare @TableName sysname = 'DictPublic'
--declare @TableNameL sysname = 'dictPublic'
declare @TableNameL varchar(200)
set @TableNameL = (select (lower(left(@TableName,1))+SUBSTRING(@TableName,2,len(@TableName))))
--print @TableNameL
declare @Result varchar(max) = '
/// <summary>
/// ' + @TableName +
 
'
/// </summary>
public class ' + @TableName + '
{'
    select @Result = @Result + '
    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
-------------------------------生成实体类-------------------------------
print ''
--IDAL
--public partial interface IDictQualityItemsDal : IBaseDal<DictQualityItems>
print 'public partial interface I'+@TableName +'Dal : IBaseDal<'+ @TableName +'>{}'
print ''
    --bool Inserts(List<DictQualityItems> objs);
    --bool DeleteByIds(List<string> ids);
    --bool UpdateIsEnable(List<string> ids, string isEnable);
--DAL
--public partial class DictQualityItemsDal : BaseDal<DictQualityItems>, IDictQualityItemsDal
print 'public partial class '+@TableName +'Dal : BaseDal<'+ @TableName +'>,I'+@TableName+'Dal{}'
print ''
--IService
 --public partial interface IDictReviewItemsService : IBaseService<DictReviewItems>
print 'public partial interface I'+@TableName +'Service : IBaseService<'+ @TableName +'>{}'
print ''
--Service
--public partial class DictQualityItemsService : BaseService<DictQualityItems>, IDictQualityItemsService
--private IDictQualityItemsDal Dal = new DictQualityItemsDal();
--public override void SetCurrentDal()
--CurrentDal = new DictQualityItemsDal();
print 'public partial class '+@TableName +'Service : BaseService<'+ @TableName +'>,I'+@TableName+'Service'
print '{'
print ' private I'+ @TableName +'Dal Dal = new '+@TableName +'Dal();'
print ' public override void SetCurrentDal()'
print ' {'
print '     CurrentDal = new '+ @TableName +'Dal();'
print ' }'
print''
print '}'
 
--Controller
declare @Controller varchar(max) = 
'
/// <summary>
/// ' + @TableName + 
 
' 控制器
/// </summary>' + char(13)
+'[ApiController]' +char(13)
+'public class ' + @TableName + 'Controller : ControllerBase'+char(13)
+'{'+char(13) 
+ ' #region 定义' + char(13)
+ ' /// <summary>
    ///  I' + @TableName + 'Service ' + @TableName + 'Service 
    /// </summary>' + char(13)
+'  private I'+ @TableName + 'Service ' + @TableName + 'Service; '
+'
    /// <summary>
    ///  初始化服务信息
    /// </summary>' + char(13)
    +'  /// <param name="'+@TableNameL+'Service">I' + @TableName + 'Service ' + @TableNameL + 'Service</param>' + char(13)
    +'  public '+ @TableName + 'Controller(I' + @TableName+ 'Service ' + @TableNameL + 'Service)'+ char(13)
    +'  {'+char(13) 
    + '     '+@TableName+ 'Service = ' + @TableNameL+ 'Service;'+char(13)
    +'  }'+char(13)
+ char(13)+ '   #endregion' + char(13) + char(13)
+'}'+char(13) 
 
print @Controller

生成代码如下所示：

/// <summary>
/// DictPublic
/// </summary>
public class DictPublic
{
    public string PublicNo { get; set; }
    public string DictLevel { get; set; }
    public string DictName { get; set; }
    public string DictValue { get; set; }
    public string OperateType { get; set; }
    public string DictParentId { get; set; }
    public decimal OrderNo { get; set; }
    public string UseDept { get; set; }
    public string Remark { get; set; }
    public decimal IsEnable { get; set; }
    public decimal IsDelete { get; set; }
    public string DeleteUser { get; set; }
    public string DeleteTime { get; set; }
}
  
public partial interface IDictPublicDal : IBaseDal<DictPublic>{}
  
public partial class DictPublicDal : BaseDal<DictPublic>,IDictPublicDal{}
  
public partial interface IDictPublicService : IBaseService<DictPublic>{}
  
public partial class DictPublicService : BaseService<DictPublic>,IDictPublicService
{
    private IDictPublicDal Dal = new DictPublicDal();
    public override void SetCurrentDal()
    {
        CurrentDal = new DictPublicDal();
    }
  
}
 
/// <summary>
/// DictPublic 控制器
/// </summary>
[ApiController]
public class DictPublicController : ControllerBase
{
    #region 定义
    /// <summary>
    ///  IDictPublicService DictPublicService 
    /// </summary>
    private IDictPublicService DictPublicService; 
    /// <summary>
    ///  初始化服务信息
    /// </summary>
    /// <param name="dictPublicService">IDictPublicService dictPublicService</param>
    public DictPublicController(IDictPublicService dictPublicservice)
    {
        DictPublicService = dictPublicService;
    }
 
    #endregion
 
}