你的位置:首页 > ASP.net教程

[ASP.net教程]使用T4模板生成MySql数据库实体类


  现在呆的公司使用的数据库几乎都是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 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 }

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 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 #>

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."#> 5 <#@ assembly name="System."#> 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."#> 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 } #>

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 #>.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 #>

TextTemplate

 

  至此,已基本实现。在需要执行的模板里按下Ctrl+S,它就会执行一遍。

  里面有些写死的东西,可以调整到配置文件或其他地方。比如是否是WCF模型,如果是的话会自动加上[DataMember]等属性。具体格式等可自行扩展。