阅读目录
开篇
平常开发时,由于冗余代码过多,程序员做重复的工作过多势必会影响开发效率。倘若对重复性代码简单的复制、粘贴,虽然也能节省时间,但也需仔细一步步替换,这无疑也是一件费力的事。这时我们急需代码生成工具,根据一套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不是最优化的,还有很多细节需要你考虑,比如数据类型的转换:
就像我开篇所说那样,工欲善其事,必先利其器,一套好的模板可以事半功倍,我在这儿抛砖引玉,期望于君共勉,打造属于自己的铜墙铁壁。