注：本文系作者原创，但可随意转载。
现在呆的公司使用的数据库几乎都是MySQL。编程方式DatabaseFirst。即先写数据库设计，表设计按照规范好的文档写进Excel里，然后用公司的宏，生成建表脚本和实体类文件。
之前就见识过T4模板生成SQL实体类文件，但还没自己实践过，这次正好实现一下生成MySQL的实体类。
目标类文件结构大致如下：
[flash=500,400]http://s1.knowsky.com/20160211/13hfjzlkhm446.gif[/flash][flash=500,400]http://s1.knowsky.com/20160211/bixpi5ecy0y46.gif[/flash]1//-----------------------------------------------------------------------2//<copyright file=" UserPRofile2.cs" company="xxx Enterprises">3//* Copyright (C) 2015 xxx Enterprises All Rights Reserved4//* version : 4.0.30319.184445//* author : auto generated by T46//* FileName: UserProfile2.cs7//* history : Created by T4 11/24/2015 18:05:308//</copyright>9//-----------------------------------------------------------------------10usingSystem;1112namespaceConsole4Test13{14///<summary>15///UserProfile2 Entity Model16///</summary>17[Serializable]18publicclassUserProfile219{20///<summary>21///主键ID22///</summary>23publicstringID {get;set; }2425///<summary>26///姓名27///</summary>28publicstringName {get;set; }2930///<summary>31///年龄32///</summary>33publicintAge {get;set; }3435///<summary>36///性别37///</summary>38publicintGender {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等直接返回类型字符串，不做处理，让编译报错即可。
[flash=500,400]http://s1.knowsky.com/20160211/13hfjzlkhm446.gif[/flash][flash=500,400]http://s1.knowsky.com/20160211/bixpi5ecy0y46.gif[/flash]1<#@ assembly name="System.Core"#>2<#@ assembly name="System.Data"#>3<#@ assembly name="$(ProjectDir)\PublicDll\MySql.Data.dll"#>4<#@ importnamespace="System"#>5<#@ importnamespace="System.Data"#>6<#@ importnamespace="System.Collections.Generic"#>7<#@ importnamespace="System.Linq"#>8<#@ importnamespace="MySql.Data.MySqlClient"#>9<#+10publicclassEntityHelper11{12publicstaticList<Entity> GetEntities(stringconnectionString, List<string>databases)13{14varlist =newList<Entity>();15varconn =newMySqlConnection(connectionString);16try17{18conn.Open();19vardbs =string.Join("','", databases.ToArray());20varcmd =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`25FROM `information_schema`.`COLUMNS`26WHERE `information_schema`.`COLUMNS`.`TABLE_SCHEMA` IN ('{0}')", dbs);27using(varreader =MySqlHelper.ExecuteReader(conn, cmd))28{29while(reader.Read())30{31vardb = reader["TABLE_SCHEMA"].ToString();32vartable = reader["TABLE_NAME"].ToString();33varcolumn = reader["COLUMN_NAME"].ToString();34vartype = reader["DATA_TYPE"].ToString();35varcomment = reader["COLUMN_COMMENT"].ToString();36varentity = list.FirstOrDefault(x => x.EntityName ==table);37if(entity ==null)38{39entity =newEntity(table);40entity.Fields.Add(newField41{42Name =column,43Type =GetCLRType(type),44Comment =comment45});4647list.Add(entity);48}49else50{51entity.Fields.Add(newField52{53Name =column,54Type =GetCLRType(type),55Comment =comment56});57}58}59}60}61finally62{63conn.Close();64}6566returnlist;67}6869publicstaticstringGetCLRType(stringdbType)70{71switch(dbType)72{73case"tinyint":74case"smallint":75case"mediumint":76case"int":77case"integer":78return"int";79case"double":80return"double";81case"float":82return"float";83case"decimal":84return"decimal";85case"numeric":86case"real":87return"decimal";88case"bit":89return"bool";90case"date":91case"time":92case"year":93case"datetime":94case"timestamp":95return"DateTime";96case"tinyblob":97case"blob":98case"mediumblob":99case"longblog":100case"binary":101case"varbinary":102return"byte[]";103case"char":104case"varchar":105case"tinytext":106case"text":107case"mediumtext":108case"longtext":109return"string";110case"point":111case"linestring":112case"polygon":113case"geometry":114case"multipoint":115case"multilinestring":116case"multipolygon":117case"geometrycollection":118case"enum":119case"set":120default:121returndbType;122}123}124}125126publicclassEntity127{128publicEntity()129{130this.Fields =newList<Field>();131}132133publicEntity(stringname)134:this()135{136this.EntityName =name;137}138139publicstringEntityName {get;set; }140publicList<Field> Fields {get;set; }141}142143publicclassField144{145publicstringName {get;set; }146publicstringType {get;set; }147publicstringComment {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类代码：
[flash=500,400]http://s1.knowsky.com/20160211/13hfjzlkhm446.gif[/flash][flash=500,400]http://s1.knowsky.com/20160211/bixpi5ecy0y46.gif[/flash]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<#@ importnamespace="System"#>7<#@ importnamespace="System.CodeDom"#>8<#@ importnamespace="System.CodeDom.Compiler"#>9<#@ importnamespace="System.Collections.Generic"#>10<#@ importnamespace="System.Data.Linq"#>11<#@ importnamespace="System.Data.Linq.Mapping"#>12<#@ importnamespace="System.IO"#>13<#@ importnamespace="System.Linq"#>14<#@ importnamespace="System.Reflection"#>15<#@ importnamespace="System.Text"#>16<#@ importnamespace="System.Xml.Linq"#>17<#@ importnamespace="Microsoft.VisualStudio.TextTemplating"#>18<#+19 20//Manager class records the various blocks so it can split them up21classManager {22 privateclassBlock {23 publicString Name;24 publicintStart, Length;25 }26 27 privateBlock currentBlock;28 privateList<Block> files =newList<Block>();29 privateBlock footer =newBlock();30 privateBlock header =newBlock();31 privateITextTemplatingEngineHost host;32 privateStringBuilder template;33 protectedList<String> generatedFileNames =newList<String>();34 35 publicstaticManager Create(ITextTemplatingEngineHost host, StringBuilder template) {36 return(hostisIServiceProvider) ?newVSManager(host, template) :newManager(host, template);37 }38 39 publicvoidStartNewFile(String name) {40 if(name ==null)41 thrownewArgumentNullException("name");42 CurrentBlock =newBlock { Name =name };43 }44 45 publicvoidStartFooter() {46 CurrentBlock =footer;47 }48 49 publicvoidStartHeader() {50 CurrentBlock =header;51 }52 53 publicvoidEndBlock() {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 publicvirtualvoidProcess(boolsplit) {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 blockinfiles) {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 protectedvirtualvoidCreateFile(String fileName, String content) {80 if(IsFileContentDifferent(fileName, content))81 File.WriteAllText(fileName, content);82 }83 84 publicvirtualString GetCustomToolNamespace(String fileName) {85 returnnull;86 }87 88 publicvirtualString DefaultProjectNamespace {89 get{returnnull; }90 }91 92 protectedboolIsFileContentDifferent(String fileName, String newContent) {93 return!(File.Exists(fileName) && File.ReadAllText(fileName) ==newContent);94 }95 96 privateManager(ITextTemplatingEngineHost host, StringBuilder template) {97 this.host =host;98 this.template =template;99 }100 101 privateBlock CurrentBlock {102 get{returncurrentBlock; }103 set{104 if(CurrentBlock !=null)105 EndBlock();106 if(value !=null)107 value.Start =template.Length;108 currentBlock =value;109 }110 }111 112 privateclassVSManager: Manager {113 privateEnvDTE.ProjectItem templateProjectItem;114 privateEnvDTE.DTE dte;115 privateAction<String>checkOutAction;116 privateAction<IEnumerable<String>>projectSyncAction;117 118 publicoverrideString DefaultProjectNamespace {119 get{120 returntemplateProjectItem.ContainingProject.Properties.Item("DefaultNamespace").Value.ToString();121 }122 }123 124 publicoverrideString GetCustomToolNamespace(stringfileName) {125 returndte.Solution.FindProjectItem(fileName).Properties.Item("CustomToolNamespace").Value.ToString();126 }127 128 publicoverridevoidProcess(boolsplit) {129 if(templateProjectItem.ProjectItems ==null)130 return;131 base.Process(split);132 projectSyncAction.EndInvoke(projectSyncAction.BeginInvoke(generatedFileNames,null,null));133 }134 135 protectedoverridevoidCreateFile(String fileName, String content) {136 if(IsFileContentDifferent(fileName, content)) {137 CheckoutFileIfRequired(fileName);138 File.WriteAllText(fileName, content);139 }140 }141 142 internalVSManager(ITextTemplatingEngineHost host, StringBuilder template)143 :base(host, template) {144 varhostServiceProvider =(IServiceProvider) host;145 if(hostServiceProvider ==null)146 thrownewArgumentNullException("Could not obtain IServiceProvider");147 dte = (EnvDTE.DTE) hostServiceProvider.GetService(typeof(EnvDTE.DTE));148 if(dte ==null)149 thrownewArgumentNullException("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 privatestaticvoidProjectSync(EnvDTE.ProjectItem templateProjectItem, IEnumerable<String>keepFileNames) {156 varkeepFileNameSet =newHashSet<String>(keepFileNames);157 varprojectFiles =newDictionary<String, EnvDTE.ProjectItem>();158 varoriginalFilePrefix = Path.GetFileNameWithoutExtension(templateProjectItem.get_FileNames(0)) +".";159 foreach(EnvDTE.ProjectItem projectItemintemplateProjectItem.ProjectItems)160 projectFiles.Add(projectItem.get_FileNames(0), projectItem);161 162 //Remove unused items from the project163 foreach(varpairinprojectFiles)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 fileNameinkeepFileNameSet)169 if(!projectFiles.ContainsKey(fileName))170 templateProjectItem.ProjectItems.AddFromFile(fileName);171 }172 173 privatevoidCheckoutFileIfRequired(String fileName) {174 varsc =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框架引用。但后来我把这个引用移除了好像也没什么影响。
最后贴上，我们用来执行的模板
[flash=500,400]http://s1.knowsky.com/20160211/13hfjzlkhm446.gif[/flash][flash=500,400]http://s1.knowsky.com/20160211/bixpi5ecy0y46.gif[/flash]1<#@ template debug="false"hostspecific="true"language="C#"#>2<#@ include file="Manager.ttinclude"#>3<#@ include file="EntityHelper.ttinclude"#>4<#5//是否是WCF服务模型6boolserviceModel =false;78//数据库连接9varconnectionString =@"server=127.0.0.1;uid=root;pwd=12345678;charset=utf8;";1011//需要解析的数据库12vardatabase =newList<string> {"chatroom"};1314//文件版权信息15varcopyright = DateTime.Now.Year +"xxxx Enterprises All Rights Reserved";16varversion =Environment.Version;17varauthor ="auto generated by T4";1819varmanager =Manager.Create(Host, GenerationEnvironment);20varentities =EntityHelper.GetEntities(connectionString, database);2122foreach(Entity entityinentities)23{24manager.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//-----------------------------------------------------------------------35usingSystem;36<#if(serviceModel)37{38#>39usingSystem.Runtime.Serialization;40<#41}42#>4344namespaceConsole4Test45{46///<summary>47///<#= entity.EntityName #>Entity Model48///</summary>49[Serializable]50<#if(serviceModel)51{52#>53[DataContract]54<#55}56#>57publicclass<#= entity.EntityName #>58{59<#60for(inti =0; i < entity.Fields.Count; i++)61{62if(i ==0){63#>///<summary>64///<#= entity.Fields[i].Comment #>65///</summary>66<#if(serviceModel)67{68#>69[DataMember]70<#71}72#>73public<#= entity.Fields[i].Type #> <#= entity.Fields[i].Name #> {get;set; }74<#75}76else{77#>78///<summary>79///<#= entity.Fields[i].Comment #>80///</summary>81<#if(serviceModel)82{83#>84[DataMember]85<#86}87#>88public<#= entity.Fields[i].Type #> <#= entity.Fields[i].Name #> {get;set; }89<# }90}91#>92}93}94<#95manager.EndBlock();96}9798manager.Process(true);99#>
TextTemplate
至此，已基本实现。在需要执行的模板里按下Ctrl+S，它就会执行一遍。
里面有些写死的东西，可以调整到配置文件或其他地方。比如是否是WCF模型，如果是的话会自动加上[DataMember]等属性。具体格式等可自行扩展。