C#编写T4模板实现数据表到实体类的映射
开发工具：VS2017、SQLServer2017、PL/SQL
准备工作：下载并安装T4Toolbox
1、创建文件EntityTemplate.tt、EntityProperties.tt，注意选择这个哦
EntityTemplate.tt内容如下：
<#+
public class EntityTemplate : CSharpTemplate
{
private string tableName;
private string colProperties;
public EntityTemplate(string tableName,string colProperties)
{
this.tableName = tableName;
this.colProperties = colProperties;
}
public override string TransformText()
{
base.TransformText();
#>
using System;
namespace Model.DefaultDB
{
public class <#= tableName #>
{
<#= colProperties #>
}
}
<#+
return this.GenerationEnvironment.ToString();
}
}
#>
EntityProperties.tt内容如下：
<#+
public class EntityProperties : CSharpTemplate
{
private string colName;
private string colType;
private string colDesc;
public EntityProperties(string colName,string colType,string colDesc)
{
this.colName = colName;
this.colType = colType;
this.colDesc = colDesc;
}
public string DbTypeConvert(string dbType)
{
dbType = dbType.ToString().ToLower();
switch (dbType)
{
case "char":
case "varchar":
case "varchar2":
case "nvarchar":
case "nvarchar2":
case "clob":
case "nclob":
case "xmltype":
{
return "string";
}
case "date":
case "datetime":
case "datetime2":
{
return "DateTime";
}
case "tinyint":
{
return "byte";
}
case "number":
{
return "int";
}
case "uniqueidentifier":
{
return "Guid";
}
default:
{
return dbType;
}
}
}
public override string TransformText()
{
base.TransformText();
#>
/// <summary>
/// <#= colDesc #>
/// </summary>
public <#= DbTypeConvert(colType) #> <#= colName #> { get; set; }
<#+
return this.GenerationEnvironment.ToString();
}
}
#>
2、创建SQLServerDefaultDB.tt、OracleDefaultDB.tt，模板为vs自带的
SQLServerDefaultDB.tt内容如下：
<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ assembly name="$(SolutionDir)\Lib\Core.dll" #>
<#@ assembly name="$(SolutionDir)\Lib\Dal.dll" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="Core" #>
<#@ import namespace="Dal" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ include file="T4Toolbox.tt" #>
<#@ include file="EntityTemplate.tt" #>
<#@ include file="EntityProperties.tt" #>
<#
//连接字符串
string connStr = "Data Source=.;Initial Catalog=DefaultDB;enlist=false;User ID=sa;Password=123456";
//查询所有用户表
string sqlQueryTables = "select 表名=o.name from sys.sysobjects as o where o.xtype='U' and o.name<>'dtproperties' order by o.name";
//查询表定义
string sqlQueryColumns = @"
SELECT
表名=o.name,
字段名 = c.name,
类型 = t.name,
说明 = p.[value]
FROM syscolumns as c
inner join sys.sysobjects as o on c.id = o.id and o.xtype = 'U' and o.name <> 'dtproperties'
inner join sys.systypes as t on c.xusertype = t.xusertype
left join sys.extended_properties as p on c.id = p.major_id and c.colid = p.minor_id
order by o.name";
//替换T4模板中多余的注释
Regex reg1 = new Regex("// <autogenerated>\r\n");
Regex reg2 = new Regex("// This file was generated by T4 code generator SQLServerDefaultDB.tt.\r\n");
Regex reg3 = new Regex("// Any changes made to this file manually will be lost next time the file is regenerated.\r\n");
Regex reg4 = new Regex("// </autogenerated>\r\n");
//创建实体类存放路径
string curPath = Path.GetDirectoryName(Host.TemplateFile);
string defaultDBPath = Path.Combine(curPath, "DefaultDB");
if(!Directory.Exists(defaultDBPath))
{
Directory.CreateDirectory(defaultDBPath);
}
var dbProvider = new SQLServerService(connStr);
var dsTableName = dbProvider.ExecuteQuery(sqlQueryTables);
var dsColumnInfo = dbProvider.ExecuteQuery(sqlQueryColumns);
foreach (DataRow rTableName in dsTableName.Tables[0].Rows)
{
var tableName = rTableName["表名"].ToString();
StringBuilder colProperties = new StringBuilder();
foreach (DataRow rColumnInfo in dsColumnInfo.Tables[0].Rows)
{
if (rColumnInfo["表名"].ToString() == tableName)
{
EntityProperties prop = new EntityProperties(rColumnInfo["字段名"].ToString(),rColumnInfo["类型"].ToString(),rColumnInfo["说明"].ToString());
var propStr = prop.TransformText();
var propStrNew = reg1.Replace(propStr, "");
propStrNew = reg2.Replace(propStrNew, "");
propStrNew = reg3.Replace(propStrNew, "");
propStrNew = reg4.Replace(propStrNew, "");
colProperties.AppendLine(propStrNew);
}
}
string fileName = string.Format(@"{0}\{1}.cs", defaultDBPath, tableName);
EntityTemplate template = new EntityTemplate(tableName,colProperties.ToString());
template.Output.Encoding = Encoding.UTF8;
template.RenderToFile(fileName);
}
#>
OracleDefaultDB.tt内容如下：
<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ assembly name="$(SolutionDir)\Lib\Core.dll" #>
<#@ assembly name="$(SolutionDir)\Lib\Dal.dll" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="Core" #>
<#@ import namespace="Dal" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ include file="T4Toolbox.tt" #>
<#@ include file="EntityTemplate.tt" #>
<#@ include file="EntityProperties.tt" #>
<#
//连接字符串
string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=00.000.00.000)(PORT=0000))(CONNECT_DATA=(SID=GZTNS)));User ID=DefaultDB;Password=123456";
//查询所有用户表
string sqlQueryTables = "select distinct o.table_name 表名 from user_tab_columns o order by o.table_name";
//查询表定义
string sqlQueryColumns = @"
select
o.table_name 表名,
c.column_name 字段名,
o.data_type 类型,
c.comments 说明
from user_col_comments c
inner join user_tab_columns o on c.column_name = o.column_name and c.table_name = o.table_name order by o.table_name";
//替换T4模板中多余的注释
Regex reg1 = new Regex("// <autogenerated>\r\n");
Regex reg2 = new Regex("// This file was generated by T4 code generator OracleDefaultDB.tt.\r\n");
Regex reg3 = new Regex("// Any changes made to this file manually will be lost next time the file is regenerated.\r\n");
Regex reg4 = new Regex("// </autogenerated>\r\n");
//创建实体类存放路径
string curPath = Path.GetDirectoryName(Host.TemplateFile);
string defaultDBPath = Path.Combine(curPath, "DefaultDB_Oracle");
if(!Directory.Exists(defaultDBPath))
{
Directory.CreateDirectory(defaultDBPath);
}
var dbProvider = new OracleService(connStr);
var dsTableName = dbProvider.ExecuteQuery(sqlQueryTables);
var dsColumnInfo = dbProvider.ExecuteQuery(sqlQueryColumns);
foreach (DataRow rTableName in dsTableName.Tables[0].Rows)
{
var tableName = rTableName["表名"].ToString();
StringBuilder colProperties = new StringBuilder();
foreach (DataRow rColumnInfo in dsColumnInfo.Tables[0].Rows)
{
if (rColumnInfo["表名"].ToString() == tableName)
{
EntityProperties prop = new EntityProperties(rColumnInfo["字段名"].ToString(),rColumnInfo["类型"].ToString(),rColumnInfo["说明"].ToString());
var propStr = prop.TransformText();
var propStrNew = reg1.Replace(propStr, "");
propStrNew = reg2.Replace(propStrNew, "");
propStrNew = reg3.Replace(propStrNew, "");
propStrNew = reg4.Replace(propStrNew, "");
colProperties.AppendLine(propStrNew);
}
}
string fileName = string.Format(@"{0}\{1}.cs", defaultDBPath, tableName);
EntityTemplate template = new EntityTemplate(tableName,colProperties.ToString());
template.Output.Encoding = Encoding.UTF8;
template.RenderToFile(fileName);
}
#>
实体类存放路径自己定义，搞定后，选择SQLServerDefaultDB.tt或OracleDefaultDB.tt，右击->运行自定义工具
如果数据库是SQLServer的话，可以打开profiler看下
PS：数据库访问部分，是ADO.NET的。若oracle，需要引用Oracle.DataAccess.dll。
最后生成的结果图如下：
DefaultDB_Oracle，184个数据表，16/17秒瞬间搞定~~
搬砖，努力搬砖。
