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>

 


Comments are closed.