T4模板配合Dapper生成Model层

以前见朋友开发程序用的是个C#版的Model生成器，里面就有Insert、Update、Delete、GetList这些方法，感觉挺方便，最近利用T4模板和Dapper自己也写了个。好东西要分享，顺便网友们给看看还有什么优化的地方。如果有什么好建议或者是不懂的地方可以联系我 QQ:1229145381  加好友请写备注：CSDN

一共是三个文件，添加的时候直接添加文本文件就行，然后把后缀名改成与图片对应的就行。Model是主文件，DbHelper.ttinclude和MultipleOutputHelper.ttinclude就像是html页和Jq库一样它俩都在Model.tt中引用。

下面上代码：

Model.tt

<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.xml" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>

<#@ include file="MultipleOutputHelper.ttinclude" #>

<#    
   
        string connectionString= "server=.;database=AdminSys;uid=sa;password=123456;";        
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
    
        string selectQuery ="SET FMTONLY ON; select * from @tableName; SET FMTONLY OFF;";
        SqlCommand command = new SqlCommand(selectQuery,conn);
        SqlDataAdapter ad = new SqlDataAdapter(command);
        System.Data.DataSet ds = new DataSet(); 
  
        var manager = Manager.Create(Host, GenerationEnvironment);  

        System.Data.DataTable schema = conn.GetSchema("Tables");
        foreach(System.Data.DataRow row in schema.Rows)
        {    
            ds.Tables.Clear();
            string tb_name= row["TABLE_NAME"].ToString();        
            command.CommandText = selectQuery.Replace("@tableName",row["TABLE_NAME"].ToString());
            ad.FillSchema(ds, SchemaType.Mapped,tb_name);  

            manager.StartNewFile(tb_name+".cs");#>

using System;
using System.Data;
using System.Linq;
using Dapper;
using System.Reflection;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;

namespace TL.Model
{    
    /// <summary>
    /// 实体-<#=tb_name#> 
    /// </summary>
    public partial class <#=tb_name#>    
    {    
        <#
        PushIndent("	");
        foreach (DataColumn dc in ds.Tables[0].Columns) 
        {
		//根据表名称和字段名称查出字段描述 comment
		 string sql = @"select d.value from  sys.syscolumns a left outer join  sys.extended_properties  d on a.id = d.major_id and a.colid = d.minor_id and d.name = 'MS_Description' where object_name(a.id) = '{0}' and a.name='{1}'  ";
         SqlCommand cmd = new SqlCommand(string.Format(sql, row["TABLE_NAME"],dc.ColumnName),conn);
         object comment= cmd.ExecuteScalar(); 
         WriteLine("\r\n    /// <summary>\r\n    ///"+comment+"\r\n    /// </summary>\r\n    public " + dc.DataType.Name+" " + dc.ColumnName + " { get; set; }");
		
        }
		WriteLine("\r\n    /// <summary>\r\n    ///实体类转字典集合\r\n    /// </summary>");
		WriteLine("    public List<Dictionary<string, object>> ToList()");
		WriteLine("    {");
		WriteLine("        PropertyInfo[] attrs = this.GetType().GetProperties();");
		WriteLine("        List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();");
		WriteLine("        foreach (var item in attrs)");
		WriteLine("        {");
		WriteLine("            list.Add(new Dictionary<string, object>() { { item.Name, item.GetValue(this, null) } });");
		WriteLine("        }");
		WriteLine("        return list;");
		WriteLine("    }");
        PopIndent();
        #>
    }
}
    
<#
        manager.EndBlock();    
         }            
        conn.Close();  
       manager.Process(true);  
        #>

DbHelper.ttinclude :

<#+
    public class DbHelper
    {
        #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