注：本文系作者原创，但可随意转载。
现在呆的公司使用的数据库几乎都是MySQL。编程方式DatabaseFirst。即先写数据库设计，表设计按照规范好的文档写进EXCEL里，然后用公司的宏，生成建表脚本和实体类文件。
之前就见识过T4模板生成SQL实体类文件，但还没自己实践过，这次正好实现一下生成MySQL的实体类。
目标类文件结构大致如下：
//-----------------------------------------------------------------------
// <copyright file=" UserProfile2.cs" company="xxx Enterprises">
// * Copyright (C) 2015 xxx Enterprises All Rights Reserved
// * version : 4.0.30319.18444
// * author : auto generated by T4
// * FileName: UserProfile2.cs
// * history : Created by T4 11/24/2015 18:05:30
// </copyright>
//-----------------------------------------------------------------------
using System; namespace Console4Test
{
/// <summary>
/// UserProfile2 Entity Model
/// </summary>
[Serializable]
public class UserProfile2
{
/// <summary>
/// 主键ID
/// </summary>
public string ID { get; set; } /// <summary>
/// 姓名
/// </summary>
public string Name { get; set; } /// <summary>
/// 年龄
/// </summary>
public int Age { get; set; } /// <summary>
/// 性别
/// </summary>
public int Gender { get; set; }
}
}
UserProfile2
主要思路其实就两步：
1）读取数据库表结构信息。（视个人情况，读取到的信息够用即可。）
2）根据读取到的表结构信息，为每个表生成实体类文件。
在实现第一步时，参考了一些SQL的文章。很多是需要多次执行SQL，感觉有点儿浪费。看了下MySQL的系统库information_schema，里面有张COLUMNS表，表里有TABLE_SCHEMA（即数据库名）, TABLE_NAME（表名）, COLUMN_NAME（列名）, DATA_TYPE（数据类型）, COLUMN_COMMENT（列说明）等字段，已能满足基本需求，因此读库时，只进行一次查询即可。
下面列出Helper的代码，只有2个方法，一是负责读取数据库表结构，二是把MySql数据库类型与C#数据类型匹配，这里我们建表时不允许为NULL，所以也不存在匹配可空类型，比较简单。可能有的匹配的不对，我没有全部试验过，一些特殊类型比如set, enum等直接返回类型字符串，不做处理，让编译报错即可。
<#@ assembly name="System.Core"#>
<#@ assembly name="System.Data"#>
<#@ assembly name="$(ProjectDir)\PublicDll\MySql.Data.dll" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="MySql.Data.MySqlClient" #>
<#+
public class EntityHelper
{
public static List<Entity> GetEntities(string connectionString, List<string> databases)
{
var list = new List<Entity>();
var conn = new MySqlConnection(connectionString);
try
{
conn.Open();
var dbs = string.Join("','", databases.ToArray());
var cmd = string.Format(@"SELECT `information_schema`.`COLUMNS`.`TABLE_SCHEMA`
,`information_schema`.`COLUMNS`.`TABLE_NAME`
,`information_schema`.`COLUMNS`.`COLUMN_NAME`
,`information_schema`.`COLUMNS`.`DATA_TYPE`
,`information_schema`.`COLUMNS`.`COLUMN_COMMENT`
FROM `information_schema`.`COLUMNS`
WHERE `information_schema`.`COLUMNS`.`TABLE_SCHEMA` IN ('{0}') ", dbs);
using (var reader = MySqlHelper.ExecuteReader(conn, cmd))
{
while (reader.Read())
{
var db = reader["TABLE_SCHEMA"].ToString();
var table = reader["TABLE_NAME"].ToString();
var column = reader["COLUMN_NAME"].ToString();
var type = reader["DATA_TYPE"].ToString();
var comment = reader["COLUMN_COMMENT"].ToString();
var entity = list.FirstOrDefault(x => x.EntityName == table);
if(entity == null)
{
entity = new Entity(table);
entity.Fields.Add(new Field
{
Name = column,
Type = GetCLRType(type),
Comment = comment
}); list.Add(entity);
}
else
{
entity.Fields.Add(new Field
{
Name = column,
Type = GetCLRType(type),
Comment = comment
});
}
}
}
}
finally
{
conn.Close();
} return list;
} public static string GetCLRType(string dbType)
{
switch(dbType)
{
case "tinyint":
case "smallint":
case "mediumint":
case "int":
case "integer":
return "int";
case "double":
return "double";
case "float":
return "float";
case "decimal":
return "decimal";
case "numeric":
case "real":
return "decimal";
case "bit":
return "bool";
case "date":
case "time":
case "year":
case "datetime":
case "timestamp":
return "DateTime";
case "tinyblob":
case "blob":
case "mediumblob":
case "longblog":
case "binary":
case "varbinary":
return "byte[]";
case "char":
case "varchar":
case "tinytext":
case "text":
case "mediumtext":
case "longtext":
return "string";
case "point":
case "linestring":
case "polygon":
case "geometry":
case "multipoint":
case "multilinestring":
case "multipolygon":
case "geometrycollection":
case "enum":
case "set":
default:
return dbType;
}
}
} public class Entity
{
public Entity()
{
this.Fields = new List<Field>();
} public Entity(string name)
: this()
{
this.EntityName = name;
} public string EntityName { get;set; }
public List<Field> Fields { get;set; }
} public class Field
{
public string Name { get;set; }
public string Type { get;set; }
public string Comment { get;set; }
}
#>
EntityHelper
这里需要注意的大概有三点：
1)我通过NuGet引用的MySQL.Data.dll直接引用报错找不到文件，我把它拷贝到PublicDLL\文件夹下进行引用。
2)此文件为模板执行时引用的文件，不需直接执行，因此将其后缀名改为.ttinclude。
3)MySQL在Windows下安装后默认表名等大小写不敏感。比如UserProfile表，读出来就是userprofile，这样生成的类名就是userprofile。因此需要对MySQL进行配置使其对大小写敏感。很简单可自行百度。
第一步实现后，我捣鼓了两下后发现执行模板只能生成一个文件，看的示例也比较简单，没有说生成多个文件的。后来搜索了一下，引用一个老外写的Helper类就可以了，这个方法应该比较流行吧，看了下比较简单，试了下也可以就没看别的方法。
附上他的博客地址：http://damieng.com/blog/2009/11/06/multiple-outputs-from-t4-made-easy-revisited
下面附上他的Helper类代码：
<#@ assembly name="System.Core"#>
<#@ assembly name="System.Data.Linq"#>
<#@ assembly name="EnvDTE"#>
<#@ assembly name="System.Xml"#>
<#@ assembly name="System.Xml.Linq"#>
<#@ import namespace="System"#>
<#@ import namespace="System.CodeDom"#>
<#@ import namespace="System.CodeDom.Compiler"#>
<#@ import namespace="System.Collections.Generic"#>
<#@ import namespace="System.Data.Linq"#>
<#@ import namespace="System.Data.Linq.Mapping"#>
<#@ import namespace="System.IO"#>
<#@ import namespace="System.Linq"#>
<#@ import namespace="System.Reflection"#>
<#@ import namespace="System.Text"#>
<#@ import namespace="System.Xml.Linq"#>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating"#>
<#+
// Manager class records the various blocks so it can split them up
class Manager {
private class Block {
public String Name;
public int Start, Length;
}
private Block currentBlock;
private List<Block> files = new List<Block>();
private Block footer = new Block();
private Block header = new Block();
private ITextTemplatingEngineHost host;
private StringBuilder template;
protected List<String> generatedFileNames = new List<String>();
public static Manager Create(ITextTemplatingEngineHost host, StringBuilder template) {
return (host is IServiceProvider) ? new VSManager(host, template) : new Manager(host, template);
}
public void StartNewFile(String name) {
if (name == null)
throw new ArgumentNullException("name");
CurrentBlock = new Block { Name = name };
}
public void StartFooter() {
CurrentBlock = footer;
}
public void StartHeader() {
CurrentBlock = header;
}
public void EndBlock() {
if (CurrentBlock == null)
return;
CurrentBlock.Length = template.Length - CurrentBlock.Start;
if (CurrentBlock != header && CurrentBlock != footer)
files.Add(CurrentBlock);
currentBlock = null;
}
public virtual void Process(bool split) {
if (split) {
EndBlock();
String headerText = template.ToString(header.Start, header.Length);
String footerText = template.ToString(footer.Start, footer.Length);
String outputPath = Path.GetDirectoryName(host.TemplateFile);
files.Reverse();
foreach(Block block in files) {
String fileName = Path.Combine(outputPath, block.Name);
String content = headerText + template.ToString(block.Start, block.Length) + footerText;
generatedFileNames.Add(fileName);
CreateFile(fileName, content);
template.Remove(block.Start, block.Length);
}
}
}
protected virtual void CreateFile(String fileName, String content) {
if (IsFileContentDifferent(fileName, content))
File.WriteAllText(fileName, content);
}
public virtual String GetCustomToolNamespace(String fileName) {
return null;
}
public virtual String DefaultProjectNamespace {
get { return null; }
}
protected bool IsFileContentDifferent(String fileName, String newContent) {
return !(File.Exists(fileName) && File.ReadAllText(fileName) == newContent);
}
private Manager(ITextTemplatingEngineHost host, StringBuilder template) {
this.host = host;
this.template = template;
}
private Block CurrentBlock {
get { return currentBlock; }
set {
if (CurrentBlock != null)
EndBlock();
if (value != null)
value.Start = template.Length;
currentBlock = value;
}
}
private class VSManager: Manager {
private EnvDTE.ProjectItem templateProjectItem;
private EnvDTE.DTE dte;
private Action<String> checkOutAction;
private Action<IEnumerable<String>> projectSyncAction;
public override String DefaultProjectNamespace {
get {
return templateProjectItem.ContainingProject.Properties.Item("DefaultNamespace").Value.ToString();
}
}
public override String GetCustomToolNamespace(string fileName) {
return dte.Solution.FindProjectItem(fileName).Properties.Item("CustomToolNamespace").Value.ToString();
}
public override void Process(bool split) {
if (templateProjectItem.ProjectItems == null)
return;
base.Process(split);
projectSyncAction.EndInvoke(projectSyncAction.BeginInvoke(generatedFileNames, null, null));
}
protected override void CreateFile(String fileName, String content) {
if (IsFileContentDifferent(fileName, content)) {
CheckoutFileIfRequired(fileName);
File.WriteAllText(fileName, content);
}
}
internal VSManager(ITextTemplatingEngineHost host, StringBuilder template)
: base(host, template) {
var hostServiceProvider = (IServiceProvider) host;
if (hostServiceProvider == null)
throw new ArgumentNullException("Could not obtain IServiceProvider");
dte = (EnvDTE.DTE) hostServiceProvider.GetService(typeof(EnvDTE.DTE));
if (dte == null)
throw new ArgumentNullException("Could not obtain DTE from host");
templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile);
checkOutAction = (String fileName) => dte.SourceControl.CheckOutItem(fileName);
projectSyncAction = (IEnumerable<String> keepFileNames) => ProjectSync(templateProjectItem, keepFileNames);
}
private static void ProjectSync(EnvDTE.ProjectItem templateProjectItem, IEnumerable<String> keepFileNames) {
var keepFileNameSet = new HashSet<String>(keepFileNames);
var projectFiles = new Dictionary<String, EnvDTE.ProjectItem>();
var originalFilePrefix = Path.GetFileNameWithoutExtension(templateProjectItem.get_FileNames()) + ".";
foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems)
projectFiles.Add(projectItem.get_FileNames(), projectItem);
// Remove unused items from the project
foreach(var pair in projectFiles)
if (!keepFileNames.Contains(pair.Key) && !(Path.GetFileNameWithoutExtension(pair.Key) + ".").StartsWith(originalFilePrefix))
pair.Value.Delete();
// Add missing files to the project
foreach(String fileName in keepFileNameSet)
if (!projectFiles.ContainsKey(fileName))
templateProjectItem.ProjectItems.AddFromFile(fileName);
}
private void CheckoutFileIfRequired(String fileName) {
var sc = dte.SourceControl;
if (sc != null && sc.IsItemUnderSCC(fileName) && !sc.IsItemCheckedOut(fileName))
checkOutAction.EndInvoke(checkOutAction.BeginInvoke(fileName, null, null));
}
}
} #>
T4Manager
同样把这个Helper类的后缀名改为.ttinclude
需要注意的是这个文件引用了EnvDTE，看了下好像是操作VS用的，写VS插件什么的应该会用到吧。可直接从.net框架引用。但后来我把这个引用移除了好像也没什么影响。
最后贴上，我们用来执行的模板
<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ include file="Manager.ttinclude" #>
<#@ include file="EntityHelper.ttinclude" #>
<#
// 是否是WCF服务模型
bool serviceModel = false; // 数据库连接
var connectionString = @"server=127.0.0.1;uid=root;pwd=12345678;charset=utf8;"; // 需要解析的数据库
var database = new List<string> { "chatroom" }; // 文件版权信息
var copyright = DateTime.Now.Year + " xxxx Enterprises All Rights Reserved";
var version = Environment.Version;
var author = "auto generated by T4"; var manager = Manager.Create(Host, GenerationEnvironment);
var entities = EntityHelper.GetEntities(connectionString, database); foreach(Entity entity in entities)
{
manager.StartNewFile(entity.EntityName + ".cs");
#>
//-----------------------------------------------------------------------
// <copyright file=" <#= entity.EntityName #>.cs" company="xxxx Enterprises">
// * Copyright (C) <#= copyright #>
// * version : <#= version #>
// * author : <#= author #>
// * FileName: <#= entity.EntityName #>.cs
// * history : Created by T4 <#= DateTime.Now #>
// </copyright>
//-----------------------------------------------------------------------
using System;
<# if(serviceModel)
{
#>
using System.Runtime.Serialization;
<#
}
#> namespace Console4Test
{
/// <summary>
/// <#= entity.EntityName #> Entity Model
/// </summary>
[Serializable]
<# if(serviceModel)
{
#>
[DataContract]
<#
}
#>
public class <#= entity.EntityName #>
{
<#
for(int i = ; i < entity.Fields.Count; i++)
{
if(i ==){
#> /// <summary>
/// <#= entity.Fields[i].Comment #>
/// </summary>
<# if(serviceModel)
{
#>
[DataMember]
<#
}
#>
public <#= entity.Fields[i].Type #> <#= entity.Fields[i].Name #> { get; set; }
<#
}
else{
#>
/// <summary>
/// <#= entity.Fields[i].Comment #>
/// </summary>
<# if(serviceModel)
{
#>
[DataMember]
<#
}
#>
public <#= entity.Fields[i].Type #> <#= entity.Fields[i].Name #> { get; set; }
<# }
}
#>
}
}
<#
manager.EndBlock();
} manager.Process(true);
#>
TextTemplate
至此，已基本实现。在需要执行的模板里按下Ctrl+S，它就会执行一遍。
里面有些写死的东西，可以调整到配置文件或其他地方。比如是否是WCF模型，如果是的话会自动加上[DataMember]等属性。具体格式等可自行扩展。