注：本文系作者原創，但可隨意轉載。
現在呆的公司使用的數據庫幾乎都是MySQL。編程方式DatabaseFirst。即先寫數據庫設計，表設計按照規范好的文檔寫進Excel里，然后用公司的宏，生成建表腳本和實體類文件。
之前就見識過T4模板生成SQL實體類文件，但還沒自己實踐過，這次正好實現一下生成MySQL的實體類。
目標類文件結構大致如下：
1 //----------------------------------------------------------------------- 2 // <copyright file=" User 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 Console4Test13 {14 /// <summary>15 /// UserProfile2 Entity Model16 /// </summary> 17 [Serializable]18 public class UserProfile219 {20 /// <summary>21 /// 主鍵ID22 /// </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，感覺有點兒浪費?？戳讼翸ySQL的系統庫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 Entity127 {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 Field144 {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 project163 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 project168 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 #>.cs32 // * 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 Console4Test45 {46 /// <summary>47 /// <#= entity.EntityName #> Entity Model48 /// </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]等屬性。具體格式等可自行擴展。
新聞熱點
疑難解答
