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>