在通过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.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 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.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
///
/// 表结构
///
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
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did159160