在通过t4模版引擎之基础入门 对t4有了初步印象后，我们开始实战篇。t4模板引擎可以当做一个代码生成器，代码生成器的职责当然是用来生成代码(这不是废话吗)。而这其中我们使用的最普遍的是根据数据库生成实体类。 工欲善其事必先利其器，在这之前先来介绍一
在通过t4模版引擎之基础入门 对t4有了初步印象后，我们开始实战篇。t4模板引擎可以当做一个代码生成器，代码生成器的职责当然是用来生成代码(这不是废话吗)。而这其中我们使用的最普遍的是根据数据库生成实体类。
工欲善其事必先利其器，在这之前先来介绍一款t4编辑器t4 editor，我们可以点击链接去下载然后安装，不过还是推荐大家直接在vs扩展管理器里直接安装来的方便 工具->扩展管理器->联机库 搜索 t4 editor，选择第一项 tangible t4 editor 2.0 plus modeling tools for vs2010 进行安装即可，如下图所示:
安装上t4 editor后，编辑t4模板是就有代码着色和智能提示了，下图为安装t4 editor后的代码着色效果，怎么样是不是耳目一新，呵呵
接下来开始正式进入我们的主题，从数据库自动生成实体类
新建一个控制台项目，然后添加t4模板，这里我们起名字为customers.tt
修改输出文件扩展名为.cs
#@ output extension=.cs #>
添加常用的程序集和命名空间引用
#@ 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 #>
添加数据库操作dbhelper引用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 #>
#@ include file=$(projectdir)dbhelper.ttinclude #>
dbhelper相对比较复杂，把一些常用操作进行了简单封装，因此放到一个单独的文件里面进行引用，可以方便的进行复用，这里dbhelper的后缀名使用ttinclude，这里的后缀名可以随便起，按照微软的建议：用于include的文件尽量不要使用.tt做后缀名
在页面底部定义一些常用变量，以方便操作
public class config { public static readonly string connectionstring=data source=(local);integrated security=true;initial catalog=northwind;; public static readonly string dbdatabase=northwind; public static readonly string tablename=customers; }#>
这里我们把数据库连接串和数据库、表名字定义一下，方便修改和使用
最后来编写用于实体类生成的代码
//------------------------------------------------------------------------------// // 此代码由t4模板自动生成// 生成时间 by 懒惰的肥兔// 对此文件的更改可能会导致不正确的行为，并且如果// 重新生成代码，这些更改将会丢失。// //------------------------------------------------------------------------------using system;namespace t4consoleapplication.entities{ public class { foreach(dbcolumn column in dbhelper.getdbcolumns(config.connectionstring, config.dbdatabase, config.tablename)){#> /// /// /// public if(column.commontype.isvaluetype && column.isnullable){#>? { get; set; } }}
全部完成后我们的customers.tt文件就编写好了customers.tt
false hostspecific=false language=c# #>.cs #>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 懒惰的肥兔// 对此文件的更改可能会导致不正确的行为，并且如果// 重新生成代码，这些更改将会丢失。// //------------------------------------------------------------------------------using system;namespace t4consoleapplication.entities{ public class { 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);integrated security=true;initial catalog=northwind;; public static readonly string dbdatabase=northwind; public static readonly string tablename=customers; }#>
进行保存后会自动生成customers.cs文件
customers.cs
//------------------------------------------------------------------------------// // 此代码由t4模板自动生成// 生成时间 2012-07-18 17:51:26 by 懒惰的肥兔// 对此文件的更改可能会导致不正确的行为，并且如果// 重新生成代码，这些更改将会丢失。// //------------------------------------------------------------------------------using system;namespace t4consoleapplication.entities{ public class customers { /// /// /// public string customerid { get; set; } /// /// /// public string companyname { get; set; } /// /// /// public string contactname { get; set; } /// /// /// public string contacttitle { get; set; } /// /// /// public string address { get; set; } /// /// /// public string city { get; set; } /// /// /// public string region { get; set; } /// /// /// public string postalcode { get; set; } /// /// /// public string country { get; set; } /// /// /// public string phone { get; set; } /// /// /// public string fax { get; set; } }}
至此完整演示了怎样一步步根据数据库生成实体类的操作，是不是很简单，如对语法和操作不理解的地方可以参考t4模版引擎之基础入门，稍微用心研究下，轻松打造属于自己的代码生成器。
通过单个t4模板生成多个文件，以及自动生成整个数据库的所有实体类，敬请期待
下班了，拍拍屁股走人。。。
源码：t4consoleapplication.rar