注:本文系作者原创,但可随意转载。

  现在呆的公司使用的数据库几乎都是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]等属性。具体格式等可自行扩展。