ASP.NET创建SQL Server存储过程

一、环境

OS:win 10

IDE:Visual Studio 2010

Database:Northwind

二、过程

        使用Northwind数据库，编码实现订单查询功能，定义SQL语句或存储过程，用于订单的查询搜索，查询条件有客户名或者订单日期。

        （1）在SQL Server中新建一个查询，添加代码如下：

EXEC sp_configure 'clr enable', 1;
RECONFIGURE WITH OVERRIDE;
GO

        执行后，再运行RECONFIGURE语句，则完成数据库委托环境配置。

    （2）在VisualStudio中创建一个新项目，设置项目类型是“数据库”|“SQLServer”，选择“.Net Framework 3.5”，设置模板为“Visual C# SQL CLR 数据库项目”，设置项目名称为“SqlSrv”。项目创建完成后，会弹出弹窗设置数据库连接。在Visual Studio中开发存储过程的环境配置工作就完成了。

        （3）右键项目，选择“添加”|“添加存储过程”，代码如下：

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedure1(string khstr, string djrq)
    {
        string sql_str = " ";
        string str_sql = " ";

        str_sql += " SELECT dbo.Orders.OrderID, dbo.Customers.ContactName, dbo.Orders.OrderDate, ";
        str_sql += " dbo.Customers.Region, dbo.Customers.PostalCode, dbo.Customers.Country ";
        str_sql += " FROM dbo.Customers INNER JOIN ";
        str_sql += " dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID ";
        if (khstr != null && djrq != null && khstr.Trim() != " ")
        {
            sql_str = " dbo.Customers.ContactName = '" + khstr + "' AND ";
            sql_str += " dbo.Orders.OrderDate >= '" + djrq + "' ";
        }
        else if (khstr != null && khstr.Trim() != " " && djrq == null)
        {
            sql_str = " dbo.Customers.ContactName LIKE '%" + khstr.Trim() + "%' ";
        }
        else if ((khstr == null || khstr.Trim() == " ") && djrq != null)
        {
            sql_str = " dbo.Orders.OrderDate = '" + djrq + "' ";
        }
        if (sql_str != " ")
            sql_str = " Where " + sql_str;
        str_sql = str_sql + sql_str;
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmm = new SqlCommand();
            cmm.Connection = conn;
            cmm.CommandText = str_sql;
            SqlDataReader spr = cmm.ExecuteReader();
            SqlContext.Pipe.Send(spr);
        }
    }
};

        代码写完后选择“生成”|“生成SqlSrv”菜单项，生成成功之后，再选择“生成”|“部署SqlSrv”菜单项，成功将存储过程编译成功，并将存储过程部署到数据库中。

三、结果

        在SQL Server中展开Northwind数据库的“可编程性”，可以发现存储过程已成功创建到数据库中。执行存储过程进行验证：

USE [Northwind]
GO
DECLARE    @return_value int
EXEC	@return_value = [dbo].[StoredProcedure1]
		    @khstr = N'Mario Pontes',
		    @djrq = NULL
SELECT	  'Return Value' = @return_value
GO

        结果：

        

USE [Northwind]
GO
DECLARE    @return_value int
EXEC	@return_value = [dbo].[StoredProcedure1]
		    @khstr = NULL,
		    @djrq = N'1996-07-08'
SELECT	  'Return Value' = @return_value
GO 

        结果：

        

        存储过程创建是成功的。

四、BUG处理

        System.Data.SqlClient.SqlException:“'.' 附近有语法错误。”

        解决方法：下面代码中SQL语句少了一个“,”，加上即可。

   str_sql += " SELECT dbo.Orders.OrderID, dbo.Customers.ContactName, dbo.Orders.OrderDate, ";