ASP.NET三层架构源码(CodeSmith版)之五：Table-StoreProcedure(存储过程)

动软代码生成器生成的ASP.NET三层架构代码比较规范，是学习ASP.NET的好例子

此三层架构改造自动软的工厂模式模板，使用CodeSmith进行重写，以方便大家修改模板文件

以下是针对表格的存储过程生成源码：

<%@ CodeTemplate Inherits="CodeTemplate" language="C#" TargetLanguage="Text" Description="NetTiers main template."Debug="True" ResponseEncoding="UTF-8"%>
<%-- 加载访问数据库的组件SchemaExplorer,并声明其使用的命名空间 --%>
<%@ Assembly Name="SchemaExplorer" %>  
<%@ Import Namespace="SchemaExplorer" %>  
<%@ Import Namespace="System.Text" %> 
<%@ Assembly Name="System.Data" %>

<%@ Property Name="Author" Type="String" Category="Context"  Description="Author" Default="chenr"%>  
<%@ Property Name="TablePrefix" Type="System.String" Default="T" Category="Context" Description="The prefix to remove from table names" %>  
<%@ Property Name="bGenOne" Type="Boolean" Category="Context"  Description="Generate One Template Or All"%>
<%-- 添加源数据库属性 --%>
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema"  DeepLoad="True" Optional="False" Category="01. Required" Description="Database" %>
<%@ Property Name="tName" Type="TableSchema"  DeepLoad="True" Optional="True" Category="01. Required" Description="TableName" %>

<% 

TableSchema TableName;

for (int i=0; i
-- ===========================================================
-- TableName：   <%=TableName.Name%>
-- Author:       <%=Author%> 
-- Create Time:  <%=DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")%>
-- ===========================================================

if exists (select * from dbo.sysobjects where id = object_id(N'[<%=TableName.Name%>_GetMaxId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [<%=TableName.Name%>_GetMaxId]
GO

<% PrintTitle("得到主键字段最大值","",""); %>
CREATE PROCEDURE <%=TableName.Name%>_GetMaxId
AS
	DECLARE @TempID int
	SELECT @TempID = <%=GetKeyMax(TableName)%> FROM [<%=TableName.Name%>]
	IF @TempID IS NULL
		RETURN 1
	ELSE
		RETURN @TempID

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[<%=TableName.Name%>_Exists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [<%=TableName.Name%>_Exists]
GO
 
<% PrintTitle("是否已经存在","",""); %>
CREATE PROCEDURE <%=TableName.Name%>_Exists
<%= GenerateInParameter(TableName, true) %>
AS
	DECLARE @TempID int
	SELECT @TempID = count(1) FROM [<%=TableName.Name%>] WHERE <%= GetAllRows(TableName, true, "pk_where") %> 
	IF @TempID = 0
		RETURN 0
	ELSE
		RETURN 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[<%=TableName.Name%>_ADD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [<%=TableName.Name%>_ADD]
GO

<% PrintTitle("增加一条记录","",""); %>
CREATE PROCEDURE <%=TableName.Name%>_ADD
<%= GenerateInParameter(TableName, false) %>
AS 
	INSERT INTO [<%=TableName.Name%>](
    <%= GetAllRows(TableName, true, "[]") %>
	)VALUES(
	<%= GetAllRows(TableName, true, "@") %>
	)

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[<%=TableName.Name%>_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [<%=TableName.Name%>_Update]
GO

<% PrintTitle("修改一条记录","",""); %>
CREATE PROCEDURE <%=TableName.Name%>_Update
<%= GenerateInParameter(TableName, false) %>
 AS 
	UPDATE [<%=TableName.Name%>] SET 
    <%= GetAllRows(TableName, true, "update_nokey") %>
    WHERE <%= GetAllRows(TableName, true, "pk_where") %>

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[<%=TableName.Name%>_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [<%=TableName.Name%>_Delete]
GO

<% PrintTitle("删除一条记录","",""); %>
CREATE PROCEDURE <%=TableName.Name%>_Delete
<%= GenerateInParameter(TableName, true) %>
 AS 
	DELETE [<%=TableName.Name%>]
	WHERE <%= GetAllRows(TableName, true, "pk_where") %>

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[<%=TableName.Name%>_GetModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [<%=TableName.Name%>_GetModel]
GO

<% PrintTitle("得到实体对象的详细信息","",""); %>
CREATE PROCEDURE <%=TableName.Name%>_GetModel
<%= GenerateInParameter(TableName, true) %>
 AS 
	SELECT <%= GetAllRows(TableName, false, "") %>
	 FROM [<%=TableName.Name%>]
	 WHERE <%= GetAllRows(TableName, true, "pk_where") %>

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[<%=TableName.Name%>_GetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [<%=TableName.Name%>_GetList]
GO

<% PrintTitle("查询记录信息","",""); %>
CREATE PROCEDURE <%=TableName.Name%>_GetList
 AS 
	SELECT <%= GetAllRows(TableName, false, "") %>
	 FROM [<%=TableName.Name%>]

GO
<%
    if (bGenOne)
    {
        break;
    }
}%>