使用T4模板根据数据库生成model层

前言

学习EF时接触到T4模板，感觉到了它的强大。既然EF的edmx文件下的tt文件可以生成model层的cs文件，那么我们也可以直接用T4模板生成model层喽。当然T4可以做更多事，不过今日我们只让它做这一件事。
本文主要参考以下文章：
MVC —- Manager.ttinclude内容
Multiple Output Files using T4

主要内容

T4模板介绍

T4，即4个T开头的英文字母组合：Text Template Transformation Toolkit。简单来说就是自定义规则代码生成文件。T4模板因其扩展名为“.tt”又叫做tt模板。
T4模板里面可以在<##>里面写入代码而达到类似aspx页面动态输出效果。
T4内的一些类和方法我们不再深究。

添加生成文件Manager.ttinclude

T4内Damien Guard的扩展可以方便的生成多个文件。这里借用上面参考文章的方法，将生成多个文件的扩展封装成一个单独的文件，在T4内生成多个文件时只要引用这个文件并且使用这个文件内的方法就行了。

首先，新建文本文件，保存以下代码为一个模板文件(例如保存文件名为Manager.ttinclude)。

<#@ assembly name="System.Core"#>
<#@ assembly name="System.Data.Linq"#>
<#@ assembly name="EnvDTE"#>
<#@ assembly name="System.Xml"#>
<#@ assembly name="System.Xml.Linq"#>
<#@ import namespace="System"#>
<#@ import namespace="System.CodeDom"#>
<#@ import namespace="System.CodeDom.Compiler"#>
<#@ import namespace="System.Collections.Generic"#>
<#@ import namespace="System.Data.Linq"#>
<#@ import namespace="System.Data.Linq.Mapping"#>
<#@ import namespace="System.IO"#>
<#@ import namespace="System.Linq"#>
<#@ import namespace="System.Reflection"#>
<#@ import namespace="System.Text"#>
<#@ import namespace="System.Xml.Linq"#>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating"#>
<#+// Manager class records the various blocks so it can split them up
class Manager {private class Block {public String Name;public int Start, Length;}private Block currentBlock;private List<Block> files = new List<Block>();private Block footer = new Block();private Block header = new Block();private ITextTemplatingEngineHost host;private StringBuilder template;protected List<String> generatedFileNames = new List<String>();public static Manager Create(ITextTemplatingEngineHost host, StringBuilder template) {return (host is IServiceProvider) ? new VSManager(host, template) : new Manager(host, template);}public void StartNewFile(String name) {if (name == null)throw new ArgumentNullException("name");CurrentBlock = new Block { Name = name };}public void StartFooter() {CurrentBlock = footer;}public void StartHeader() {CurrentBlock = header;}public void EndBlock() {if (CurrentBlock == null)return;CurrentBlock.Length = template.Length - CurrentBlock.Start;if (CurrentBlock != header && CurrentBlock != footer)files.Add(CurrentBlock);currentBlock = null;}public virtual void Process(bool split) {if (split) {EndBlock();String headerText = template.ToString(header.Start, header.Length);String footerText = template.ToString(footer.Start, footer.Length);String outputPath = Path.GetDirectoryName(host.TemplateFile);files.Reverse();foreach(Block block in files) {String fileName = Path.Combine(outputPath, block.Name);String content = headerText + template.ToString(block.Start, block.Length) + footerText;generatedFileNames.Add(fileName);CreateFile(fileName, content);template.Remove(block.Start, block.Length);}}}protected virtual void CreateFile(String fileName, String content) {if (IsFileContentDifferent(fileName, content))File.WriteAllText(fileName, content);}public virtual String GetCustomToolNamespace(String fileName) {return null;}public virtual String DefaultProjectNamespace {get { return null; }}protected bool IsFileContentDifferent(String fileName, String newContent) {return !(File.Exists(fileName) && File.ReadAllText(fileName) == newContent);}private Manager(ITextTemplatingEngineHost host, StringBuilder template) {this.host = host;this.template = template;}private Block CurrentBlock {get { return currentBlock; }set {if (CurrentBlock != null)EndBlock();if (value != null)value.Start = template.Length;currentBlock = value;}}private class VSManager: Manager {private EnvDTE.ProjectItem templateProjectItem;private EnvDTE.DTE dte;private Action<String> checkOutAction;private Action<IEnumerable<String>> projectSyncAction;public override String DefaultProjectNamespace {get {return templateProjectItem.ContainingProject.Properties.Item("DefaultNamespace").Value.ToString();}}public override String GetCustomToolNamespace(string fileName) {return dte.Solution.FindProjectItem(fileName).Properties.Item("CustomToolNamespace").Value.ToString();}public override void Process(bool split) {if (templateProjectItem.ProjectItems == null)return;base.Process(split);projectSyncAction.EndInvoke(projectSyncAction.BeginInvoke(generatedFileNames, null, null));}protected override void CreateFile(String fileName, String content) {if (IsFileContentDifferent(fileName, content)) {CheckoutFileIfRequired(fileName);File.WriteAllText(fileName, content);}}internal VSManager(ITextTemplatingEngineHost host, StringBuilder template): base(host, template) {var hostServiceProvider = (IServiceProvider) host;if (hostServiceProvider == null)throw new ArgumentNullException("Could not obtain IServiceProvider");dte = (EnvDTE.DTE) hostServiceProvider.GetService(typeof(EnvDTE.DTE));if (dte == null)throw new ArgumentNullException("Could not obtain DTE from host");templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile);checkOutAction = (String fileName) => dte.SourceControl.CheckOutItem(fileName);projectSyncAction = (IEnumerable<String> keepFileNames) => ProjectSync(templateProjectItem, keepFileNames);}private static void ProjectSync(EnvDTE.ProjectItem templateProjectItem, IEnumerable<String> keepFileNames) {var keepFileNameSet = new HashSet<String>(keepFileNames);var projectFiles = new Dictionary<String, EnvDTE.ProjectItem>();var originalFilePrefix = Path.GetFileNameWithoutExtension(templateProjectItem.get_FileNames(0)) + ".";foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems)projectFiles.Add(projectItem.get_FileNames(0), projectItem);// Remove unused items from the projectforeach(var pair in projectFiles)if (!keepFileNames.Contains(pair.Key) && !(Path.GetFileNameWithoutExtension(pair.Key) + ".").StartsWith(originalFilePrefix))pair.Value.Delete();// Add missing files to the projectforeach(String fileName in keepFileNameSet)if (!projectFiles.ContainsKey(fileName))templateProjectItem.ProjectItems.AddFromFile(fileName);}private void CheckoutFileIfRequired(String fileName) {var sc = dte.SourceControl;if (sc != null && sc.IsItemUnderSCC(fileName) && !sc.IsItemCheckedOut(fileName))checkOutAction.EndInvoke(checkOutAction.BeginInvoke(fileName, null, null));}}
} #>

T4内代码

创建T4模板，命名为DataModel。

粘贴以下代码：

<#@ template language="C#" hostspecific="True" debug="false"#>
<#@include file="Manager.ttinclude"#>
<#@ assembly name="System" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Data.DataSetExtensions" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<# var manager = Manager.Create(Host, GenerationEnvironment); #>
<#//你的当前model的命名空间string ModelNameSpace="T4Demo";//你的数据库连接字符串string ConnectStr = "Data Source=.;database=test;integrated Security =true;";SqlConnection MySqlConnection = new SqlConnection(ConnectStr);//此sql语句找到你指定数据库下的所有表string SelectYourTableNameStr = "SELECT * FROM sys.sysobjects WHERE TYPE='U'";SqlDataAdapter MySqlDataAdapter = new SqlDataAdapter(SelectYourTableNameStr, MySqlConnection);DataSet MyDataSet = new DataSet();MySqlDataAdapter.Fill(MyDataSet, "cacheTable");//得到table表DataTable MyDataTable = MyDataSet.Tables["cacheTable"];int MyDataTableRowsCount = MyDataTable.Rows.Count;for (int i = 0; i < MyDataTableRowsCount; i++){//表名//MyDataTable.Rows[i]["name"].ToString();//此sql语句找到你当前表下的所有键值，属性，是否为null，长度。string SelectYourKeyStr = @"SELECT syscolumns.name as keyname,systypes.name as keyproperty,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('"+ MyDataTable.Rows[i]["name"].ToString() + "')";MySqlDataAdapter = new SqlDataAdapter(SelectYourKeyStr, MySqlConnection);MyDataSet = new DataSet();MySqlDataAdapter.Fill(MyDataSet, "cacheTable");//得到key表DataTable MyKeyTable = MyDataSet.Tables["cacheTable"];//tt模板输出文件manager.StartNewFile(MyDataTable.Rows[i]["name"].ToString() +".cs");
#>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace <#=ModelNameSpace#>
{public  partial class <#=MyDataTable.Rows[i]["name"].ToString()#>{
<#              for (int keyi = 0; keyi < MyKeyTable.Rows.Count; keyi++){//key名//MyKeyTable.Rows[keyi]["keyname"].ToString();//属性//MyKeyTable.Rows[keyi]["keyproperty"].ToString().ToLower();//是否null//MyKeyTable.Rows[keyi]["isnullable"].ToString();//整型：tinyint   smallint     int    bigint//精确型：decimal   numeric   //近似型 float    real     //日期类型：datetime   // 特殊数据型  cursor timestamp   uniqueidentifier    //货币型smallmoney    money//输出的C#属性string keypropertyOutput = string.Empty;switch (MyKeyTable.Rows[keyi]["keyproperty"].ToString().ToLower()){case "smallint":keypropertyOutput = "short";break;case "int":keypropertyOutput = "int";break;case "bigint":keypropertyOutput = "long";break;case "real":keypropertyOutput = "float";break;case "float":keypropertyOutput = "double";break;case "money":keypropertyOutput = "decimal";break;case "datetime":keypropertyOutput = "DateTime";break;case "uniqueidentifier":keypropertyOutput = "Guid";break;case "bit":keypropertyOutput = "bool";break;case "tinyint":keypropertyOutput = "byte";break;case "image":keypropertyOutput = "byte[]";break;case "binary":keypropertyOutput = "byte[]";break;default:keypropertyOutput = "string";break;}//可为null且不是string（就是值类型）可以加?if (MyKeyTable.Rows[keyi]["isnullable"].ToString()=="1"&& keypropertyOutput!="string"){if (keypropertyOutput == "byte[]"){keypropertyOutput = "byte?[]";}else{keypropertyOutput = keypropertyOutput + "?";}}
#>public  <#=keypropertyOutput#>  <#=MyKeyTable.Rows[keyi]["keyname"].ToString()#>  { get; set; }
<#}
#>}
}
<#              //代码结束manager.EndBlock();MySqlConnection.Close();}
#>
<# manager.Process(true); #>

保存，或者点击【生成】-【转换所有T4模板】，就会在当前tt文件下生成你所需要的cs类。
这是我的数据库和表文件：

这是所生成的cs类文件：

这是UserInfo.cs文件：

总结

这样一来model层就生成成功了。以前生成model可能用动软代码生成器，现在直接在VS内部就可以生成成功。
视图或者存储过程也应该能生成，暂时没有尝试。
使用时直接修改T4代码内的命名空间字符串和数据库连接字符串就行了。

**注意：此T4模板内代码用于SqlServer数据库，其他数据库可能因为数据类型不同而生成失败，如有需要可自行修改。