通过T4模板生成数据库实体类，妈妈再也不用担心我用CodeFirst了！！！
程序员文章站
2022-07-14 11:20:02
...
T4模板是一种代码生成器，可以通过套用设置好的模板快速生成代码，本文将介绍如何通过T4模板快速生成数据库的表映射实体类，学会这个方法，再也不用烦心于codefirst要一个一个添加字段的无聊事情当中了！
首先我们新建一个类库，右键项目-添加-新增项，在搜索栏中搜索T4，如下图：
选择文本模板，名称这里我们命名为EntitiesTemplate，点击添加。
添加完成后我们再新增一个模板帮助类TempHelper，如下：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication1
{
public class TempHelper
{
//数据库连接字符串(web.config来配置)
public const string connStr = "Server=***.***.**.***;Database=***;uid=**;pwd=******;";
public static string nspace = "MyMis";
/// <summary>
/// 执行SQL语句返回DS
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <returns>DS</returns>
public static DataSet ExecuteQuery(string cmdText)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
DataSet ds = new DataSet();
try
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
da.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException e)
{
//conn.Close();
throw new Exception(e.Message);
}
finally
{
//conn.Dispose();
//conn.Close();
}
return ds;
}
}
/// <summary>
/// 从数据库中去除表字段的详细信息
/// </summary>
/// <param name="tbName">表名</param>
/// <returns></returns>
public static List<DBModel> GetDBmodel(string tbName)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT");
sb.Append(" table_name = Case When A.colorder=1 Then D.name Else '' End,");
sb.Append(" table_Directions = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,");
sb.Append(" field_index = A.colorder,");
sb.Append(" field_name = A.name,");
sb.Append(" field_Directions = isnull(G.[value],''),");
sb.Append(" tag = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,");
sb.Append(" iskey = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (");
sb.Append(" SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then 'True' else 'False' end,");
sb.Append(" field_type = B.name,");
sb.Append(" field_size = A.Length,");
sb.Append(" field_length = COLUMNPROPERTY(A.id,A.name,'PRECISION')");
sb.Append(" FROM");
sb.Append(" syscolumns A");
sb.Append(" Left Join");
sb.Append(" systypes B");
sb.Append(" On");
sb.Append(" A.xusertype=B.xusertype");
sb.Append(" Inner Join");
sb.Append(" sysobjects D");
sb.Append(" On");
sb.Append(" A.id=D.id and D.xtype='U' and D.name<>'dtproperties'");
sb.Append(" Left Join");
sb.Append(" syscomments E");
sb.Append(" On");
sb.Append(" A.cdefault=E.id");
sb.Append(" Left Join");
sb.Append(" sys.extended_properties G");
sb.Append(" On");
sb.Append(" A.id=G.major_id and A.colid=G.minor_id");
sb.Append(" Left Join");
sb.Append(" sys.extended_properties F");
sb.Append(" On");
sb.Append(" D.id=F.major_id and F.minor_id=0");
sb.Append(string.Format(" where d.name='{0}'", tbName));
sb.Append(" Order By");
sb.Append(" A.id,A.colorder");
DataSet ds = ExecuteQuery(sb.ToString());
List<DBModel> list = new List<DBModel>();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string table_name, table_Directions, field_name, field_Directions, tag, field_type;
int field_index, field_size, field_length;
bool iskey;
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["iskey"].ToString()))
{
iskey = false;
}
else
{
iskey = ds.Tables[0].Rows[i]["iskey"].ToString() == "True" ? true : false;
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["field_index"].ToString()))
{
field_index = 0;
}
else
{
field_index = Convert.ToInt32(ds.Tables[0].Rows[i]["field_index"].ToString());
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["field_size"].ToString()))
{
field_size = 0;
}
else
{
field_size = Convert.ToInt32(ds.Tables[0].Rows[i]["field_size"].ToString());
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["field_length"].ToString()))
{
field_length = 0;
}
else
{
field_length = Convert.ToInt32(ds.Tables[0].Rows[i]["field_length"].ToString());
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["table_name"].ToString()))
{
table_name = string.Empty;
}
else
{
table_name = ds.Tables[0].Rows[i]["table_name"].ToString();
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["table_Directions"].ToString()))
{
table_Directions = string.Empty;
}
else
{
table_Directions = ds.Tables[0].Rows[i]["table_Directions"].ToString();
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["field_name"].ToString()))
{
field_name = string.Empty;
}
else
{
field_name = ds.Tables[0].Rows[i]["field_name"].ToString();
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["field_Directions"].ToString()))
{
field_Directions = string.Empty;
}
else
{
field_Directions = ds.Tables[0].Rows[i]["field_Directions"].ToString();
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["tag"].ToString()))
{
tag = string.Empty;
}
else
{
tag = ds.Tables[0].Rows[i]["tag"].ToString();
}
if (string.IsNullOrEmpty(ds.Tables[0].Rows[i]["field_type"].ToString()))
{
field_type = string.Empty;
}
else
{
field_type = ds.Tables[0].Rows[i]["field_type"].ToString();
}
list.Add(new DBModel()
{
table_name = table_name,
table_Directions = table_Directions,
field_name = field_name,
field_Directions = field_Directions,
tag = tag,
field_type = field_type,
field_index = field_index,
field_size = field_size,
field_length = field_length,
iskey = iskey
});
}
}
return list;
}
}
/// <summary>
/// 数据库表的详细信息对象
/// </summary>
public class DBModel
{
/// <summary>
/// 表名
/// </summary>
public string table_name { get; set; }
/// <summary>
/// 表说明
/// </summary>
public string table_Directions { get; set; }
/// <summary>
/// 字段序号
/// </summary>
public int field_index { get; set; }
/// <summary>
/// 字段名
/// </summary>
public string field_name { get; set; }
/// <summary>
/// 字段说明
/// </summary>
public string field_Directions { get; set; }
/// <summary>
/// 标识
/// </summary>
public string tag { get; set; }
/// <summary>
/// 是否主键
/// </summary>
public bool iskey { get; set; }
/// <summary>
/// 类别
/// </summary>
public string field_type { get; set; }
/// <summary>
/// 占用字节
/// </summary>
public int field_size { get; set; }
/// <summary>
/// 长度
/// </summary>
public int field_length { get; set; }
}
}
以上帮助类将用于后续我们从数据库中获取到关于表的详细属性
下面我们再打开T4模板文件，在其中编辑如下：
<#@ template debug="false" hostspecific="True" language="C#" #>
<#@include file="$(ProjectDir)\Manager.ttinclude"#>
<# var manager = Manager.Create(Host, GenerationEnvironment); #>
<#@ assembly name="System.Core" #>
<#@ Assembly Name="$(ProjectDir)\bin\Debug\EntityFramework.dll" #>
<#@ Assembly Name="$(ProjectDir)\bin\Debug\GenCodeTemplate.dll" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.Entity" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Linq.Expressions" #>
<#@ import namespace="GenCodeTemplate" #>
<#@ output extension=".cs" #>
<#
string[] tbNames= {"OutSou_Abnormal"};//多表用，分割如a,b,c
string nspace=TempHelper.nspace+".Entities";//这里是生成后的类的命名空间，按照自己的项目结构拼接
string solutionsPath = Host.ResolveAssemblyReference("$(SolutionDir)");
string projectPath = Host.ResolveAssemblyReference("$(ProjectDir)");
foreach(var tbName in tbNames){
List<DBModel> list = TempHelper.GetDBmodel(tbName);
manager.StartNewFile( aaa@qq.com"TextEntities\"+tbName+".cs");//这里选择一个文件夹保存我们生成后的实体类
#>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace <#= nspace #> {
//[Table("<#= tbName #>")]
public class <#= tbName #> {
<# foreach(var item in list){ #>
<# if (item.field_type=="varchar" || item.field_type=="nvarchar" || item.field_type=="text") {#>
<# if (!string.IsNullOrEmpty(item.field_Directions)) {#>
/// <summary>
/// <#= item.field_Directions #>
/// </summary>
<# } #>
public string <#= item.field_name #>{ get; set; }
<# } #>
<# if (item.field_type=="datetime") {#>
<# if (!string.IsNullOrEmpty(item.field_Directions)) {#>
/// <summary>
/// <#= item.field_Directions #>
/// </summary>
<# } #>
public DateTime? <#= item.field_name #>{ get; set; }
<# } #>
<# if (item.field_type=="int"|| item.field_type=="smallint" || item.field_type=="bigint") {#>
<# if (!string.IsNullOrEmpty(item.field_Directions)) {#>
/// <summary>
/// <#= item.field_Directions #>
/// </summary>
<# } #>
public int <#= item.field_name #>{ get; set; }
<# } #>
<# if (item.field_type=="decimal") {#>
<# if (!string.IsNullOrEmpty(item.field_Directions)) {#>
/// <summary>
/// <#= item.field_Directions #>
/// </summary>
<# } #>
public decimal? <#= item.field_name #>{ get; set; }
<# } #>
<# if (item.field_type=="smallmoney") {#>
<# if (!string.IsNullOrEmpty(item.field_Directions)) {#>
/// <summary>
/// <#= item.field_Directions #>
/// </summary>
<# } #>
public decimal? <#= item.field_name #>{ get; set; }
<# } #>
<#
}
#>
}
}
<# } #>
<# manager.EndBlock(); #>
<# manager.Process(true); #>
上面的T4模板具体生成位置及命名空间我们要按照自己的项目的需求进行拼接，完成后我们保存T4模板，将会自动生成实体类映射，如下：