注：本文系作者原创，但可随意转载。
现在呆的公司使用的数据库几乎都是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类就可以了，这个方法应该比较流行吧，看了下比较简单，试了下也可以就没看别的方法。
附上他的博客地址：damieng.com/blog/2009/1…
下面附上他的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]等属性。具体格式等可自行扩展。
