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<this.SourceDatabase.Tables.Count; i++)
{
if (bGenOne)
{
TableName = tName;
}
else
{
TableName = this.SourceDatabase.Tables[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;
}
}%>
<script runat="template">
public string GetCSharpTypeFromDBFieldType(ColumnSchema column)
{
if (column.Name.EndsWith("TypeCode")) return column.Name;
switch (column.DataType)
{
case DbType.AnsiString: return "string";
case DbType.AnsiStringFixedLength: return "string";
case DbType.Binary: return "byte[]";
case DbType.Boolean: return "bool";
case DbType.Byte: return "byte";
case DbType.Currency: return "decimal";
case DbType.Date: return "DateTime";
case DbType.DateTime: return "DateTime";
case DbType.Decimal: return "decimal";
case DbType.Double: return "double";
case DbType.Guid: return "Guid";
case DbType.Int16: return "short";
case DbType.Int32: return "int";
case DbType.Int64: return "long";
case DbType.Object: return "object";
case DbType.SByte: return "sbyte";
case DbType.Single: return "float";
case DbType.String: return "string";
case DbType.StringFixedLength: return "string";
//case DbType.StringFixedLength: return "UniqueIdentifier";
case DbType.Time: return "TimeSpan";
case DbType.UInt16: return "ushort";
case DbType.UInt32: return "uint";
case DbType.UInt64: return "ulong";
case DbType.VarNumeric: return "decimal";
default:
{
return "__UNKNOWN__" + column.NativeType;
}
}
}
public string GetSQLVariableType(ColumnSchema column)
{
if (column.Name.EndsWith("TypeCode")) return column.Name;
switch (column.NativeType.ToLower())
{
case "bigint" : return "SqlDbType.BigInt";
case "binary" : return "SqlDbType.Binary";
case "bit" : return "SqlDbType.Bit";
case "char" : return "SqlDbType.Char";
case "date" : return "SqlDbType.Date";
case "datetime" : return "SqlDbType.DateTime";
case "datetime2" : return "SqlDbType.DateTime2";
case "datetimeoffset" : return "SqlDbType.DateTimeOffset";
case "decimal" : return "SqlDbType.Decimal";
case "float" : return "SqlDbType.Float";
//case "geography" : return "SqlDbType.BigInt";
//case "geometry" : return "SqlDbType.BigInt";
//case "hierarchyid" : return "SqlDbType.BigInt";
case "image" : return "SqlDbType.Image";
case "int" : return "SqlDbType.Int";
case "money" : return "SqlDbType.Money";
case "nchar" : return "SqlDbType.NChar";
case "ntext" : return "SqlDbType.NText";
case "numeric" : return "SqlDbType.Numeric";
case "nvarchar" : return "SqlDbType.NVarChar";
case "real" : return "SqlDbType.Real";
case "smalldatetime" : return "SqlDbType.SmallDateTime";
case "smallint" : return "SqlDbType.SmallInt";
case "smallmoney" : return "SqlDbType.SmallMoney";
case "sql_variant" : return "SqlDbType.Variant";
case "text" : return "SqlDbType.Text";
case "time" : return "SqlDbType.Time";
case "timestamp" : return "SqlDbType.Timestamp";
case "tinyint" : return "SqlDbType.TinyInt";
case "uniqueidentifier" : return "SqlDbType.UniqueIdentifier";
case "varbinary" : return "SqlDbType.VarBinary";
case "varchar" : return "SqlDbType.VarChar";
case "xml" : return "SqlDbType.Xml";
default: return "__UNKNOWN__" + column.NativeType;
}
}
public void PrintTitle(string purpose, string name, string desc)
{
Response.WriteLine("-----------------------------------------");
Response.WriteLine("-- Purpose: t"+purpose);
Response.WriteLine("-- Name: tt"+name);
Response.WriteLine("-- Description: "+desc);
Response.WriteLine("-- Modify Time: ", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
Response.WriteLine("-----------------------------------------");
}
public string GenerateInParameter(TableSchema TableName, bool type)
{
string strReturn = string.Empty;
if (type)
{
for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++)
{
strReturn += "@"+TableName.PrimaryKey.MemberColumns[i].Name + " " + TableName.PrimaryKey.MemberColumns[i].NativeType +
GetParameter(TableName.PrimaryKey.MemberColumns[i]);
if (i<TableName.PrimaryKey.MemberColumns.Count-1)
strReturn += ",";
strReturn+="n";
}
}
else
{
for (int i=0; i<TableName.Columns.Count; i++)
{
strReturn += "@"+TableName.Columns[i].Name + " " + TableName.Columns[i].NativeType +
GetParameter(TableName.Columns[i]);
if (i<TableName.Columns.Count-1)
strReturn += ",";
strReturn+="n";
}
}
return strReturn;
}
public string GetParameter(ColumnSchema column)
{
switch (GetSQLVariableType(column))
{
case "SqlDbType.VarChar":
case "SqlDbType.Char":
case "SqlDbType.NChar":
case "SqlDbType.NVarchar":
case "SqlDbType.Binary":
return "("+column.Size+")";
case "SqlDbType.Numeric":
case "SqlDbType.Decimal":
return "("+column.Precision+","+column.Scale+")";
default:
return "";
}
}
public string GetAllRows(TableSchema TableName, bool bExtend, string strExtend)
{
string strReturn = "";
if (bExtend)
{
switch (strExtend)
{
case "[]":
for (int i=0; i<TableName.Columns.Count; i++)
{
strReturn += "["+TableName.Columns[i].Name+"],";
}
break;
case "@":
for (int i=0; i<TableName.Columns.Count; i++)
{
strReturn += "@"+TableName.Columns[i].Name+",";
}
break;
case "update_nokey":
for (int i=0; i<TableName.NonPrimaryKeyColumns.Count; i++)
{
strReturn += "["+TableName.NonPrimaryKeyColumns[i].Name+"]=@"+TableName.NonPrimaryKeyColumns[i].Name+",";
}
break;
case "pk_where":
for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++)
{
strReturn += TableName.PrimaryKey.MemberColumns[i].Name+"=@"+TableName.PrimaryKey.MemberColumns[i].Name;
if (i<TableName.PrimaryKey.MemberColumns.Count-1)
strReturn += " AND ";
}
break;
}
}
else
{
for (int i=0; i<TableName.Columns.Count; i++)
{
strReturn += TableName.Columns[i].Name+",";
}
}
return strReturn.TrimEnd(',');
}
public string GetKeyMax(TableSchema TableName)
{
string strReturn = "null";
for (int i=0; i<TableName.PrimaryKey.MemberColumns.Count; i++)
{
if (GetSQLVariableType(TableName.PrimaryKey.MemberColumns[i]) != "SqlDbType.UniqueIdentifier")
{
strReturn="max(["+TableName.PrimaryKey.MemberColumns[i].Name+"])+1";
break;
}
}
return strReturn;
}
</script>