procedure.cst

一套正版网络棋牌游戏程序 第一次上传 请多多指教

<%@ CodeTemplate Language="C#" ClassName="Procedure" TargetLanguage="C#" Debug="False" Description="" %>
<%@ Property Name="databaseName" Type="System.String" Default="TestDB" Optional="True" Category="Context" Description="" %>
<%@ Property Name="prefix" Type="System.String" Default="Sp" Optional="True" Category="Context" Description="" %>
<%@ Property Name="tableName" Type="System.String" Default="" Optional="True" Category="Context" Description="" %>
<%
OpenResponse("Procedure.sql");
DbEntity db = Template.GetDataBase(databaseName);
foreach(Table tb in db.Tables)
{
	if( tableName !="" && tableName != null && tableName != tb.Name)
		continue;
	
	int iFieldCount = tb.Fields.Count;
	int iField = 0;
	int iPkFieldCount = 0;
	int iPkField = 0;
	int iNoPkField = 0;
	int iDefaultIdentityField = 0;
	int iDefaultIdentityFieldCount = 0;
	int iPkIdenField = 0;
	int iPkIdenFieldCount = 0;
	string pkString = "";
	string firstPk = "";
	string pkParamList = "";
	string allParamList = "";
	
	string nopkIdenParamSqlEq = "";
 	string pkSqlEq = "";
 	string noPkSqlEq = "";
 	
	foreach(Field fd in tb.Fields)
	{
		if( fd.IsPK == true)
			iPkFieldCount++;
		if( fd.IsPK == false && fd.Identity.Enable == false)
			iPkIdenFieldCount ++;
	}
	
	foreach(Field fd in tb.Fields)
	{	
		iField++;
		if( fd.Identity.Enable == true || (fd.DefaultValue != "" && fd.DefaultValue != null))
			iDefaultIdentityFieldCount++;
			
		if( fd.IsPK == true)
		{
			iPkField++;
			pkString += fd.PropertyName;
			if( firstPk == "")
				firstPk = fd.PropertyName;

			//pkParamList
			pkParamList += "\t@"+fd.PropertyName+" "+fd.DataType.FullName;
			if(iPkField != iPkFieldCount)
				pkParamList +=",\r\n";

			//pkSqlEq 
			pkSqlEq += "\t["+fd.PropertyName+"] = @"+fd.PropertyName;
			if(iPkField != iPkFieldCount)
				pkSqlEq += ",\r\n";
		}
		else
		{
			iNoPkField++;
			//noPkSqlEq
			
			noPkSqlEq += "\t["+fd.PropertyName+"] = @"+fd.PropertyName;
			if(iNoPkField != iFieldCount - iPkFieldCount)
				noPkSqlEq += ",\r\n";
		}
		
		if( fd.IsPK == false && fd.Identity.Enable == false)
		{
			iPkIdenField ++;
			nopkIdenParamSqlEq+= "\t["+fd.PropertyName+"] = @"+fd.PropertyName;
			if(iPkIdenField != iPkIdenFieldCount)
				nopkIdenParamSqlEq += ",\r\n";
		}
		
		//allParamList
		allParamList += "\t@"+fd.PropertyName+" "+fd.DataType.FullName;
		if(iField != iFieldCount)
			allParamList +=",\r\n";
	} 
	
	string insert = prefix+"_Insert"+tb.PropertyName;
	string update = prefix+"_Update"+tb.PropertyName;
	string deleteByPk = prefix+"_Delete"+tb.PropertyName+"By"+pkString;
	string delete = prefix+"_Delete"+tb.PropertyName;
	string selectByPk = prefix+"_Select"+tb.PropertyName+"By"+pkString;
	string selectAll = prefix+"_SelectAll"+tb.PropertyName;
	string select = prefix+"_Select"+tb.PropertyName;
	string selectPaged = prefix +"_SelectPaged"+tb.PropertyName;
%>
/*==================================================
* <%=tb.PropertyName%>
*
*=================================================*/
IF OBJECT_ID(N'[dbo].[<%=insert%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=insert%>]
IF OBJECT_ID(N'[dbo].[<%=update%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=update%>]
IF OBJECT_ID(N'[dbo].[<%=deleteByPk%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=deleteByPk%>]
IF OBJECT_ID(N'[dbo].[<%=delete%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=delete%>]
IF OBJECT_ID(N'[dbo].[<%=selectByPk%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=selectByPk%>]
IF OBJECT_ID(N'[dbo].[<%=selectAll%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=selectAll%>]	
IF OBJECT_ID(N'[dbo].[<%=select%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=select%>]
IF OBJECT_ID(N'[dbo].[<%=selectPaged%>]') IS NOT NULL
	DROP PROCEDURE [dbo].[<%=selectPaged%>]
GO

<%--  Insert --%>
/*Insert=================================================*/
CREATE PROCEDURE [dbo].[<%=insert%>]
<%
iField = 0;
foreach(Field fd in tb.Fields)
{
	if( fd.Identity.Enable == false && (fd.DefaultValue == "" || fd.DefaultValue == null))
	{
		iField++;
		Response.Write("\t@"+fd.PropertyName+" "+fd.DataType.FullName);
		if(iField != iFieldCount - iDefaultIdentityFieldCount)
			Response.Write(",\r\n");
	}
}
%> 
AS 
INSERT INTO [dbo].[<%=tb.PropertyName%>] (
<%
iField = 0;
foreach(Field fd in tb.Fields)
{
	if( fd.Identity.Enable == false && (fd.DefaultValue == "" || fd.DefaultValue == null))
	{
		iField++;
		Response.Write("\t["+fd.PropertyName+"]");
		if(iField != iFieldCount - iDefaultIdentityFieldCount)
			Response.Write(",\r\n");
	}
}
%> 
) VALUES (
<%
iField = 0;
foreach(Field fd in tb.Fields)
{
	if( fd.Identity.Enable == false && (fd.DefaultValue == "" || fd.DefaultValue == null))
	{
		iField++;
		Response.Write("\t@"+fd.PropertyName);
		if(iField != iFieldCount - iDefaultIdentityFieldCount)
			Response.Write(",\r\n");
	}
}
%>
)
SELECT @@RowCount
GO

/*update=================================================*/
<%--  Update --%>
CREATE PROCEDURE [dbo].[<%=update%>]
<%=allParamList%> 
AS 
<% if(nopkIdenParamSqlEq != "")
{
%>
UPDATE [dbo].[<%=tb.PropertyName%>] SET
<%=nopkIdenParamSqlEq%>
WHERE
<%=pkSqlEq%>
<%
}
%>
SELECT @@RowCount
GO

/*DeleteByPk=================================================*/
<%--  DeleteByPk --%>
CREATE PROCEDURE [dbo].[<%=deleteByPk%>]
<%=pkParamList%>
AS 
DELETE [dbo].[<%=tb.PropertyName%>]
WHERE
<%=pkSqlEq%>
SELECT @@RowCount
GO

/*delete=================================================*/
<%--  Delete --%>
CREATE PROCEDURE [dbo].[<%=delete%>]
	@where varchar(256)
AS
DECLARE @strSQL nvarchar(1000)
set @strSQL = 'delete from [dbo].[<%=tb.PropertyName%>] '+@where
exec sp_executesql @strSQL
SELECT @@RowCount
GO

/*selectByPk=================================================*/
<%--  SelectByPk --%>
CREATE PROCEDURE [dbo].[<%=selectByPk%>]
<%=pkParamList%>
AS 
SELECT * FROM [dbo].[<%=tb.PropertyName%>]
WHERE
<%=pkSqlEq%>
GO

/*selectAll=================================================*/
<%--  SelectAll --%>
CREATE PROCEDURE [dbo].[<%=selectAll%>]
AS 
SELECT * FROM [dbo].[<%=tb.PropertyName%>]
GO
<%--  Select --%>
CREATE PROCEDURE [dbo].[<%=select%>]
	@where varchar(256)
AS
DECLARE @strSQL nvarchar(1000)
set @strSQL = 'select * from [dbo].[<%=tb.PropertyName%>] '+@where
exec sp_executesql @strSQL
GO

/*selectPaged=================================================*/
<%--  SelectPaged--%>
CREATE PROCEDURE [dbo].[<%=selectPaged%>] 
@pageIndex int,
@pageSize int,
@GetCount int,
@where varchar(255)=''
As
DECLARE @strSQL nvarchar(1000)
DECLARE @strWhereTemp nvarchar(1000)
DECLARE @strPageTemp nvarchar(1000)

set @strWhereTemp = @where
if @pageIndex > 1 
begin
   set @strPageTemp = ' <%=firstPk%> not in (select top '+str((@pageIndex-1)*@pageSize) +' <%=firstPk%> from <%=tb.PropertyName%> ' + @where+')'
   if @strWhereTemp = ''
      set @strWhereTemp = 'where '+@strPageTemp
   else
      set @strWhereTemp = @strWhereTemp +' and '+@strPageTemp
end

set @strSQL = 'select top '+str(@pageSize)+' * from <%=tb.PropertyName%> '+@strWhereTemp
exec sp_executesql @strSQL

if @GetCount != 0
begin
set @strSQL = 'select count(*) from <%=tb.PropertyName%> ' + @where 
exec sp_executesql @strSQL
end

GO

<%
}
CloseResponse();
SpApi.get_Commands().Run("OpenCommand","DirectFileName;"+FullFileName);
%>