使用T4模板生成MySql數據庫實體類，t4mysql
注：本文系作者原創，但可隨意轉載。
現在呆的公司使用的數據庫幾乎都是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 }
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等直接返回類型字符串，不做處理，讓編譯報錯即可。
![]()
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 #>
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類代碼：
![]()
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 } #>
T4Manager
同樣把這個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 #>
TextTemplate
至此，已基本實現。在需要執行的模板裡按下Ctrl+S，它就會執行一遍。
裡面有些寫死的東西，可以調整到配置文件或其他地方。比如是否是WCF模型，如果是的話會自動加上[DataMember]等屬性。具體格式等可自行擴展。
