T4 代码生成 Demo (抽奖程序)

参考自这位大狮的：  https://github.com/Pencroff/Dapper-DAL/blob/master/Dapper-DAL/Models/ModelGenerator.tt

项目Demo下载 http://download.csdn.net/detail/qq_21533697/9904071

• 支持Oracle,MSSQL,SQLite
• Demo项目是个抽奖小程序,抽奖只用到了LuckDraw表
• Demo用的SQLite包含库方便直接运行
• 里面用到Dapper就只写了Model层的模板,

---

文件目录

---

T4库

表结构读取抽象类  SchemaReader.ttinclude

 <#+
 /*
 The contents of this file are subject to the New BSD
  License (the "License"); you may not use this file
  except in compliance with the License. You may obtain a copy of
  the License at http://www.opensource.org/licenses/bsd-license.php

  Software distributed under the License is distributed on an
  "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
  implied. See the License for the specific language governing
  rights and limitations under the License.
 */

 string ConnectionString = "";
 string TableFilter = "";
 string TopNamespace = "";
 string Namespace = "";
 string ClassPrefix = "";
 string ClassSuffix = "";
 string SchemaName = null;
 bool IncludeViews;
 string[] ExcludeTablePrefixes = new string[]{};
 string _connectionString="";
 string _providerName="";

 static Regex rxCleanUp = new Regex(@"[^\w\d_]", RegexOptions.Compiled);

 static Func<string, string> CleanUp = (str) =>
 {
     str = rxCleanUp.Replace(str, "_");
     if (char.IsDigit(str[])) str = "_" + str;

     return str;
 };

 string CheckNullable(Column col)
 {
     string result="";
     if(col.IsNullable &&
         col.PropertyType !="byte[]" &&
         col.PropertyType !="string" &&
         col.PropertyType !="Microsoft.SqlServer.Types.SqlGeography" &&
         col.PropertyType !="Microsoft.SqlServer.Types.SqlGeometry"
         )
         result="?";
     return result;
 }

 static bool IsExcluded(string tablename, string[] ExcludeTablePrefixes)
 {
     for (int i = ; i < ExcludeTablePrefixes.Length; i++)
     {
         string s = ExcludeTablePrefixes[i];
         if(tablename.StartsWith(s)) return true;
     }
     return false;
 }

 abstract class SchemaReader
 {
     public abstract Tables ReadSchema(string connstr, string tableFilter);
     public GeneratedTextTransformation outer;
     public void WriteLine(string o)
     {
         outer.WriteLine(o);
     }

     public string GetPropertyType(string sqlType)
     {
         string sysType = "string";
         switch (sqlType)
         {
             case "bigint":
                 sysType = "long";
                 break;
             case "smallint":
                 sysType = "short";
                 break;
             case "int":
             case "number":
             case "integer":
                 sysType = "int";
                 break;
             case "uniqueidentifier":
                 sysType = "Guid";
                 break;
             case "smalldatetime":
             case "datetime":
             case "date":
             case "time":
                 sysType = "DateTime";
                 break;
             case "float":
                 sysType = "double";
                 break;
             case "real":
                 sysType = "float";
                 break;
             case "numeric":
             case "smallmoney":
             case "decimal":
             case "money":
                 sysType = "decimal";
                 break;
             case "tinyint":
                 sysType = "byte";
                 break;
             case "bit":
                 sysType = "bool";
                 break;
             case "image":
             case "binary":
             case "varbinary":
             case "timestamp":
                 sysType = "byte[]";
                 break;
             case "geography":
                 sysType = "Microsoft.SqlServer.Types.SqlGeography";
                 break;
             case "geometry":
                 sysType = "Microsoft.SqlServer.Types.SqlGeometry";
                 break;
         }
         return sysType;
     }
 } 

 public class Table
 {
     public List<Column> Columns;
     public string Name;
     public string Schema;
     public bool IsView;
     public string CleanName;
     public string ClassName;
     public string SequenceName;
     public bool Ignore;

     public Column PK
     {
         get
         {
             return this.Columns.SingleOrDefault(x=>x.IsPK);
         }
     }

     public Column GetColumn(string columnName)
     {
         return Columns.Single(x=>string.Compare(x.Name, columnName, true)==);
     }

     public Column this[string columnName]
     {
         get
         {
             return GetColumn(columnName);
         }
     }

 }

 public class Column
 {
     public string Name;
     public string PropertyName;
     public string PropertyType;
     public string DbType;
     public bool IsPK;
     public bool IsNullable;
     public bool IsAutoIncrement;
     public bool Ignore;
 }

 public class Tables : List<Table>
 {
     public Tables()
     {
     }

     public Table GetTable(string tableName)
     {
         return this.Single(x=>string.Compare(x.Name, tableName, true)==);
     }

     public Table this[string tableName]
     {
         get
         {
             return GetTable(tableName);
         }
     }

 }

 #>

SQLite表结构读取实现 SQLiteSchemaReader.ttinclude

 <#@ include file=".\SchemaReader.ttinclude" #>
 <#+
 Tables LoadTables()
 { 

     WriteLine("// This file was automatically generated by the Dapper.SimpleCRUD T4 Template");
     WriteLine("// Do not make changes directly to this file - edit the template instead");
     WriteLine("// ");
     WriteLine("// The following connection settings were used to generate this file");
     WriteLine("// ");
     WriteLine("//     Connection String : `{0}`", ConnectionString);
     WriteLine("");

     //DbProviderFactory _factory ;
     try
     {
         // _factory = DbProviderFactories.GetFactory(ProviderName);
     }
     catch (Exception x)
     {
         var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
         WriteLine("");
         WriteLine("// -----------------------------------------------------------------------------------------");
         WriteLine("// -----------------------------------------------------------------------------------------");
         WriteLine("");
         return new Tables();
     }

     try
     {
         Tables result;

         SchemaReader reader= new SqliteSchemaReader();
         result=reader.ReadSchema(ConnectionString, TableFilter);

         for (int i=result.Count-; i>=; i--)
         {
             if (SchemaName!=null && string.Compare(result[i].Schema, SchemaName, true)!=)
             {
                 result.RemoveAt(i);
                 continue;
             }
             if (!IncludeViews && result[i].IsView)
             {
                 result.RemoveAt(i);
                 continue;
             }
         }

         var rxClean = new Regex("^(Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
         foreach (var t in result)
         {
             t.ClassName = ClassPrefix + t.ClassName + ClassSuffix;
             foreach (var c in t.Columns)
             {
                 c.PropertyName = rxClean.Replace(c.PropertyName, "_$1");

                 // Make sure property name doesn't * with class name
                 if (c.PropertyName == t.ClassName)
                     c.PropertyName = "_" + c.PropertyName;
             }
         }

             return result;
     }
     catch (Exception x)
     {
         var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
         Warning(string.Format("Failed to read database schema - {0}", error));
         WriteLine("");
         WriteLine("// -----------------------------------------------------------------------------------------");
         WriteLine("// Failed to read database schema - {0}", error);
         WriteLine("// -----------------------------------------------------------------------------------------");
         WriteLine("");
         return new Tables();
     }

 }

 class SqliteSchemaReader : SchemaReader
     {

         private    string _connstr {get;set;}

         public override Tables ReadSchema(string connstr, string tableFilter)
         {
             _connstr = connstr;
             var result = new Tables();
             //pull the tables in a reader
             using (IDataReader rdr = ExecuteReader(TABLE_SQL + tableFilter)) // SQLitehelper.
             {
                 while (rdr.Read())
                 {
                     Table tbl = new Table();
                     tbl.Name = rdr["name"].ToString();
                     //tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
                     //tbl.IsView = string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true) == 0;
                     tbl.CleanName = CleanUp(tbl.Name);
                     // tbl.CleanName = T4Generator.CleanUp(tbl.Name);
                     if (tbl.CleanName.StartsWith("tbl_")) tbl.CleanName = tbl.CleanName.Replace("tbl_", "");
                     if (tbl.CleanName.StartsWith("tbl")) tbl.CleanName = tbl.CleanName.Replace("tbl", "");
                     tbl.CleanName = tbl.CleanName.Replace("_", "");
                     tbl.ClassName = tbl.CleanName;

                     result.Add(tbl);
                 }
            } 

             foreach (var tbl in result)
             {
                 tbl.Columns = LoadColumns(tbl);

                 //Mark the primary key
                 //string PrimaryKey = GetPK(tbl.Schema, tbl.Name);
                 //var pkColumn = tbl.Columns.SingleOrDefault(x => x.Name.ToLower().Trim() == PrimaryKey.ToLower().Trim());
                 //if (pkColumn != null)
                 //{
                 //    pkColumn.IsPK = true;
                 //}
             }

             return result;
         }

         List<Column> LoadColumns(Table tbl)
         {
             var result = new List<Column>();
             using (IDataReader rdr = ExecuteReader(COLUMN_SQL.Replace("@tableName", tbl.Name))) // SQLitehelper.
             {
                 while (rdr.Read())
                 {
                     Column col = new Column();
                     col.Name = rdr["name"].ToString();
                     col.PropertyName = CleanUp(col.Name);
                     //col.PropertyName = T4Generator.CleanUp(col.Name);
                     col.PropertyType = base.GetPropertyType(rdr["type"].ToString().ToLower());
                     col.IsNullable = rdr["notnull"].ToString() != "";
                     //col.IsAutoIncrement = false; //((int)rdr["IsIdentity"]) == 1;
                     col.IsPK = rdr["pk"].ToString() == "";
                     result.Add(col);
                 }
             }

             return result;
         }

         string Table_Filter =  " ";

         const string TABLE_SQL = " select name from sqlite_master where type = 'table' ";

         const string COLUMN_SQL = " PRAGMA table_info(@tableName) ";

         /// <summary>
         /// 查询
         /// </summary>
         /// <param name="sql">sql语句</param>
         /// <param name="slPars">参数</param>
         /// <returns>发挥SQLiteDataReader</returns>
         public SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] slPars)
         {
             SQLiteConnection conn = new SQLiteConnection(_connstr);
             using (SQLiteCommand cmd = new SQLiteCommand(sql,conn))
             {
                 if (slPars != null)
                 {
                     cmd.Parameters.AddRange(slPars);
                 }
                 try
                 {
                     conn.Open();
                     return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                 }
                 catch(Exception ex)
                 {
                     conn.Close();
                     conn.Dispose();
                     throw ex;
                 }

             }

         }

     }
 #>

SQLite配置文件  SQLiteInit.ttinclude

1.配置连接串  注意修改连接串为自己对应的  ConnectionString = @"Data Source=E:/cc/test/LotterySite/Lib/db/cater.db;";

2.需要生成的表  3.*命名空间   4.一个库对应一个配置文件  (开始是把这些配置直接写在各层模板文件的,后来涉及多库的切换,每个文件改链接麻烦)

 <#@ include file="./SQLiteSchemaReader.ttinclude" #>
 <#  // 初始化文件 一个库对应一个ttinclude文件
     // Settings  初始化配置
     ConnectionString = @"Data Source=E:/cc/test/LotterySite/Lib/db/cater.db;";  // 连接串
     TableFilter = " and name in ('LuckDraw') ";  // 过滤表
     TopNamespace = "FW";  // *命名空间
     ClassPrefix = "";
     ClassSuffix = "";
     IncludeViews = true;
     ExcludeTablePrefixes = new string[]{"aspnet_","webpages_"}; 

     // Read schema
     var tables = LoadTables();   //读取的所有表结构
 #>

---

SQLite 模板使用 

 <#@ template hostspecific="True" #>
 <#@ include file="EF.Utility.CS.ttinclude"#>
 <#@ include file="$(SolutionDir)\FW.Common\T4Ttinclude\SQLiteInit.ttinclude" #>
 <#@ assembly name="EnvDTE" #>
 <#@ assembly name="System.Data" #>
 <#@ assembly name="System.Data.Entity.Design" #>
 <#@ assembly name="System.Xml" #>
 <#@ assembly name="System.Configuration" #>
 <#@ assembly name="$(SolutionDir)\Lib\sqlite\System.Data.SQLite.dll" #> 

 <#@ import namespace="System.Collections.Generic" #>
 <#@ import namespace="System.Data" #>
 <#@ import namespace="System.Data.Common" #>
 <#@ import namespace="System.Diagnostics" #>
 <#@ import namespace="System.Globalization" #>
 <#@ import namespace="System.IO" #>
 <#@ import namespace="System.Linq" #>
 <#@ import namespace="System.Text" #>
 <#@ import namespace="System.Text.RegularExpressions" #>
 <#@ import namespace="System.Configuration" #>
 <#@ import namespace="System.Data.SQLite" #>
 <#@ output extension=".cst"#>  

 <#
     Namespace = TopNamespace + ".TestModel";
     // Read schema 

     EntityFrameworkTemplateFileManager fileManager = EntityFrameworkTemplateFileManager.Create(this); // 多文件生成
 /*
     // Tweak Schema
     tables["tablename"].Ignore = true;                            // To ignore a table
     tables["tablename"].ClassName = "newname";                    // To change the class name of a table
     tables["tablename"]["columnname"].Ignore = true;            // To ignore a column
     tables["tablename"]["columnname"].PropertyName="newname";    // To change the property name of a column
     tables["tablename"]["columnname"].PropertyType="bool";        // To change the property type of a column
 */
 #>

 <# fileManager.StartHeader(); #>

 using System;
 using Dapper;
 using Dapper.Contrib.Extensions;

 namespace <#=Namespace #>
 {

 <# fileManager.EndBlock(); #>

 <#
 foreach(Table tbl in from t in tables where !t.Ignore select t){
         if(IsExcluded(tbl.Name, ExcludeTablePrefixes)) continue;

         fileManager.StartNewFile(tbl.Name+".cs"); // 新建文件
 #>
     /// <summary>
     /// A class which represents the <#=tbl.Name#> <#=(tbl.IsView)?"view":"table"#>.
     /// </summary>
     [Table("[<#=tbl.Name#>]")]
     public partial class <#=tbl.ClassName#>
     {
        /* <#foreach(Column col in from c in tbl.Columns where !c.Ignore select c) {#> <#=col.PropertyName #> <#}#> */ 

 <#foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
 {#>
     <# if (tbl.PK!=null && tbl.PK.Name==col.PropertyName) { #>
     [Key]
     <#}#>
     public virtual <#=col.PropertyType #><#=CheckNullable(col)#> <#=col.PropertyName #> { get; set; }
 <#}#>
     }

 <#}#>

 <# fileManager.StartFooter(); #>
 } // namespace
 <# fileManager.EndBlock(); #>
 <# fileManager.Process(true); #>
  

模板属性：

Namespace 命名空间
tables 表结构集合

表属性 
Name 数据库中的表名
ClassName 实体类名称
IsView 是否是视图(没怎么用)
PK.Name 主键列名

列属性
PropertyName 属性名称
PropertyType 属性类型
CheckNullable(col) 可空类型判断

注意引入对应的SQLite的T4配置库   

<#@ include file="$(SolutionDir)\FW.Common\T4Ttinclude\SQLiteInit.ttinclude" #>

$(SolutionDir)         解决方案目录