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

最新推荐文章于 2024-07-06 07:36:03 发布

changuncle

最新推荐文章于 2024-07-06 07:36:03 发布

阅读量2.3k

点赞数 3

分类专栏： .Net 文章标签： sql server 编码 class T4

本文链接：https://blog.csdn.net/xiaouncle/article/details/79059600

版权

.Net 专栏收录该内容

60 篇文章 1 订阅

订阅专栏

学习了前面三章的内容现在我们可以利用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
    }
    ......
#>