ASP.NET MVC5+EF6+EasyUI 后台管理系统（29）-T4模版

系列目录

本节不再适合本系统，在58，59节已经重构。请超过本节

这讲适合所有的MVC程序

很荣幸，我们的系统有了体验的地址了。演示地址

之前我们发布了一个简单的代码生成器，其原理就是读取数据库的表结构，生成文本的一个方式来生成代码！

为了替代重复的劳动，微软自己有一套T4模版，我不想把T4模版说得那么的复杂，因为这个复杂我自己也不知道。

原理跟市面的代码生成器一个道理，但是T4的扩展比代码生成器更灵活，可以更方便根据类生成代码等操作。

T4代码模式是没有颜色高亮了，但是我们的VS支持插件 T4 Editor

下载安装之后就可以跟我们写C#一样有智能高亮和提示了。

新建一个项目，专门用于T4的存放。

首先我们要让T4连接我们的数据库

新建一个DbHelper.ttinclude模版包含文件

<#+    public class DbHelper    {        #region GetDbTables                public static List<DbTable> GetDbTables(string connectionString, string database, string tables = null)        {                        if (!string.IsNullOrEmpty(tables))            {                tables = string.Format(" and obj.name in ('{0}')", tables.Replace(",", "','"));            }            #region SQL            string sql = string.Format(@"SELECT                                    obj.name tablename,                                    schem.name schemname,                                    idx.rows,                                    CAST                                    (                                        CASE                                             WHEN (SELECT COUNT(1) FROM sys.indexes WHERE object_id= obj.OBJECT_ID AND is_primary_key=1) >=1 THEN 1                                            ELSE 0                                        END                                     AS BIT) HasPrimaryKey                                                                             from {0}.sys.objects obj                                     inner join {0}.dbo.sysindexes idx on obj.object_id=idx.id and idx.indid<=1                                    INNER JOIN {0}.sys.schemas schem ON obj.schema_id=schem.schema_id                                    where type='U' {1}                                    order by obj.name", database, tables);            #endregion            DataTable dt = GetDataTable(connectionString, sql);            return dt.Rows.Cast<DataRow>().Select(row => new DbTable            {                TableName = row.Field<string>("tablename"),                SchemaName = row.Field<string>("schemname"),                Rows = row.Field<int>("rows"),                HasPrimaryKey = row.Field<bool>("HasPrimaryKey")            }).ToList();        }        #endregion        #region GetDbColumns                public static List<DbColumn> GetDbColumns(string connectionString, string database, string tableName, string schema = "dbo")        {            #region SQL            string sql = string.Format(@"                                    WITH indexCTE AS                                    (                                        SELECT                                         ic.column_id,                                        ic.index_column_id,                                        ic.object_id                                            FROM {0}.sys.indexes idx                                        INNER JOIN {0}.sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id                                        WHERE  idx.object_id =OBJECT_ID(@tableName) AND idx.is_primary_key=1                                    )                                    select                                    colm.column_id ColumnID,                                    CAST(CASE WHEN indexCTE.column_id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey,                                    colm.name ColumnName,                                    systype.name ColumnType,                                    colm.is_identity IsIdentity,                                    colm.is_nullable IsNullable,                                    cast(colm.max_length as int) ByteLength,                                    (                                        case                                             when systype.name='nvarchar' and colm.max_length>0 then colm.max_length/2                                             when systype.name='nchar' and colm.max_length>0 then colm.max_length/2                                            when systype.name='ntext' and colm.max_length>0 then colm.max_length/2                                             else colm.max_length                                        end                                    ) CharLength,                                    cast(colm.precision as int) Precision,                                    cast(colm.scale as int) Scale,                                    prop.value Remark                                    from {0}.sys.columns colm                                    inner join {0}.sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id                                    left join {0}.sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id                                    LEFT JOIN indexCTE ON colm.column_id=indexCTE.column_id AND colm.object_id=indexCTE.object_id                                                                            where colm.object_id=OBJECT_ID(@tableName)                                    order by colm.column_id", database);            #endregion            SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = string.Format("{0}.{1}.{2}", database, schema, tableName) };            DataTable dt = GetDataTable(connectionString, sql, param);            return dt.Rows.Cast<DataRow>().Select(row => new DbColumn()            {                ColumnID = row.Field<int>("ColumnID"),                IsPrimaryKey = row.Field<bool>("IsPrimaryKey"),                ColumnName = row.Field<string>("ColumnName"),                ColumnType = row.Field<string>("ColumnType"),                IsIdentity = row.Field<bool>("IsIdentity"),                IsNullable = row.Field<bool>("IsNullable"),                ByteLength = row.Field<int>("ByteLength"),                CharLength = row.Field<int>("CharLength"),                Scale = row.Field<int>("Scale"),                Remark = row["Remark"].ToString()            }).ToList();        }        #endregion             #region GetDataTable                public static DataTable GetDataTable(string connectionString, string commandText, params SqlParameter[] parms)        {            using (SqlConnection connection = new SqlConnection(connectionString))            {                SqlCommand command = connection.CreateCommand();                command.CommandText = commandText;                command.Parameters.AddRange(parms);                SqlDataAdapter adapter = new SqlDataAdapter(command);                DataTable dt = new DataTable();                adapter.Fill(dt);                return dt;            }        }        #endregion    }    #region DbTable    /// <summary>    /// 表结构    /// </summary>    public sealed class DbTable    {        /// <summary>        /// 表名称        /// </summary>        public string TableName { get; set; }        /// <summary>        /// 表的架构        /// </summary>        public string SchemaName { get; set; }        /// <summary>        /// 表的记录数        /// </summary>        public int Rows { get; set; }        /// <summary>        /// 是否含有主键        /// </summary>        public bool HasPrimaryKey { get; set; }    }    #endregion    #region DbColumn    /// <summary>    /// 表字段结构    /// </summary>    public sealed class DbColumn    {        /// <summary>        /// 字段ID        /// </summary>        public int ColumnID { get; set; }        /// <summary>        /// 是否主键        /// </summary>        public bool IsPrimaryKey { get; set; }        /// <summary>        /// 字段名称        /// </summary>        public string ColumnName { get; set; }        /// <summary>        /// 字段类型        /// </summary>        public string ColumnType { get; set; }        /// <summary>        /// 数据库类型对应的C#类型        /// </summary>        public string CSharpType        {            get            {                return SqlServerDbTypeMap.MapCsharpType(ColumnType);            }        }        /// <summary>        ///         /// </summary>        public Type CommonType        {            get            {                return SqlServerDbTypeMap.MapCommonType(ColumnType);            }        }        /// <summary>        /// 字节长度        /// </summary>        public int ByteLength { get; set; }        /// <summary>        /// 字符长度        /// </summary>        public int CharLength { get; set; }        /// <summary>        /// 小数位        /// </summary>        public int Scale { get; set; }        /// <summary>        /// 是否自增列        /// </summary>        public bool IsIdentity { get; set; }        /// <summary>        /// 是否允许空        /// </summary>        public bool IsNullable { get; set; }        /// <summary>        /// 描述        /// </summary>        public string Remark { get; set; }    }    #endregion    #region SqlServerDbTypeMap    public class SqlServerDbTypeMap    {        public static string MapCsharpType(string dbtype)        {            if (string.IsNullOrEmpty(dbtype)) return dbtype;            dbtype = dbtype.ToLower();            string csharpType = "object";            switch (dbtype)            {                case "bigint": csharpType = "long"; break;                case "binary": csharpType = "byte[]"; break;                case "bit": csharpType = "bool"; break;                case "char": csharpType = "string"; break;                case "date": csharpType = "DateTime"; break;                case "datetime": csharpType = "DateTime"; break;                case "datetime2": csharpType = "DateTime"; break;                case "datetimeoffset": csharpType = "DateTimeOffset"; break;                case "decimal": csharpType = "decimal"; break;                case "float": csharpType = "double"; break;                case "image": csharpType = "byte[]"; break;                case "int": csharpType = "int"; break;                case "money": csharpType = "decimal"; break;                case "nchar": csharpType = "string"; break;                case "ntext": csharpType = "string"; break;                case "numeric": csharpType = "decimal"; break;                case "nvarchar": csharpType = "string"; break;                case "real": csharpType = "Single"; break;                case "smalldatetime": csharpType = "DateTime"; break;                case "smallint": csharpType = "short"; break;                case "smallmoney": csharpType = "decimal"; break;                case "sql_variant": csharpType = "object"; break;                case "sysname": csharpType = "object"; break;                case "text": csharpType = "string"; break;                case "time": csharpType = "TimeSpan"; break;                case "timestamp": csharpType = "byte[]"; break;                case "tinyint": csharpType = "byte"; break;                case "uniqueidentifier": csharpType = "Guid"; break;                case "varbinary": csharpType = "byte[]"; break;                case "varchar": csharpType = "string"; break;                case "xml": csharpType = "string"; break;                default: csharpType = "object"; break;            }            return csharpType;        }                   public static Type MapCommonType(string dbtype)        {            if (string.IsNullOrEmpty(dbtype)) return Type.Missing.GetType();            dbtype = dbtype.ToLower();            Type commonType = typeof(object);            switch (dbtype)            {                case "bigint": commonType = typeof(long); break;                case "binary": commonType = typeof(byte[]); break;                case "bit": commonType = typeof(bool); break;                case "char": commonType = typeof(string); break;                case "date": commonType = typeof(DateTime); break;                case "datetime": commonType = typeof(DateTime); break;                case "datetime2": commonType = typeof(DateTime); break;                case "datetimeoffset": commonType = typeof(DateTimeOffset); break;                case "decimal": commonType = typeof(decimal); break;                case "float": commonType = typeof(double); break;                case "image": commonType = typeof(byte[]); break;                case "int": commonType = typeof(int); break;                case "money": commonType = typeof(decimal); break;                case "nchar": commonType = typeof(string); break;                case "ntext": commonType = typeof(string); break;                case "numeric": commonType = typeof(decimal); break;                case "nvarchar": commonType = typeof(string); break;                case "real": commonType = typeof(Single); break;                case "smalldatetime": commonType = typeof(DateTime); break;                case "smallint": commonType = typeof(short); break;                case "smallmoney": commonType = typeof(decimal); break;                case "sql_variant": commonType = typeof(object); break;                case "sysname": commonType = typeof(object); break;                case "text": commonType = typeof(string); break;                case "time": commonType = typeof(TimeSpan); break;                case "timestamp": commonType = typeof(byte[]); break;                case "tinyint": commonType = typeof(byte); break;                case "uniqueidentifier": commonType = typeof(Guid); break;                case "varbinary": commonType = typeof(byte[]); break;                case "varchar": commonType = typeof(string); break;                case "xml": commonType = typeof(string); break;                default: commonType = typeof(object); break;            }            return commonType;        }    }    #endregion    #>

DbHelper.ttinclude

这里面包含了一些转换的方法和访问数据库的SQL语句

其中GetDbTables方法就是根据数据库的链接，表名来读取字段数据

怎么用呢？就是让模版包含这个文件，就可以访问方法了

举一个Model为例吧，新建Model文件夹。并新建文件，模版文件为tt结尾的扩展

我们新建Test.tt之后

<#@ template debug="false" hostspecific="false" language="C#" #><#@ assembly name="System.Core" #><#@ import namespace="System.Linq" #><#@ import namespace="System.Text" #><#@ import namespace="System.Collections.Generic" #><#@ output extension=".txt" #>

最后一行<#@ output extension=".txt" #>

为模版生成的文件，可以是.cs可以是其他。我们就不改了，就是txt

如果是cs类，生成会编译，如果代码是对的，但是系统不知道是对的，会编译出错，虽然这不影响结果，但是不爽

我们为Test.tt添加如下代码

<#@ template debug="false" hostspecific="false" language="C#" #><#@ output extension=".txt" #><#@ assembly name="System.Core.dll" #><#@ assembly name="System.Data.dll" #><#@ assembly name="System.Data.DataSetExtensions.dll" #><#@ assembly name="System.Xml.dll" #><#@ import namespace="System" #><#@ import namespace="System.Xml" #><#@ import namespace="System.Linq" #><#@ import namespace="System.Data" #><#@ import namespace="System.Data.SqlClient" #><#@ import namespace="System.Collections.Generic" #><#@ import namespace="System.IO" #><#@ include file="$(ProjectDir)DbHelper.ttinclude"  #>//------------------------------------------------------------------------------// <auto-generated>//     此代码由T4模板自动生成//       生成时间 <#=DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")#> by YmNets//     对此文件的更改可能会导致不正确的行为，并且如果//     重新生成代码，这些更改将会丢失。// </auto-generated>//------------------------------------------------------------------------------

我们引入了一些命名空间，因为我们要将读到的表结构进行一系列的处理，比如for循环等操作

其中<#@ include file="$(ProjectDir)DbHelper.ttinclude"  #>就是包含了刚刚简历的访问数据库文件

T4模版的语法通过上面我们可以看出是<#@#>,<#=#>,<##>,<#+#>结构跟原生的asp.net一个鸟样，不是Razor语法，这里要注意

我们添加一些代码

<#@ template debug="false" hostspecific="false" language="C#" #><#@ output extension=".txt" #><#@ assembly name="System.Core.dll" #><#@ assembly name="System.Data.dll" #><#@ assembly name="System.Data.DataSetExtensions.dll" #><#@ assembly name="System.Xml.dll" #><#@ import namespace="System" #><#@ import namespace="System.Xml" #><#@ import namespace="System.Linq" #><#@ import namespace="System.Data" #><#@ import namespace="System.Data.SqlClient" #><#@ import namespace="System.Collections.Generic" #><#@ import namespace="System.IO" #><#@ include file="$(ProjectDir)DbHelper.ttinclude"  #>//------------------------------------------------------------------------------// <auto-generated>//     此代码由T4模板自动生成//       生成时间 <#=DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")#> by YmNets//     对此文件的更改可能会导致不正确的行为，并且如果//     重新生成代码，这些更改将会丢失。// </auto-generated>//------------------------------------------------------------------------------using System;using System.ComponentModel.DataAnnotations;namespace <#=config.nameSpace#>{        public class <#=config.TableName#>Model    {        <# foreach(DbColumn column in DbHelper.GetDbColumns(config.ConnectionString, config.DbDatabase, config.TableName)){#>        public <#= column.CSharpType#><# if(column.CommonType.IsValueType && column.IsNullable){#>?<#}#> <#=column.ColumnName#> { get; set; }        <#}#>     }}<#+    public class config    {        public static readonly string ConnectionString="Data Source=(local);Initial Catalog=AppDB;User ID=sa;Password=zhaoyun123!@#;";        public static readonly string DbDatabase="AppDB";        public static readonly string TableName="SysSample";        public static readonly string nameSpace="App.Sys";    }#>

有一些通用的我建了一个类config，访问config只需要config.TableName即可

访问DbHelper.ttinclude，值需要DbHelper.***即可

所以同道理，通用的类我们可以建造 Common.ttinclude来完善模版生成库

OK。配置好之后，值需要展开Test.tt就可以看到Test.txt文件，文件生成情况如下

//------------------------------------------------------------------------------// <auto-generated>//     此代码由T4模板自动生成//       生成时间 2014-03-03 17:34:45 by YmNets//     对此文件的更改可能会导致不正确的行为，并且如果//     重新生成代码，这些更改将会丢失。// </auto-generated>//------------------------------------------------------------------------------using System;using System.ComponentModel.DataAnnotations;namespace App.Sys{        public class SysSampleModel    {                public string Id { get; set; }                public string Name { get; set; }                public int? Age { get; set; }                public DateTime? Bir { get; set; }                public string Photo { get; set; }                public string Note { get; set; }                public DateTime? CreateTime { get; set; }             }}

非常快速，所以，你可以很快建立自己项目的模版文件了。

以上只是基础，我们更加关心的是更多的扩展，比如判断数据库类型啊等等，做逻辑处理，获取字段的长度做处理啊。

所以我给大家提供一个备注：下面这条语句可以得到表的结构

 <# foreach(DbColumn column in DbHelper.GetDbColumns(config.ConnectionString, config.DbDatabase, config.TableName)){#>       <#}#> 

其中：

column.CSharpType              字段的类型

column.IsNullable                  是否为空

column.IsPrimaryKey             是否是主键

column.CharLength               字段的长度，如varchar(50)那么column.CharLength=50

column.ColumnName             字段的名称

样例：判断是否DataTime类型

<#if(column.CSharpType=="DateTime"){#><# } #>

一个小小的例子，我们就能看懂MVC的T4模版，并能熟练运用它