使用T4模板生成MySql数据库实体类
注：本文系作者原创，但可随意转载。
现在呆的公司使用的数据库几乎都是MySQL。编程方式DatabaseFirst。即先写数据库设计，表设计按照规范好的文档写进EXCEL里，然后用公司的宏，生成建表脚本和实体类文件。
之前就见识过T4模板生成SQL实体类文件，但还没自己实践过，这次正好实现一下生成MySQL的实体类。
目标类文件结构大致如下：
1 //----------------------------------------------------------------------- 2 // <copyright file=" UserProfile2.cs" company="xxx Enterprises"> 3 // * Copyright (C) 2015 xxx Enterprises All Rights Reserved 4 // * version : 4.0.30319.18444 5 // * author : auto generated by T4 6 // * FileName: UserProfile2.cs 7 // * history : Created by T4 11/24/2015 18:05:30 8 // </copyright> 9 //----------------------------------------------------------------------- 10 using System; 11 12 namespace Console4Test 13 { 14 /// <summary> 15 /// UserProfile2 Entity Model 16 /// </summary> 17 [Serializable] 18 public class UserProfile2 19 { 20 /// <summary> 21 /// 主键ID 22 /// </summary> 23 public string ID { get; set; } 24 25 /// <summary> 26 /// 姓名 27 /// </summary> 28 public string Name { get; set; } 29 30 /// <summary> 31 /// 年龄 32 /// </summary> 33 public int Age { get; set; } 34 35 /// <summary> 36 /// 性别 37 /// </summary> 38 public int Gender { get; set; } 39 } 40 }
主要思路其实就两步：
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等直接返回类型字符串，不做处理，让编译报错即可。
1 <#@ assembly name="System.Core"#> 2 <#@ assembly name="System.Data"#> 3 <#@ assembly name="$(ProjectDir)\PublicDll\MySql.Data.dll" #> 4 <#@ import namespace="System" #> 5 <#@ import namespace="System.Data" #> 6 <#@ import namespace="System.Collections.Generic" #> 7 <#@ import namespace="System.Linq" #> 8 <#@ import namespace="MySql.Data.MySqlClient" #> 9 <#+ 10 public class EntityHelper 11 { 12 public static List<Entity> GetEntities(string connectionString, List<string> databases) 13 { 14 var list = new List<Entity>(); 15 var conn = new MySqlConnection(connectionString); 16 try 17 { 18 conn.Open(); 19 var dbs = string.Join("','", databases.ToArray()); 20 var cmd = string.Format(@"SELECT `information_schema`.`COLUMNS`.`TABLE_SCHEMA` 21 ,`information_schema`.`COLUMNS`.`TABLE_NAME` 22 ,`information_schema`.`COLUMNS`.`COLUMN_NAME` 23 ,`information_schema`.`COLUMNS`.`DATA_TYPE` 24 ,`information_schema`.`COLUMNS`.`COLUMN_COMMENT` 25 FROM `information_schema`.`COLUMNS` 26 WHERE `information_schema`.`COLUMNS`.`TABLE_SCHEMA` IN ('{0}') ", dbs); 27 using (var reader = MySqlHelper.ExecuteReader(conn, cmd)) 28 { 29 while (reader.Read()) 30 { 31 var db = reader["TABLE_SCHEMA"].ToString(); 32 var table = reader["TABLE_NAME"].ToString(); 33 var column = reader["COLUMN_NAME"].ToString(); 34 var type = reader["DATA_TYPE"].ToString(); 35 var comment = reader["COLUMN_COMMENT"].ToString(); 36 var entity = list.FirstOrDefault(x => x.EntityName == table); 37 if(entity == null) 38 { 39 entity = new Entity(table); 40 entity.Fields.Add(new Field 41 { 42 Name = column, 43 Type = GetCLRType(type), 44 Comment = comment 45 }); 46 47 list.Add(entity); 48 } 49 else 50 { 51 entity.Fields.Add(new Field 52 { 53 Name = column, 54 Type = GetCLRType(type), 55 Comment = comment 56 }); 57 } 58 } 59 } 60 } 61 finally 62 { 63 conn.Close(); 64 } 65 66 return list; 67 } 68 69 public static string GetCLRType(string dbType) 70 { 71 switch(dbType) 72 { 73 case "tinyint": 74 case "smallint": 75 case "mediumint": 76 case "int": 77 case "integer": 78 return "int"; 79 case "double": 80 return "double"; 81 case "float": 82 return "float"; 83 case "decimal": 84 return "decimal"; 85 case "numeric": 86 case "real": 87 return "decimal"; 88 case "bit": 89 return "bool"; 90 case "date": 91 case "time": 92 case "year": 93 case "datetime": 94 case "timestamp": 95 return "DateTime"; 96 case "tinyblob": 97 case "blob": 98 case "mediumblob": 99 case "longblog": 100 case "binary": 101 case "varbinary": 102 return "byte[]"; 103 case "char": 104 case "varchar": 105 case "tinytext": 106 case "text": 107 case "mediumtext": 108 case "longtext": 109 return "string"; 110 case "point": 111 case "linestring": 112 case "polygon": 113 case "geometry": 114 case "multipoint": 115 case "multilinestring": 116 case "multipolygon": 117 case "geometrycollection": 118 case "enum": 119 case "set": 120 default: 121 return dbType; 122 } 123 } 124 } 125 126 public class Entity 127 { 128 public Entity() 129 { 130 this.Fields = new List<Field>(); 131 } 132 133 public Entity(string name) 134 : this() 135 { 136 this.EntityName = name; 137 } 138 139 public string EntityName { get;set; } 140 public List<Field> Fields { get;set; } 141 } 142 143 public class Field 144 { 145 public string Name { get;set; } 146 public string Type { get;set; } 147 public string Comment { get;set; } 148 } 149 #>
这里需要注意的大概有三点：
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类代码：
1 <#@ assembly name="System.Core"#> 2 <#@ assembly name="System.Data.Linq"#> 3 <#@ assembly name="EnvDTE"#> 4 <#@ assembly name="System.Xml"#> 5 <#@ assembly name="System.Xml.Linq"#> 6 <#@ import namespace="System"#> 7 <#@ import namespace="System.CodeDom"#> 8 <#@ import namespace="System.CodeDom.Compiler"#> 9 <#@ import namespace="System.Collections.Generic"#> 10 <#@ import namespace="System.Data.Linq"#> 11 <#@ import namespace="System.Data.Linq.Mapping"#> 12 <#@ import namespace="System.IO"#> 13 <#@ import namespace="System.Linq"#> 14 <#@ import namespace="System.Reflection"#> 15 <#@ import namespace="System.Text"#> 16 <#@ import namespace="System.Xml.Linq"#> 17 <#@ import namespace="Microsoft.VisualStudio.TextTemplating"#> 18 <#+ 19 20 // Manager class records the various blocks so it can split them up 21 class Manager { 22 private class Block { 23 public String Name; 24 public int Start, Length; 25 } 26 27 private Block currentBlock; 28 private List<Block> files = new List<Block>(); 29 private Block footer = new Block(); 30 private Block header = new Block(); 31 private ITextTemplatingEngineHost host; 32 private StringBuilder template; 33 protected List<String> generatedFileNames = new List<String>(); 34 35 public static Manager Create(ITextTemplatingEngineHost host, StringBuilder template) { 36 return (host is IServiceProvider) ? new VSManager(host, template) : new Manager(host, template); 37 } 38 39 public void StartNewFile(String name) { 40 if (name == null) 41 throw new ArgumentNullException("name"); 42 CurrentBlock = new Block { Name = name }; 43 } 44 45 public void StartFooter() { 46 CurrentBlock = footer; 47 } 48 49 public void StartHeader() { 50 CurrentBlock = header; 51 } 52 53 public void EndBlock() { 54 if (CurrentBlock == null) 55 return; 56 CurrentBlock.Length = template.Length - CurrentBlock.Start; 57 if (CurrentBlock != header && CurrentBlock != footer) 58 files.Add(CurrentBlock); 59 currentBlock = null; 60 } 61 62 public virtual void Process(bool split) { 63 if (split) { 64 EndBlock(); 65 String headerText = template.ToString(header.Start, header.Length); 66 String footerText = template.ToString(footer.Start, footer.Length); 67 String outputPath = Path.GetDirectoryName(host.TemplateFile); 68 files.Reverse(); 69 foreach(Block block in files) { 70 String fileName = Path.Combine(outputPath, block.Name); 71 String content = headerText + template.ToString(block.Start, block.Length) + footerText; 72 generatedFileNames.Add(fileName); 73 CreateFile(fileName, content); 74 template.Remove(block.Start, block.Length); 75 } 76 } 77 } 78 79 protected virtual void CreateFile(String fileName, String content) { 80 if (IsFileContentDifferent(fileName, content)) 81 File.WriteAllText(fileName, content); 82 } 83 84 public virtual String GetCustomToolNamespace(String fileName) { 85 return null; 86 } 87 88 public virtual String DefaultProjectNamespace { 89 get { return null; } 90 } 91 92 protected bool IsFileContentDifferent(String fileName, String newContent) { 93 return !(File.Exists(fileName) && File.ReadAllText(fileName) == newContent); 94 } 95 96 private Manager(ITextTemplatingEngineHost host, StringBuilder template) { 97 this.host = host; 98 this.template = template; 99 } 100 101 private Block CurrentBlock { 102 get { return currentBlock; } 103 set { 104 if (CurrentBlock != null) 105 EndBlock(); 106 if (value != null) 107 value.Start = template.Length; 108 currentBlock = value; 109 } 110 } 111 112 private class VSManager: Manager { 113 private EnvDTE.ProjectItem templateProjectItem; 114 private EnvDTE.DTE dte; 115 private Action<String> checkOutAction; 116 private Action<IEnumerable<String>> projectSyncAction; 117 118 public override String DefaultProjectNamespace { 119 get { 120 return templateProjectItem.ContainingProject.Properties.Item("DefaultNamespace").Value.ToString(); 121 } 122 } 123 124 public override String GetCustomToolNamespace(string fileName) { 125 return dte.Solution.FindProjectItem(fileName).Properties.Item("CustomToolNamespace").Value.ToString(); 126 } 127 128 public override void Process(bool split) { 129 if (templateProjectItem.ProjectItems == null) 130 return; 131 base.Process(split); 132 projectSyncAction.EndInvoke(projectSyncAction.BeginInvoke(generatedFileNames, null, null)); 133 } 134 135 protected override void CreateFile(String fileName, String content) { 136 if (IsFileContentDifferent(fileName, content)) { 137 CheckoutFileIfRequired(fileName); 138 File.WriteAllText(fileName, content); 139 } 140 } 141 142 internal VSManager(ITextTemplatingEngineHost host, StringBuilder template) 143 : base(host, template) { 144 var hostServiceProvider = (IServiceProvider) host; 145 if (hostServiceProvider == null) 146 throw new ArgumentNullException("Could not obtain IServiceProvider"); 147 dte = (EnvDTE.DTE) hostServiceProvider.GetService(typeof(EnvDTE.DTE)); 148 if (dte == null) 149 throw new ArgumentNullException("Could not obtain DTE from host"); 150 templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile); 151 checkOutAction = (String fileName) => dte.SourceControl.CheckOutItem(fileName); 152 projectSyncAction = (IEnumerable<String> keepFileNames) => ProjectSync(templateProjectItem, keepFileNames); 153 } 154 155 private static void ProjectSync(EnvDTE.ProjectItem templateProjectItem, IEnumerable<String> keepFileNames) { 156 var keepFileNameSet = new HashSet<String>(keepFileNames); 157 var projectFiles = new Dictionary<String, EnvDTE.ProjectItem>(); 158 var originalFilePrefix = Path.GetFileNameWithoutExtension(templateProjectItem.get_FileNames(0)) + "."; 159 foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems) 160 projectFiles.Add(projectItem.get_FileNames(0), projectItem); 161 162 // Remove unused items from the project 163 foreach(var pair in projectFiles) 164 if (!keepFileNames.Contains(pair.Key) && !(Path.GetFileNameWithoutExtension(pair.Key) + ".").StartsWith(originalFilePrefix)) 165 pair.Value.Delete(); 166 167 // Add missing files to the project 168 foreach(String fileName in keepFileNameSet) 169 if (!projectFiles.ContainsKey(fileName)) 170 templateProjectItem.ProjectItems.AddFromFile(fileName); 171 } 172 173 private void CheckoutFileIfRequired(String fileName) { 174 var sc = dte.SourceControl; 175 if (sc != null && sc.IsItemUnderSCC(fileName) && !sc.IsItemCheckedOut(fileName)) 176 checkOutAction.EndInvoke(checkOutAction.BeginInvoke(fileName, null, null)); 177 } 178 } 179 } #>
同样把这个Helper类的后缀名改为.ttinclude
需要注意的是这个文件引用了EnvDTE，看了下好像是操作VS用的，写VS插件什么的应该会用到吧。可直接从.net框架引用。但后来我把这个引用移除了好像也没什么影响。
最后贴上，我们用来执行的模板
1 <#@ template debug="false" hostspecific="true" language="C#" #> 2 <#@ include file="Manager.ttinclude" #> 3 <#@ include file="EntityHelper.ttinclude" #> 4 <# 5 // 是否是WCF服务模型 6 bool serviceModel = false; 7 8 // 数据库连接 9 var connectionString = @"server=127.0.0.1;uid=root;pwd=12345678;charset=utf8;"; 10 11 // 需要解析的数据库 12 var database = new List<string> { "chatroom" }; 13 14 // 文件版权信息 15 var copyright = DateTime.Now.Year + " xxxx Enterprises All Rights Reserved"; 16 var version = Environment.Version; 17 var author = "auto generated by T4"; 18 19 var manager = Manager.Create(Host, GenerationEnvironment); 20 var entities = EntityHelper.GetEntities(connectionString, database); 21 22 foreach(Entity entity in entities) 23 { 24 manager.StartNewFile(entity.EntityName + ".cs"); 25 #> 26 //----------------------------------------------------------------------- 27 // <copyright file=" <#= entity.EntityName #>.cs" company="xxxx Enterprises"> 28 // * Copyright (C) <#= copyright #> 29 // * version : <#= version #> 30 // * author : <#= author #> 31 // * FileName: <#= entity.EntityName #>.cs 32 // * history : Created by T4 <#= DateTime.Now #> 33 // </copyright> 34 //----------------------------------------------------------------------- 35 using System; 36 <# if(serviceModel) 37 { 38 #> 39 using System.Runtime.Serialization; 40 <# 41 } 42 #> 43 44 namespace Console4Test 45 { 46 /// <summary> 47 /// <#= entity.EntityName #> Entity Model 48 /// </summary> 49 [Serializable] 50 <# if(serviceModel) 51 { 52 #> 53 [DataContract] 54 <# 55 } 56 #> 57 public class <#= entity.EntityName #> 58 { 59 <# 60 for(int i = 0; i < entity.Fields.Count; i++) 61 { 62 if(i ==0){ 63 #> /// <summary> 64 /// <#= entity.Fields[i].Comment #> 65 /// </summary> 66 <# if(serviceModel) 67 { 68 #> 69 [DataMember] 70 <# 71 } 72 #> 73 public <#= entity.Fields[i].Type #> <#= entity.Fields[i].Name #> { get; set; } 74 <# 75 } 76 else{ 77 #> 78 /// <summary> 79 /// <#= entity.Fields[i].Comment #> 80 /// </summary> 81 <# if(serviceModel) 82 { 83 #> 84 [DataMember] 85 <# 86 } 87 #> 88 public <#= entity.Fields[i].Type #> <#= entity.Fields[i].Name #> { get; set; } 89 <# } 90 } 91 #> 92 } 93 } 94 <# 95 manager.EndBlock(); 96 } 97 98 manager.Process(true); 99 #>
至此，已基本实现。在需要执行的模板里按下Ctrl+S，它就会执行一遍。
里面有些写死的东西，可以调整到配置文件或其他地方。比如是否是WCF模型，如果是的话会自动加上[DataMember]等属性。具体格式等可自行扩展。
