c# - 参数如何从 SQL Server 传递到基于 CLR 的存储过程？

Question

使用这个例子:

http://www.sommarskog.se/dynsearch-2008/search_orders_cs.cs

....我构建并成功部署了一个基于 CLR 的存储过程到 SQL Server。在 SSMS 中查看生成的存储过程定义时(参见下面的代码)，我注意到 C# 定义中定义的参数确实是 SP 定义的一部分，但是当您查看对存储过程的实际调用时，没有参数的引用，所以我很好奇参数值实际上是如何传递的？

(我特别询问的原因是，在 c# 代码中，我想知道是否可以将 Query 变量设置为我想要的任何现有存储过程，然后在一个大的 varchar 参数中传递在 all 中，相关参数作为分隔的 KeyValue 对，然后将它们拆分并将它们作为 Command.Parameters 添加到一个循环中。基本上，我正在尝试构建一个可以执行任何其他存储的通用存储过程过程，对参数的变化数量或数据类型没有任何限制，所有这些都将在运行时读取。所以基本上，从 C# 调用到 SQL Server 时具有相同的灵 active ，除了在 SQL Server 中实现。)

生成的存储过程(在 SSMS 中查看):

CREATE PROCEDURE [dbo].[search_orders_cs]
    @Orderid [int],
    @Fromdate [datetime],
    @Todate [datetime],
    @Minprice [money],
    @Maxprice [money],
    @Custid [nvarchar](4000),
    @Custname [nvarchar](4000),
    @City [nvarchar](4000),
    @Region [nvarchar](4000),
    @Country [nvarchar](4000),
    @Prodid [int],
    @Prodname [nvarchar](4000),
    @Debug [bit]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerProject1].[StoredProcedures].[search_orders_cs]
GO


EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'twg_clr_based_sp.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'search_orders_cs'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'23' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'search_orders_cs'
GO

C# 源代码:

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void search_orders_cs(
           SqlInt32 Orderid,
           SqlDateTime Fromdate,
           SqlDateTime Todate,
           SqlMoney Minprice,
           SqlMoney Maxprice,
           SqlString Custid,
           SqlString Custname,
           SqlString City,
           SqlString Region,
           SqlString Country,
           SqlInt32 Prodid,
           SqlString Prodname,
           SqlBoolean Debug)
    {
        string Query;
        SqlCommand Command = new SqlCommand();

        Query = @"SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
                     c.CustomerID, c.CompanyName, c.Address, c.City,
                     c.Region, c.PostalCode, c.Country, c.Phone,
                     p.ProductID, p.ProductName, p.UnitsInStock,
                     p.UnitsOnOrder
              FROM   dbo.Orders o
              JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
              JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
              JOIN   dbo.Products p ON p.ProductID = od.ProductID
              WHERE  1 = 1 ";

        if (!Orderid.IsNull)
        {
            Query += " AND o.OrderID = @orderid " +
                     " AND od.OrderID = @orderid";
            Command.Parameters.Add("@orderid", SqlDbType.Int);
            Command.Parameters["@orderid"].Value = Orderid;
            Command.Parameters["@orderid"].Direction = ParameterDirection.Input;
        }

        if (!Fromdate.IsNull)
        {
            Query += " AND o.OrderDate >= @fromdate";
            Command.Parameters.Add("@fromdate", SqlDbType.DateTime);
            Command.Parameters["@fromdate"].Value = Fromdate;
            Command.Parameters["@fromdate"].Direction = ParameterDirection.Input;
        }

        if (!Todate.IsNull)
        {
            Query += " AND o.OrderDate <= @todate";
            Command.Parameters.Add("@todate", SqlDbType.DateTime);
            Command.Parameters["@todate"].Value = Todate;
            Command.Parameters["@todate"].Direction = ParameterDirection.Input;
        }

        if (!Minprice.IsNull)
        {
            Query += " AND od.UnitPrice >= @minprice";
            Command.Parameters.Add("@minprice", SqlDbType.Money);
            Command.Parameters["@minprice"].Value = Minprice;
            Command.Parameters["@minprice"].Direction = ParameterDirection.Input;
        }

        if (!Maxprice.IsNull)
        {
            Query += " AND od.UnitPrice <= @maxprice";
            Command.Parameters.Add("@maxprice", SqlDbType.Money);
            Command.Parameters["@maxprice"].Value = Maxprice;
            Command.Parameters["@maxprice"].Direction = ParameterDirection.Input;
        }

        if (!Custid.IsNull)
        {
            Query += " AND o.CustomerID = @custid" +
                     " AND c.CustomerID = @custid";
            Command.Parameters.Add("@custid", SqlDbType.NChar, 5);
            Command.Parameters["@custid"].Value = Custid;
            Command.Parameters["@custid"].Direction = ParameterDirection.Input;
        }

        if (!Custname.IsNull)
        {
            Query += " AND c.CompanyName LIKE @custname + '%'";
            Command.Parameters.Add("@custname", SqlDbType.NVarChar, 40);
            Command.Parameters["@custname"].Value = Custname;
            Command.Parameters["@custname"].Direction = ParameterDirection.Input;
        }

        if (!City.IsNull)
        {
            Query += " AND c.City = @city";
            Command.Parameters.Add("@city", SqlDbType.NVarChar, 15);
            Command.Parameters["@city"].Value = City;
            Command.Parameters["@city"].Direction = ParameterDirection.Input;
        }

        if (!Region.IsNull)
        {
            Query += " AND c.Region = @region";
            Command.Parameters.Add("@region", SqlDbType.NVarChar, 15);
            Command.Parameters["@region"].Value = Region;
            Command.Parameters["@region"].Direction = ParameterDirection.Input;
        }

        if (!Country.IsNull)
        {
            Query += " AND c.Country = @country";
            Command.Parameters.Add("@country", SqlDbType.NVarChar, 15);
            Command.Parameters["@country"].Value = Country;
            Command.Parameters["@country"].Direction = ParameterDirection.Input;
        }

        if (!Prodid.IsNull)
        {
            Query += " AND od.ProductID = @prodid" +
                     " AND p.ProductID = @prodid";
            Command.Parameters.Add("@prodid", SqlDbType.Int);
            Command.Parameters["@prodid"].Value = Prodid;
            Command.Parameters["@prodid"].Direction = ParameterDirection.Input;
        }

        if (!Prodname.IsNull)
        {
            Query += " AND p.ProductName LIKE @prodname + '%'";
            Command.Parameters.Add("@prodname", SqlDbType.NVarChar, 40);
            Command.Parameters["@prodname"].Value = Prodname;
            Command.Parameters["@prodname"].Direction = ParameterDirection.Input;
        }

        Query += " ORDER BY o.OrderID";

        using (SqlConnection Connection = new SqlConnection("context connection=true"))
        {
            Connection.Open();

            if (Debug)
            {
                SqlContext.Pipe.Send(Query);
            }

            Command.CommandType = CommandType.Text;
            Command.CommandText = Query;
            Command.Connection = Connection;
            SqlContext.Pipe.ExecuteAndSend(Command);
        }
    }
};

Answer 1

这是一个示例，您可以使用它来调用 DLL 中的方法。

Step 1. Create a library class in Visual Studio

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MyNamespace
{
    public static class MyClass
    {
        [SqlProcedure]
        public static void MyMethod(SqlString strInParam, out SqlString strOutParam)
        {
            strOutParam = $"Hi '{strInParam}', The date time is: " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
        }
    }
}

Step 2. Create an assembly

CREATE ASSEMBLY [AssemblyName]
    AUTHORIZATION dbo
    FROM 'DLL_Path'
    WITH PERMISSION_SET = SAFE
GO

Step 3. Create a procedure

-- DROP PROCEDURE MyProcedure
CREATE PROCEDURE MyProcedure(@strInParam nvarchar(1000), @strOutParam nvarchar(1000) OUTPUT)
    AS EXTERNAL NAME [AssemblyName].[MyNamespace.MyClass].[MyMethod]
GO

Step 4. Enable CLR

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled'
GO

Step 5. Executing the stored-procedure will result in callling the written method

DECLARE @res NVARCHAR(1000);
EXEC dbo.MyProcedure @strInParam = 'Siya', @strOutParam = @res OUTPUT;

SELECT @res

The result is:
Hi 'Siya', The date time is: 2018/05/19 14:17:47