T4系列文章之四：根据SqlServer生成实体类

学习了前面三章的内容现在我们可以利用T4模板做一些小工具了，今天这里要讲的就是利用T4模板生成实体类。在写这一部分的时候，我发现统一编码格式是一件麻烦事，因为T4代码里边有“<##>”这样的东西，试验了几种方法之后，我找到了一种稍微好点的，那就是把“<##>”也当做代码来看，然后缩进使用Tab就好。
生成实体类的代码还是稍微有点复杂的，具体内容我就不在这里一一讲解了，不过我这个生成方法可以指定要生成的数据库和表。
部分代码如下：

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ 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" #>
<#@ output extension=".cs" #>

using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Model
{
<# List<DbTable> tables=new List<DbTable>();
tables=TemplateHelper.GetDbTables(config.ConnectionString,config.DbDatabase,config.TableName);
foreach(DbTable dbTab in tables){ #>
public class <#=dbTab.TableName#>
 {
    <# List<DbColumn> dbcolumnList=new List<DbColumn>();
    dbcolumnList=TemplateHelper.GetDbColumns(config.ConnectionString, config.DbDatabase, dbTab.TableName);
foreach (DbColumn c in dbcolumnList){
if(c.IsNullable){#>
private <#= GetPrivateDbType(c.ColumnType) #> _<#= c.ColumnName.ToString().ToLower() #> = null ;
        <#}else{#>
private <#= GetPrivateDbType(c.ColumnType) #> _<#= c.ColumnName.ToString().ToLower() #> = null ;
        <#}
    }#>
public const string TableName = "<#=dbTab.TableName #>";
public const string TablePrimaryKey = "<# foreach (DbColumn c in dbcolumnList){
 if (c.IsPrimaryKey){#><#= c.ColumnName.ToString() #><# }
 }#>";
    <# foreach(DbColumn column in dbcolumnList){
if (column.IsPrimaryKey){ #>
/// <summary>
/// <#=column.Remark#>
/// </summary>
public <#= GetPrivateDbType(column.ColumnType)#> PrimaryKey
            {
                get{ return _<#= column.ColumnName.ToString().ToLower()#>; }
                set{ _<#= column.ColumnName.ToString().ToLower() #> = value; }
            }
public <#= GetPrivateDbType(column.ColumnType)#> <#= column.ColumnName #>
            {
                get{ return _<#= column.ColumnName.ToString().ToLower()#>; }
                set{ _<#= column.ColumnName.ToString().ToLower() #> = value; }
            }
        <#}else {#>
/// <summary>
/// <#= column.Remark == "" ? column.ColumnName : column.Remark #>
/// </summary>
public <#= GetPrivateDbType(column.ColumnType)#> <#= column.ColumnName #>
            {
                get{ return _<#= column.ColumnName.ToString().ToLower()#>; }
                set{ _<#= column.ColumnName.ToString().ToLower() #> = value; }
            }
        <#}
    }#> 
    }
<# } #>
}
<#+
public class config
 {
public static readonly string ConnectionString="Data Source=127.0.0.1;Initial Catalog=HBDX_PMS_20171122;User ID=sa;Password=sasa;";
//public static readonly string ConnectionString="Data Source=192.168.8.22;Initial Catalog=HBDX_PMS_20171122;User ID=sa;Password=xxh;";
public static readonly string DbDatabase="HBDX_PMS_20171122";
public static readonly string TableName="CTD_Project,CTD_CommencementReport,CTD_EngineeringSituation,CTD_ReturnWorkReport,CTD_StoppageReport";
    }
#>

还有一部分重要的东西放在include文件中，这里边主要包括了对数据库的一些操作，部分代码如下：

<#+
public class TemplateHelper
    {
#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} and obj.name!='sysdiagrams'
 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
    }
    ......
#>

示例程序下载地址如下（开发环境是VS2013）：
http://download.csdn.net/download/xiaouncle/10205427