工欲善其事，必先利其器：分享一套Code Smith 搭建N层架构模板

开篇

平常开发时，由于冗余代码过多，程序员做重复的工作过多势必会影响开发效率。倘若对重复性代码简单的复制、粘贴，虽然也能节省时间，但也需仔细一步步替换，这无疑也是一件费力的事。这时我们急需代码生成工具，根据一套Template快速生成我们需要的代码。代码生成器原理简单，完全可以开发一套适合自己的代码生成器，一个最简单的代码生成器，有几点你需要关注下：

查询系统视图：INFORMATION_SCHEMA.TABLES、 INFORMATION_SCHEMA.COLUMNS 可以获得数据库中表、列的相关信息。

字符串的拼接：StringBuilder的使用，其AppendLine()自动换行。

将字符串写入文本文件：File.WriteAllText()

使用了部分类（partial）

使用可空类型：由于数据库中表中数据很有可能是NULL，可空类型使得数据从表中读取出来赋值给值类型更加兼容。

当然自己开发的代码生成器局限性很大，但对于小项目也是很好的选择。我也写过两篇代码生成器的拙文，仅供参考。

简单代码生成器原理剖析（一）

简单代码生成器原理剖析（二）

说起代码生成器，不得不说Code Smith,基于Template的编程，下面举例的NTier架构是很基础的，除了熟悉的三层架构，还生成了抽象工厂、缓存、单例、反射、存储过程等，当然这个Demo只是学习用，大家可以继续扩展，打造自己的铜墙铁壁。

Code Smith

CodeSmith 是一种语法类似于asp.net的基于模板的代码生成器，程序可以自定义模板，从而减少重复编码的劳动量，提高效率。Code Smith提供自定义Template，语法也不复杂，类似于asp.net的标识符号，<%%>、<%=%>、<script runat="template">...</script>

Code Smith API

N层架构-实体类模板-Entity Template

首先创建一个C# template，创建指令集，导入程序集和名称空间：

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %>

添加属性Property：

<%--DataSourse--%>
<%@ Property Name="CurrentTable" Type="SchemaExplorer.TableSchema" Default="" Optional="False" DeepLoad="True" Category="" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %>

<%@ Property Name="RootNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>

一个简单的实体类模板内容，可以这样写：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace <%=this.RootNamespace%>.Entity
{
    public class <%= CurrentTable.Name%>
    {
        <% foreach(ColumnSchema col in CurrentTable.Columns)
        {%>
            public <%=col.DataType%> <%=col.Name%> {get;set;}
        <%}%>
            
    }
}

当然生成实体类时，需要考虑可空类型，写好一个Template,以后爽歪歪:)。

N层架构-数据访问层接口模板，IDao Template

Script标签里可以自定义调用的方法，属性等。数据访问层接口大家肯定烂熟于心。
常用的CRUD方法以及主表找子表，子表找主表。
Script里面的方法，你需要熟悉一下Code Smith的API，我在上面已经贴出了常用的API，供大家参考。

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %> 

<%-- 1. Datasource --%>
<%@ Property Name="CurrentTable" Type="SchemaExplorer.TableSchema" DeepLoad="True" Optional="False"  %>

<%@ Property Name="RootNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using <%=this.RootNamespace%>.Model;

namespace <%=this.RootNamespace%>.IDao
{
    public interface I<%= this.CurrentTable.Name %>Dao
    {
         int Add(Model.<%= this.CurrentTable.Name %> entity);
         int Update(Model.<%= this.CurrentTable.Name %> entity);
         int Delete(Model.<%= this.CurrentTable.Name %> entity); 
         List<Model.<%= this.CurrentTable.Name %>> LoadAllEntities();
         Model.<%= this.CurrentTable.Name %> LoadByPK(<%= PKArgsForTable()%>);
 
    
    #region 主表找子表
    
    <% foreach(TableKeySchema fk in this.CurrentTable.ForeignKeys)
    {
    %>
        List<Model.<%=this.CurrentTable.Name%>> Query_<%=this.CurrentTable.Name%>List_By<%=fk.Name%>(<%=this.FKArgsForTable(fk)%>);
    <%}%>
    #endregion
    }
}





<script runat="template">

    
    //方法:生成列的主键列构成的参数列表
    public string PKArgsForTable()
    {
        string args="";
        foreach(MemberColumnSchema pkCol in this.CurrentTable.PrimaryKey.MemberColumns)
        {
                args += string.Format("{0} {1},",pkCol.Column.DataType,pkCol.Column.Name);  
        }
                   
         return args.Substring(0,args.Length-1);
    }
    
    //主键在子表中的外键所包含的列的参数列表带数据类型
    public string FKArgsForTable(TableKeySchema key){
      
         string args=""; 
        foreach(MemberColumnSchema Col in key.ForeignKeyMemberColumns)
        {
                args += string.Format("{1} {0},",Col.Column.Name,Col.DataType);
        }
                   
         return args.Substring(0,args.Length-1);
        }
</script>

N层架构-抽象工厂-AbstactFactory Template

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %> 

<%-- 1. Datasource --%>

<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" DeepLoad="True" Optional="False"%>

<%@ Property Name="RootNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace <%=this.RootNamespace%>.AbstractFactory
{
    public abstract class DaoFactory
    {
<% foreach(TableSchema table in this.SourceDatabase.Tables)
{
    %>
    
        public abstract IDao.I<% = table.Name %>Dao <% = table.Name %>SqlProviderDao
        {
            get;
        }
    
    <%
    }
    %>

    }
}

N层架构-DaoSqlFactory Template

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %> 

<%-- 1. Datasource --%>

<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" DeepLoad="True" Optional="False"%>

<%@ Property Name="RootNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace <%=this.RootNamespace%>.SqlProviderDao
{
    public class DaoSqlFactory : <%=this.RootNamespace%>.AbstractFactory.DaoFactory
    {
    
<% foreach(TableSchema table in this.SourceDatabase.Tables)
{
    %>
     
        public override <%=this.RootNamespace%>.IDao.I<% = table.Name %>Dao <% = table.Name %>SqlProviderDao
        {
            get
            {

                //TODO:效率考虑,可考虑使用缓存

                SqlProviderDao.<% = table.Name %>SqlProviderDao obj = System.Web.HttpContext.Current.Cache.Get("<%=this.RootNamespace%>.IDao.I<% = table.Name %>Dao") as SqlProviderDao.<% = table.Name %>SqlProviderDao;
                if (obj == null)
                {
                    var instance = new SqlProviderDao.<% = table.Name %>SqlProviderDao();

                    System.Web.HttpContext.Current.Cache.Add("<%=this.RootNamespace%>.IDao.I<% = table.Name %>Dao",instance , null, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, null);

                  //System.Web.HttpRuntime.Cache.Insert(
                    return instance;
                }

                return obj;



            }
        }
        
    <%
    }
    %>

   }
}

N层架构-数据访问层-SqlProviderDao Template

数据访问层(DAAB)可以使用微软企业级框架 Microsoft Enterprise Library

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %> 

<%-- 1. Datasource --%>
<%@ Property Name="CurrentTable" Type="SchemaExplorer.TableSchema" DeepLoad="True" Optional="False"  %>

<%@ Property Name="RootNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;
using <%=this.RootNamespace%>.Model;

namespace <%=this.RootNamespace%>.SqlProviderDao
{
    /// <summary>
    /// 对*<%= this.CurrentTable.Name %>*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class <%= this.CurrentTable.Name %>SqlProviderDao:IDao.I<%= this.CurrentTable.Name %>Dao
    { 

 
        public int Add(Model.<%= this.CurrentTable.Name %> entity)
        {
            //通过DataBaseFactory获得当前数据库连接对象
             Database db=DatabaseFactory.CreateDatabase();
             SqlCommand cmd=db.GetStoredProcCommand("usp_<%=this.CurrentTable.Name%>_Insert") as SqlCommand;
            //cmd参数赋值
            <% foreach(ColumnSchema col in this.CurrentTable.Columns)
            {
            %>
                  cmd.Parameters.AddWithValue("@<%=col.Name%>",entity.<%=col.Name%>);
            <%}%>
             return db.ExecuteNonQuery(cmd);
            
        }

        public int Update(Model.<%= this.CurrentTable.Name %> entity)
        {
             //通过DataBaseFactory获得当前数据库连接对象
             Database db=DatabaseFactory.CreateDatabase();
             SqlCommand cmd=db.GetStoredProcCommand("usp_<%=this.CurrentTable.Name%>_Update") as SqlCommand;
            //cmd参数赋值
            <% foreach(ColumnSchema col in this.CurrentTable.Columns)
            {
            %>
                  cmd.Parameters.AddWithValue("@<%=col.Name%>",entity.<%=col.Name%>);
            <%}%>
             return db.ExecuteNonQuery(cmd);
        }

        public int Delete(Model.<%= this.CurrentTable.Name %> entity)
        {
           //根据主键进行删除数据，需要考虑出现复合主键
           //通过DataBaseFactory获得当前数据库连接对象
             Database db=DatabaseFactory.CreateDatabase();
             SqlCommand cmd=db.GetStoredProcCommand("usp_<%=this.CurrentTable.Name%>_Delete") as SqlCommand;
            //cmd参数赋值
            <% foreach(MemberColumnSchema pkCol in this.CurrentTable.PrimaryKey.MemberColumns)
            {
            %>
                  cmd.Parameters.AddWithValue("@<%=pkCol.Name%>",entity.<%=pkCol.Name%>);
            <%}%>
             return db.ExecuteNonQuery(cmd);
        
        }

        public List<Model.<%= this.CurrentTable.Name %>> LoadAllEntities()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            SqlCommand cmd = db.GetStoredProcCommand("usp_<% = this.CurrentTable.Name %>_LoadAll") as SqlCommand;

            //3.通过db执行一个DBReader

           SqlDataReader reader =  db.ExecuteReader(cmd) as SqlDataReader;
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<Model.<%= this.CurrentTable.Name %>> entitiesList=new  List<Model.<%= this.CurrentTable.Name %>>();
            while(reader.Read())
            {
                entitiesList.Add(new Model.<%=this.CurrentTable.Name%>(){
                    <%=this.SetValueToProperty %>
                });
            }
            return entitiesList;
        }

        public Model.<%= this.CurrentTable.Name %> LoadByPK(<%= PKArgsForTable()%>)
        {
           
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            SqlCommand cmd = db.GetStoredProcCommand("usp_<% = this.CurrentTable.Name %>_LoadByPK") as SqlCommand;
            
            <%foreach(MemberColumnSchema pkCol in this.CurrentTable.PrimaryKey.MemberColumns)
            {%>
                  cmd.Parameters.AddWithValue("@<% = pkCol.Name%>", <% = pkCol.Name%>);
            <%}%>

            //3.通过db执行一个DBReader

            SqlDataReader reader =  db.ExecuteReader(cmd) as SqlDataReader;
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            Model.<%=this.CurrentTable.Name%> one<%=this.CurrentTable.Name%>=null;
            if(reader.Read())
            {
                one<%=this.CurrentTable.Name%>=new <%=this.CurrentTable.Name%>(){
                    <%=this.SetValueToProperty %>
                };
            }
            return one<%=this.CurrentTable.Name%>;
        }
         #region ,当前表是子表,主表找子表
    
    <% foreach(TableKeySchema fk in this.CurrentTable.ForeignKeys)
    {
    %>
        public List<Model.<%=this.CurrentTable.Name%>> Query_<%=this.CurrentTable.Name%>List_By<%=fk.Name%>(<%=this.FKArgsForTable(fk)%>)
        {
              Database db = DatabaseFactory.CreateDatabase();
              SqlCommand cmd = db.GetStoredProcCommand("usp_<% = this.CurrentTable.Name %>_LoadBy_<%= fk.Name%>") as SqlCommand;
              //赋值：传递一个ForeignKey
              <%=this.SetPropertyValueByFK(fk)%>
            
             SqlDataReader reader =  db.ExecuteReader(cmd) as SqlDataReader;
             List<Model.<%= this.CurrentTable.Name %>> entitiesList=new  List<Model.<%= this.CurrentTable.Name %>>();
            while(reader.Read())
            {
                entitiesList.Add(new Model.<%=this.CurrentTable.Name%>(){
                    <%=this.SetValueToProperty %>
                });
            }
            return entitiesList;
            
            
        }
    <%}%>
    #endregion
     
    }
}
  



<script runat="template">

   private string SetPropertyValueByFK(TableKeySchema fk)
    {
        StringBuilder sb=new StringBuilder();
        foreach(MemberColumnSchema fkCol in fk.ForeignKeyMemberColumns)
        {
                sb.AppendFormat("cmd.Parameters.AddWithValue(\"@{0}\",{0});\r\n",fkCol.Column.Name);
        }
        return sb.ToString();
    }

    private string SetValueToProperty
    {
      get
        {
            string args="";
            foreach(ColumnSchema col in this.CurrentTable.Columns)
            {
                    args+=string.Format("{0}=({2})reader[\"{1}\"],",col.Name,col.Name,col.DataType);
            }
            return args.Substring(0,args.Length-1);
        }
        
    }

    //方法:生成列的主键列构成的参数列表
    public string PKArgsForTable()
    {
        string args="";
        foreach(MemberColumnSchema pkCol in this.CurrentTable.PrimaryKey.MemberColumns)
        {
                args += string.Format("{0} {1},",pkCol.Column.DataType,pkCol.Column.Name);  
        }
                   
         return args.Substring(0,args.Length-1);
    }
    
    //主键在子表中的外键所包含的列的参数列表带数据类型
    public string FKArgsForTable(TableKeySchema key){
      
         string args=""; 
        foreach(MemberColumnSchema Col in key.ForeignKeyMemberColumns)
        {
                args += string.Format("{1} {0},",Col.Column.Name,Col.DataType);
        }
                   
         return args.Substring(0,args.Length-1);
        }
</script>

N层架构-SP存储过程-ScriptSp Template

创建存储过程之前首先需要判读存储过程是否存在

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %> 

<%-- 1. Datasource --%>
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" DeepLoad="True" Optional="False"  %>

--检查是否存在

use <%=this.SourceDatabase%>
go

<% foreach(TableSchema table in this.SourceDatabase.Tables){
%>
        
            if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_<% = table.Name %>_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
            drop procedure [dbo].[usp_<% = table.Name %>_Insert]
            GO
            
            if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_<% = table.Name %>_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
            drop procedure [dbo].[usp_<% = table.Name %>_Update]
            GO
            
            if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_<% = table.Name %>_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
            drop procedure [dbo].[usp_<% = table.Name %>_Delete]
            GO
            
            if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_<% = table.Name %>_LoadAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
            drop procedure [dbo].[usp_<% = table.Name %>_LoadAll]
            GO
            
            if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_<% = table.Name %>_LoadByPK]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
            drop procedure [dbo].[usp_<% = table.Name %>_LoadByPK]
            GO
            
            
            ------------------
              <%   foreach(TableKeySchema pk in table.ForeignKeys){
                            %>
                         if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_<%= table.Name %>_LoadBy_<%= pk.Name%>]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
            drop procedure [dbo].[usp_<%= table.Name %>_LoadBy_<%= pk.Name%>]
            GO      
                         
                            <%
                            }
                            %>
            
            
            
            
<%
}%>




--生成存储过程
<%foreach(TableSchema table in SourceDatabase.Tables)
{%>
    --当前表示:<%=table.Name%>
    create proc usp_<%=table.Name%>_Insert
    (
       <%=this.CreateAllColumnsArgs(table)%>	
    )
as
insert into <%=table.Name%>(<%=this.CreateAllColumnsWithoutNativeType(table)%>) values(<%=this.CreateAllColumnsArgsWithoutNativeType(table)%>);

go
--------
create proc usp_<%=table.Name%>_LoadAll
as
select <%=this.CreateAllColumnsWithoutNativeType(table)%> from <%=table.Name%> 
go
--------
create proc usp_<%=table.Name%>_Delete
(
    <%=this.CreatePKColumnsArgs(table)%>
)
as
Delete from <%=table.Name%>
where
<%=this.CreatePKColumnsArgsWithoutNativeTypeAppendToWhere(table)%>;
go
--------

create proc usp_<%=table.Name%>_Update
(
	<%=this.CreateAllColumnsArgs(table)%>	
)
as
update <%=table.Name%> set <%=this.CreateAllColumnsArgsWithoutPK(table)%> where
<%=this.CreatePKColumnsArgsWithoutNativeTypeAppendToWhere(table)%>;

go
---------------
create proc usp_<%=table.Name%>_LoadByPK
(
     <%=this.CreatePKColumnsArgs(table)%>
)
as
select <%=this.CreateAllColumnsWithoutNativeType(table)%>
from
<%=table.Name%>
where
<%=this.CreatePKColumnsArgsWithoutNativeTypeAppendToWhere(table)%>

go
----------主外键关系,类的单一职责

    <%foreach(TableKeySchema fk in table.ForeignKeys)
    {%>
        create proc  usp_<%=table.Name%>_LoadBy_<%=fk.Name%> 
        (
            <%=this.CreateFK(fk)%>
        )
        as
        select  <%=this.CreateAllColumnsWithoutNativeType(table)%> from <%=table.Name%> 
        where <%=this.CreateFKSetValues(fk,table)%>
        go
        
        
    <%}%>


<%}%>
<script runat="template">
    //生成所有列的参数列表
    private string CreateAllColumnsArgs(TableSchema table)
    {
        string args="";
        foreach(ColumnSchema col in table.Columns)
        {
           if(col.NativeType=="varchar"|| col.NativeType=="nvarchar"||col.NativeType=="char"||col.NativeType=="nchar")
            {
                args+=string.Format("@{0} {1}({2}),",col.Name,col.NativeType,col.Size);            
            }
            else
            {
                args+=string.Format("@{0} {1},",col.Name,col.NativeType);
                
            }
        }
        return args.Substring(0,args.Length-1);
    }
    //生成所有列的列表，不带参数类型
    private string CreateAllColumnsWithoutNativeType(TableSchema table)
    {
        string args="";
        foreach(ColumnSchema col in table.Columns)
        {
                args+=string.Format("{0},",col.Name);
        
        }
        return args.Substring(0,args.Length-1);
    }
    //生成所有列的参数列表，不带参数类型
    private string CreateAllColumnsArgsWithoutNativeType(TableSchema table)
    {
        string args="";
        foreach(ColumnSchema col in table.Columns)
        {
                args+=string.Format("@{0},",col.Name);
        
        }
        return args.Substring(0,args.Length-1);        
    
    }
    
    //生成PK的参数列表
    private string CreatePKColumnsArgs(TableSchema table)
    {
        string args="";
        foreach(ColumnSchema col in table.Columns)
        {
            if(col.IsPrimaryKeyMember)
            {
                if(col.NativeType=="varchar"||col.NativeType=="nvarchar"||col.NativeType=="char"||col.NativeType=="nchar")
                {
                  args+=string.Format("@{0} {1}({2}),",col.Name,col.NativeType,col.Size);      
                }
                else
                {
                    args+=string.Format("@{0} {1},",col.Name,col.NativeType);    
                }
            }
        }
        return args.Substring(0,args.Length-1);
    }
    //生成主键列不带参数类型Where ID=@ID and...
    private string CreatePKColumnsArgsWithoutNativeTypeAppendToWhere(TableSchema table)
    {
        string args="";
        for(int i=0;i<table.PrimaryKey.MemberColumns.Count;i++)
        {
            args+=string.Format("{0}=@{0}",table.PrimaryKey.MemberColumns[i].Column.Name);
            if(i<table.PrimaryKey.MemberColumns.Count-1)
            {
                args +=string.Format(" and");   
            }
        }
        return args;
        
    }
    //非主键类型参数列表的赋值语句
    private string CreateAllColumnsArgsWithoutPK(TableSchema table)
    {
        string args="";
        foreach(ColumnSchema col in table.NonPrimaryKeyColumns)
        {
                args=string.Format("{0}=@{0},",col.Name);
        }
        if(table.NonPrimaryKeyColumns.Count==0)
        {
                return "";
        }
        return args.Substring(0,args.Length-1);
    }
    
    //主键在子表的引用外键所包含的列
    private string CreateFK(TableKeySchema fk)
    {
        string args=""; 
        foreach(MemberColumnSchema fkCol in fk.ForeignKeyMemberColumns)    
        {
                if(fkCol.NativeType=="varchar"||fkCol.NativeType=="nvarchar"||fkCol.NativeType=="char"||fkCol.NativeType=="nchar")
                {
                  args+=string.Format("@{0} {1}({2}),",fkCol.Column.Name,fkCol.Column.NativeType,fkCol.Column.Size);      
                }
                else
                {
                    args+=string.Format("@{0} {1},",fkCol.Column.Name,fkCol.Column.NativeType);    
                } 
        }
        return args.Substring(0,args.Length-1);
    }
    //根据外键表查找子表中多行的Where
    private string CreateFKSetValues(TableKeySchema fk,TableSchema table)
    {
        string args="";
        foreach(MemberColumnSchema fkCol in fk.ForeignKeyMemberColumns)
        {
            args+=string.Format("{0}.{1}=@{1},",table.Name,fkCol.Column.Name);
        }
        return args.Substring(0,args.Length-1);
    }
    
</script>

N层架构-业务逻辑层-BLLTemplate

业务逻辑层上一对多，多对一需要考虑清楚，还要考虑复合主键这类情况

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %> 

<%-- 1. Datasource --%>
<%@ Property Name="CurrentTable" Type="SchemaExplorer.TableSchema" DeepLoad="True" Optional="False"  %>

<%@ Property Name="RootNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace <%=this.RootNamespace%>.BLL
{
    public class <%= CurrentTable.Name%>
    {
        private 
        AbstractFactory.DaoFactory dataFactory = DataProvider.DefaultProvider;

        #region 增
        public int Add(Model.<%= CurrentTable.Name%> entity)
        {
            //通过工厂获得当前提供程序
            //用反射,通过web.config获得当前提供程序的名,再实例化


            return dataFactory.<%= CurrentTable.Name%>SqlProviderDao.Add(entity);
        }
        public int Add(<%=this.AllArgumentsForTable()%>)
        {

            return this.Add(new <%=this.RootNamespace%>.Model.<%= CurrentTable.Name%>()
            {
               <%=this.AllPropertiesSetValueForTable()%>

            });
        }

        #endregion

        #region 改
        public int Update(Model.<%= CurrentTable.Name%> entity)
        {
           
            return dataFactory.<%= CurrentTable.Name%>SqlProviderDao.Update(entity);

        }
        public int Update(<%=this.AllArgumentsForTable()%>)
        {
            return this.Update(new <%=this.RootNamespace%>.Model.<%= CurrentTable.Name%>()
            {
                  <%=this.AllPropertiesSetValueForTable()%>
            });

        }
        #endregion

        #region 删
        public int Delete(Model.<%= CurrentTable.Name%> entity)
        {
             return dataFactory.<%= CurrentTable.Name%>SqlProviderDao.Delete(entity);
        }
        public int Delete(<%= this.PKArgsForTable()%>)
        {
            return this.Delete(new <%=this.RootNamespace%>.Model.<%= CurrentTable.Name%>()
            {
                <%=this.PKPropertiesSetValueForTalble()%>
            });

        }
        #endregion

        #region Select
        public Model.<%= CurrentTable.Name%> Get<%= CurrentTable.Name%>ByPK(<%=this.PKArgsForTable()%>)
        {
   
            return dataFactory.<%=this.CurrentTable.Name%>SqlProviderDao.LoadByPK(<%=this.PKArgsWithoutDataTypeForTable()%>);
        }
        public List<Model.<%= CurrentTable.Name%>> GetAll<%=this.CurrentTable.Name%>List()
        {
            return dataFactory.<%=this.CurrentTable.Name%>SqlProviderDao.LoadAllEntities();

        }

        #endregion
        
               
        #region  当前表如果存在外键,找外键所在的主表
            <% foreach(TableKeySchema fk in this.CurrentTable.ForeignKeys)
            {%>
              public Model.<%=fk.PrimaryKeyTable.Name%> FindParentBy<%=fk.Name%>(<%=this.GetFKArgs(fk)%>)
              {
                     return this.dataFactory.<% = fk.PrimaryKeyTable.Name%>SqlProviderDao.LoadByPK(<%=GetFKArgsWithoutDataType(fk)%>);<% //= PKArgsWithoutDataTypeForTable(fk.PrimaryKeyTable)%>
              }
            
            <%}%>
        #endregion
        
         #region 主表找从表

   <%   foreach(TableKeySchema pk in this.CurrentTable.PrimaryKeys){
                
                    %>
                    
        //从当前表,找子表的List
             public List<Model.<%= pk.ForeignKeyTable.Name%>> Get<%= pk.ForeignKeyTable.Name%>ListBy<%= pk.Name%>(<%=this.FKArgsForTable(pk)%>)
        {
            
               return this.dataFactory.<%= pk.ForeignKeyTable.Name%>SqlProviderDao.Query_<%=pk.ForeignKeyTable.Name%>List_By<%=pk.Name%>(<%=this. FKArgsForTableWithoutDataType(pk) %>);
         
        }
        
        <%
        }
        %>
        #endregion
    }
    

      
       
}
        

<script runat="template">
    //方法:根据表,生成所有列构成的参数列表
    private string AllArgumentsForTable()
    {
        string args="";
        foreach(ColumnSchema col in CurrentTable.Columns)
        {
            args+=string.Format("{0} {1},",col.DataType,col.Name);
        }
        return args.Substring(0,args.Length-1);
    }
    //生成实力类时初始化属性
    private string AllPropertiesSetValueForTable()
    {
              string args="";
        foreach(ColumnSchema col in CurrentTable.Columns)
        {
            args+=string.Format("{0}={1},",col.Name,col.Name);
        }
        return args.Substring(0,args.Length-1);
    }
    //方法：生成列的主键列构成的参数列表
    private string PKArgsForTable()
    {
        string args="";
        foreach(MemberColumnSchema pkCol in this.CurrentTable.PrimaryKey.MemberColumns)
        {
            args+=string.Format("{0} {1},",pkCol.Column.DataType,pkCol.Column.Name);
        }
        return args.Substring(0,args.Length-1);
    }
    //方法:生成主键列的赋值：
    public string PKPropertiesSetValueForTalble()
    {
       string args="";
       foreach(MemberColumnSchema pkCol in this.CurrentTable.PrimaryKey.MemberColumns)
       {
            args+=string.Format("{0}={1},",pkCol.Column.Name,pkCol.Column.Name); 
       }
       return args.Substring(0,args.Length-1);
    }
    //方法：生成主键列参数
    private string PKArgsWithoutDataTypeForTable()
    {
        string args="";
        foreach(MemberColumnSchema pkCol in this.CurrentTable.PrimaryKey.MemberColumns)
        {
            args+=string.Format("{0},",pkCol.Column.Name);
        }
        return args.Substring(0,args.Length-1);
    }
    private string PKArgsWithoutDataTypeForTable(TableSchema table)
    {
        string args=""; 
        foreach(MemberColumnSchema pkCol in table.PrimaryKey.MemberColumns)
        {
                args += string.Format("{0},",pkCol.Column.Name);
        }
                   
         return args.Substring(0,args.Length-1);
    }
    //方法：按照某个FK，取他的列构成的参数列表（天啊，难道有复合外键吗）
    public string GetFKArgs(TableKeySchema fk)
    {
        string args="";
        foreach(MemberColumnSchema fkCol in fk.ForeignKeyMemberColumns)
        {
             args+=string.Format("{0} {1},",fkCol.Column.DataType,fkCol.Column.Name);
        }
        return args.Substring(0,args.Length-1);
    }
    
    public string GetFKArgsWithoutDataType(TableKeySchema fk)
    {
        string args="";
        foreach(MemberColumnSchema fkCol in fk.ForeignKeyMemberColumns)
        {
             args+=string.Format("{0},",fkCol.Column.Name);
        }
        return args.Substring(0,args.Length-1);
    }
    
          //主键在子表中的外键所包含的列的参数列表
    public string FKArgsForTableWithoutDataType(TableKeySchema key){
      
         string args=""; 
        foreach(MemberColumnSchema Col in key.ForeignKeyMemberColumns)
        {
                args += string.Format("{0},",Col.Column.Name);
        }
                   
         return args.Substring(0,args.Length-1);
        }
        
          //主键在子表中的外键所包含的列的参数列表带数据类型
    public string FKArgsForTable(TableKeySchema key){
      
         string args=""; 
        foreach(MemberColumnSchema Col in key.ForeignKeyMemberColumns)
        {
                args += string.Format("{1} {0},",Col.Column.Name,Col.DataType);
        }
                   
         return args.Substring(0,args.Length-1);
        }
    
</script>

N层架构-单例模式-DefaultProvieder Template

单例+反射

<%@ CodeTemplate  Inherits="CodeTemplate" Language="C#" TargetLanguage="Text"
 Description="NetTiers main template." Debug="True" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="System.DirectoryServices" %>
<%@ Assembly Name="System.Web" %>
<%@ Assembly Name="System.Xml" %>

<%@ Import Namespace="SchemaExplorer" %>
<%@ Import NameSpace="System.IO" %>
<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %> 

<%-- 1. Datasource --%>

<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" DeepLoad="True" Optional="False"%>

<%@ Property Name="RootNamespace" Default="MyOffice.Models" Type="System.String" Category="Context" Description="TargetTable that the object is based on." %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace <%=this.RootNamespace%>.BLL
{
    internal class DataProvider
    {
        private static <%=this.RootNamespace%>.AbstractFactory.DaoFactory instance = null;

        static DataProvider()
        {          
            //.NET能保证这里的代码只执行一次
            //TODO:根据web.config 动态反射实例化数据工厂的实例
            //需要:提供程序的DLL文件名
            //         类名(完整名称)    命名空间.类名
            string dllfilename = System.Web.Configuration.WebConfigurationManager.AppSettings["DataProviderDllFile"];
            string daoFactoryClassName =
                System.Web.Configuration.WebConfigurationManager.AppSettings["DataProviderFactoryName"];

            //抽象工厂
            System.Reflection.Assembly dll = System.Reflection.Assembly.LoadFile(
                System.Web.HttpContext.Current.Server.MapPath("~/DataProviders/" + dllfilename));

            instance = dll.CreateInstance(daoFactoryClassName) as <%=this.RootNamespace%>.AbstractFactory.DaoFactory;


        }

        private DataProvider()
        {
        } 
        public static <%=this.RootNamespace%>.AbstractFactory.DaoFactory  DefaultProvider
        {
            get
            {
                return  instance;
            }
        }
    }
}

N层架构-主模板-Main Template

添加指令集

<%@ Import NameSpace="System.Text" %>
<%@ Import NameSpace="System.Text.RegularExpressions" %>
<%@ Import NameSpace="System.Diagnostics" %>
<%@ Import NameSpace="System.Xml" %>
<%@ Import NameSpace="System.Xml.Xsl" %>
<%@ Import NameSpace="System.Xml.XPath" %>

注册子模板

<%@ Register Name="EntityClassTemplate" Template="Eyes.Entity.cst" MergeProperties="False" ExcludeProperties="" %>
<%@ Register Name="BizClassTemplate" Template="Eyes.Biz.cst" MergeProperties="False" ExcludeProperties="" %>
<%@ Register Name="IDALInterfaceTemplate" Template="Eyes.IDAL.cst" MergeProperties="False" ExcludeProperties="" %>
<%@ Register Name="DALFactoryTemplate" Template="Eyes.DALFactory.cst" MergeProperties="False" ExcludeProperties="" %>
<%@ Register Name="DALSqlFactoryTemplate" Template="Eyes.DALSqlFactory.cst" MergeProperties="False" ExcludeProperties="" %>
<%@ Register Name="SqlSPTemplate" Template="Eyes.SqlSp.cst" MergeProperties="False" ExcludeProperties="" %>
<%@ Register Name="DataProvider" Template="Eyes.Provider.cst" MergeProperties="False" ExcludeProperties="" %>
<%@ Register Name="DALSqlTemplate" Template="Eyes.DALSql.cst" MergeProperties="False" ExcludeProperties="" %>
<%--DataSourse--%>
<%@ Property Name="ChooseSourceDatabase" Type="SchemaExplorer.DatabaseSchema" DeepLoad="True" Optional="False" Category="01. Getting Started - Required" Description="Database that the tables views, and stored procedures should be based on. IMPORTANT!!! If SourceTables and SourceViews are left blank, the Entire Database will then be generated." %>

添加属性

<%--DataSourse--%>
<%@ Property Name="ChooseSourceDatabase" Type="SchemaExplorer.DatabaseSchema" DeepLoad="True" Optional="False" Category="01. Getting Started - Required" Description="Database that the tables views, and stored procedures should be based on. IMPORTANT!!! If SourceTables and SourceViews are left blank, the Entire Database will then be generated." %>

<%@ Property Name="RootNamespace" Default="Net.Itcast.CN" Type="System.String" Optional="False"%>

调用Script里面的创建模板方法

<%@ Property Name="RootNamespace" Default="Net.Itcast.CN" Type="System.String" Optional="False"%>
<%this.SaveEntityClasses();%>
<%this.GenerateBizClasses();%>
<%this.GenerateIDALInterface();%>
<%this.GenerateDALFactory();%>
<%this.GenerateDALSqlFactory();%>
<%this.GenerateDALSqlTemplate();%>
<%this.GenerateDataProvider();%>
<%this.GenerateSqlStoredProcedure();%>
<script runat="template">

    private string templateOutputDirectory=@"C:\Users\thinkpad\Desktop\ERPSolution\ERPSolution";
    
	[Editor(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(System.Drawing.Design.UITypeEditor))] 
	[Optional, NotChecked]
	[Category("01. Getting Started - Required")]
	[Description("The directory to output the results to.")]
	[DefaultValue("")]
	public string OutputDirectory 
	{ 
		get
		{
			 return templateOutputDirectory;
		}
		set
		{
			if (value.EndsWith("\\")) value = value.Substring(0, value.Length - 1);
			templateOutputDirectory = value;
		} 
	}
    private void SaveEntityClasses()
    {
            CodeTemplate entityTemplate=new EntityClassTemplate();
            foreach(TableSchema table in this.ChooseSourceDatabase.Tables)
            {
                entityTemplate.SetProperty("CurrentTable",table);
                entityTemplate.SetProperty("RootNamespace",this.RootNamespace);
                entityTemplate.RenderToFile(this.templateOutputDirectory+@"\"+this.RootNamespace+@".Model\"+table.Name+".cs",true);
                Debug.WriteLine(table.Name);
            }
            
    }
   
    private void GenerateBizClasses()
    {
          CodeTemplate template = new BizClassTemplate();
            foreach(TableSchema table in this.ChooseSourceDatabase.Tables)
            {
                  template.SetProperty("CurrentTable",table);
                  template.SetProperty("RootNamespace",this.RootNamespace);
                  template.RenderToFile(this.templateOutputDirectory + @"\"+this.RootNamespace+@".BLL\"  + table.Name+".cs",true);
            }
           
    }
    
       private void GenerateIDALInterface()
    {
          CodeTemplate template = new IDALInterfaceTemplate();
            foreach(TableSchema table in this.ChooseSourceDatabase.Tables)
            {
                  template.SetProperty("CurrentTable",table);
                  template.SetProperty("RootNamespace",this.RootNamespace);
                  template.RenderToFile(this.templateOutputDirectory + @"\"+this.RootNamespace+@".IDAL\"  + "I"+table.Name+"Dao"+".cs",true);
            }
           
    } 
    private void GenerateDALSqlTemplate()
    {
          CodeTemplate template = new DALSqlTemplate();
            foreach(TableSchema table in this.ChooseSourceDatabase.Tables)
            {
                template.SetProperty("CurrentTable",table);
                template.SetProperty("RootNamespace",this.RootNamespace);
                template.RenderToFile(this.templateOutputDirectory + @"\"+this.RootNamespace+@".SqlProviderDao\"  + table.Name+"SqlDao"+".cs",true);
               
            }
           
    }
    
    
        private void GenerateDALFactory()
    {
         CodeTemplate template = new DALFactoryTemplate();
            template.SetProperty("SourceDatabase",this.ChooseSourceDatabase);
            template.SetProperty("RootNamespace",this.RootNamespace);
                template.RenderToFile(this.templateOutputDirectory + @"\"+this.RootNamespace+@".DaoFactory\DaoFactory.cs",true);
        
        }
        
            private void GenerateDataProvider()
    {
         CodeTemplate template = new DataProvider();
            template.SetProperty("SourceDatabase",this.ChooseSourceDatabase);
            template.SetProperty("RootNamespace",this.RootNamespace);
                template.RenderToFile(this.templateOutputDirectory + @"\"+this.RootNamespace+@".BLL\DataProvider.cs",true);
        
        }
       
        
          private void GenerateDALSqlFactory()
    {
         CodeTemplate template = new DALSqlFactoryTemplate();
            template.SetProperty("SourceDatabase",this.ChooseSourceDatabase);
            template.SetProperty("RootNamespace",this.RootNamespace);
                template.RenderToFile(this.templateOutputDirectory + @"\"+this.RootNamespace+@".SqlProviderDao\DaoSqlFactory.cs",true);
        
        }
        
          private void GenerateSqlStoredProcedure()
    {
         CodeTemplate template = new SqlSPTemplate();
            template.SetProperty("SourceDatabase",this.ChooseSourceDatabase);
            template.SetProperty("RootNamespace",this.RootNamespace);
                template.RenderToFile(this.templateOutputDirectory + @"\"+this.RootNamespace+@".SqlSPScript\SqlSPScript.sql",true);
        
        }
   
   
</script>

小结

上面的Ntier Tempalte不是最优化的，还有很多细节需要你考虑，比如数据类型的转换：

就像我开篇所说那样，工欲善其事，必先利其器，一套好的模板可以事半功倍，我在这儿抛砖引玉，期望于君共勉，打造属于自己的铜墙铁壁。

点击下载

相关阅读:
QT5笔记：1.UI文件设计与运行机制
 QStringListModel的使用
 D pid(16916) tid(19140) 14:05:45 EdgeSnapFeature::PostExitSizeMove: WM_TWINVIEW_SHOW_GRIDLINES > off
QT5笔记：3.手动撸界面和可视化托界面混合
 Qt QString的格式化与QString的拼接
 C# WinForm UDP 发送和接收消息
 关于Convert.ToUInt16(string? value, int fromBase);
QT5笔记：2.可视化UI设计
 OpenCvSharp 打开rtsp视频并录制mp4文件
 C# List LinQ Lambda 表达式
原文地址：https://www.cnblogs.com/OceanEyes/p/codeSimthTemplate.html