标签：style blog class c code java
原文:构建ASP.NET
MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统（29）-T4模版
这讲适合所有的MVC程序
很荣幸，我们的系统有了体验的地址了。演示地址
之前我们发布了一个简单的代码生成器，其原理就是读取数据库的表结构，生成文本的一个方式来生成代码！
为了替代重复的劳动，微软自己有一套T4模版，我不想把T4模版说得那么的复杂，因为这个复杂我自己也不知道。
原理跟市面的代码生成器一个道理，但是T4的扩展比代码生成器更灵活，可以更方便根据类生成代码等操作。
T4代码模式是没有颜色高亮了，但是我们的VS支持插件 T4
Editor
下载安装之后就可以跟我们写C#一样有智能高亮和提示了。
新建一个项目，专门用于T4的存放。
首先我们要让T4连接我们的数据库
新建一个DbHelper.ttinclude模版包含文件
public class DbHelper
{
#region GetDbTables
public static List 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", database, tables);
#endregion
DataTable dt = GetDataTable(connectionString, sql);
return dt.Rows.Cast().Select(row => new DbTable
{
TableName = row.Fieldstring>("tablename"),
SchemaName = row.Fieldstring>("schemname"),
Rows = row.Fieldint>("rows"),
HasPrimaryKey = row.Fieldbool>("HasPrimaryKey")
}).ToList();
}
#endregion
#region GetDbColumns
public static List 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().Select(row => new DbColumn()
{
ColumnID = row.Fieldint>("ColumnID"),
IsPrimaryKey = row.Fieldbool>("IsPrimaryKey"),
ColumnName = row.Fieldstring>("ColumnName"),
ColumnType = row.Fieldstring>("ColumnType"),
IsIdentity = row.Fieldbool>("IsIdentity"),
IsNullable = row.Fieldbool>("IsNullable"),
ByteLength = row.Fieldint>("ByteLength"),
CharLength = row.Fieldint>("CharLength"),
Scale = row.Fieldint>("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
///
/// 表结构
///
public sealed class DbTable
{
///
/// 表名称
///
public string TableName { get; set; }
///
/// 表的架构
///
public string SchemaName { get; set; }
///
/// 表的记录数
///
public int Rows { get; set; }
///
/// 是否含有主键
///
public bool HasPrimaryKey { get; set; }
}
#endregion
#region DbColumn
///
/// 表字段结构
///
public sealed class DbColumn
{
///
/// 字段ID
///
public int ColumnID { get; set; }
///
/// 是否主键
///
public bool IsPrimaryKey { get; set; }
///
/// 字段名称
///
public string ColumnName { get; set; }
///
/// 字段类型
///
public string ColumnType { get; set; }
///
/// 数据库类型对应的C#类型
///
public string CSharpType
{
get
{
return SqlServerDbTypeMap.MapCsharpType(ColumnType);
}
}
///
///
///
public Type CommonType
{
get
{
return SqlServerDbTypeMap.MapCommonType(ColumnType);
}
}
///
/// 字节长度
///
public int ByteLength { get; set; }
///
/// 字符长度
///
public int CharLength { get; set; }
///
/// 小数位
///
public int Scale { get; set; }
///
/// 是否自增列
///
public bool IsIdentity { get; set; }
///
/// 是否允许空
///
public bool IsNullable { get; set; }
///
/// 描述
///
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
#>
这里面包含了一些转换的方法和访问数据库的SQL语句
其中GetDbTables方法就是根据数据库的链接，表名来读取字段数据
怎么用呢？就是让模版包含这个文件，就可以访问方法了
举一个Model为例吧，新建Model文件夹。并新建文件，模版文件为tt结尾的扩展
我们新建Test.tt之后
"false" hostspecific="false" language="C#" #>
"System.Core" #>
namespace="System.Linq" #>
namespace="System.Text" #>
namespace="System.Collections.Generic" #>
".txt" #>
最后一行
为模版生成的文件，可以是.cs可以是其他。我们就不改了，就是txt
如果是cs类，生成会编译，如果代码是对的，但是系统不知道是对的，会编译出错，虽然这不影响结果，但是不爽
我们为Test.tt添加如下代码
"false" hostspecific="false" language="C#" #>
".txt" #>
"System.Core.dll" #>
"System.Data.dll" #>
"System.Data.DataSetExtensions.dll" #>
"System.Xml.dll" #>
namespace="System" #>
namespace="System.Xml" #>
namespace="System.Linq" #>
namespace="System.Data" #>
namespace="System.Data.SqlClient" #>
namespace="System.Collections.Generic" #>
namespace="System.IO" #>
"$(ProjectDir)DbHelper.ttinclude" #>
//------------------------------------------------------------------------------
//
// 此代码由T4模板自动生成
// 生成时间 by YmNets
// 对此文件的更改可能会导致不正确的行为，并且如果
// 重新生成代码，这些更改将会丢失。
//
//------------------------------------------------------------------------------
我们引入了一些命名空间，因为我们要将读到的表结构进行一系列的处理，比如for循环等操作
其中就是包含了刚刚简历的访问数据库文件
T4模版的语法通过上面我们可以看出是,,,结构跟原生的asp.net一个鸟样，不是Razor语法，这里要注意
我们添加一些代码
"false" hostspecific="false" language="C#" #>
".txt" #>
"System.Core.dll" #>
"System.Data.dll" #>
"System.Data.DataSetExtensions.dll" #>
"System.Xml.dll" #>
namespace="System" #>
namespace="System.Xml" #>
namespace="System.Linq" #>
namespace="System.Data" #>
namespace="System.Data.SqlClient" #>
namespace="System.Collections.Generic" #>
namespace="System.IO" #>
"$(ProjectDir)DbHelper.ttinclude" #>
//------------------------------------------------------------------------------
//
// 此代码由T4模板自动生成
// 生成时间 by YmNets
// 对此文件的更改可能会导致不正确的行为，并且如果
// 重新生成代码，这些更改将会丢失。
//
//------------------------------------------------------------------------------
using System;
using System.ComponentModel.DataAnnotations;
namespace
{
public class Model
{
foreach(DbColumn column in DbHelper.GetDbColumns(config.ConnectionString, config.DbDatabase, config.TableName)){#>
public if(column.CommonType.IsValueType && column.IsNullable){#>? { 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文件，文件生成情况如下
//------------------------------------------------------------------------------
//
// 此代码由T4模板自动生成
// 生成时间 2014-03-03 17:34:45 by YmNets
// 对此文件的更改可能会导致不正确的行为，并且如果
// 重新生成代码，这些更改将会丢失。
//
//------------------------------------------------------------------------------
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模版，并能熟练运用它
构建ASP.NET MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统（29）-T4模版,搜素材,soscw.com
构建ASP.NET MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统（29）-T4模版
标签：style blog class c code java
原文地址：http://www.cnblogs.com/lonelyxmas/p/3738871.html