CLR 存储过程

项目
04/27/2017

存储过程是不能用于标量表达式的例程。与标量函数不同，存储过程可以向客户端返回表格结果和消息、调用数据定义语言 (DDL) 和数据操作语言 (DML) 语句并返回输出参数。有关 CLR 集成的优点和在托管代码和 Transact-SQL 之间进行选择的信息，请参阅 CLR 集成概述。

CLR 存储过程的要求

在公共语言运行时 (CLR) 中，存储过程作为 Microsoft.NET Framework 程序集中的类上的公共静态方法实现。该静态方法可以声明为 void，或者返回整数值。如果它返回某一整数值，则返回的整数将视作来自该存储过程的返回代码。例如：

EXECUTE @return_status = procedure_name

变量 @return_status 将包含方法返回的值。如果该方法声明为 void，则返回代码是 0。

如果该方法带参数，则 .NET Framework 实现中参数的数目应与存储过程的 Transact-SQL 声明中使用的参数数目相同。

传递给 CLR 存储过程的参数可以是在托管代码中具有等效值的任何本机 SQL Server 类型。对于用于创建该存储过程的 Transact-SQL 语法，应该用最适合的本机 SQL Server 类型等效项指定这些类型。有关类型转换的详细信息，请参阅映射 CLR 参数数据。

表值参数

表值参数 (TVP) 即传递到某一过程或函数的用户定义表类型，它提供了一种将多行数据传递到服务器的高效方法。 TVP 提供与参数数组类似的功能，但提供更大的灵活性和更紧密的与 Transact-SQL 的集成。它们还提供提升性能的潜力。 TVP 还有助于减少到服务器的往返次数。可以将数据作为 TVP 发送到服务器，而不是向服务器发送多个请求（例如，对于标量参数列表）。用户定义的表类型不能作为表值参数传递给SQL Server进程中执行的托管存储过程或函数，也不能从中返回。有关 TVP 的详细信息，请参阅将 Table-Valued 参数 (数据库引擎) 。

从 CLR 存储过程中返回结果

可以通过多种方式从.NET Framework存储过程返回信息。这包括输出参数、表格结果和消息。

OUTPUT 参数和 CLR 存储过程

与 Transact-SQL 存储过程一样，可以使用 OUTPUT 参数从.NET Framework存储过程返回信息。用于创建 .NET Framework 存储过程的 Transact-SQL DML 语法与用于创建用 Transact-SQL 编写的存储过程的语法相同。在 .NET Framework 类的代码实现中的相应参数应使用通过引用传递参数作为参数。请注意，Visual Basic 不支持与 C# 相同的输出参数。必须通过引用指定参数并应用 <Out () > 属性来表示 OUTPUT 参数，如下所示：

Imports System.Runtime.InteropServices  
...  
Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32)

以下示例显示通过 OUTPUT 参数返回信息的存储过程。

using System;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void PriceSum(out SqlInt32 value)  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         value = 0;  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);  
         SqlDataReader reader = command.ExecuteReader();  
  
         using (reader)  
         {  
            while( reader.Read() )  
            {  
               value += reader.GetSqlInt32(0);  
            }  
         }           
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
Imports System.Runtime.InteropServices  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Executes a query and iterates over the results to perform a summation.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)  
  
        Using connection As New SqlConnection("context connection=true")  
           value = 0  
           Connection.Open()  
           Dim command As New SqlCommand("SELECT Price FROM Products", connection)  
           Dim reader As SqlDataReader  
           reader = command.ExecuteReader()  
  
           Using reader  
              While reader.Read()  
                 value += reader.GetSqlInt32(0)  
              End While  
           End Using  
        End Using          
    End Sub  
End Class

在服务器上生成并创建包含上述 CLR 存储过程的程序集后，将使用以下 Transact-SQL 在数据库中创建过程，并将 sum 指定为 OUTPUT 参数。

CREATE PROCEDURE PriceSum (@sum int OUTPUT)  
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum  
-- if StoredProcedures class was inside a namespace, called MyNS,  
-- you would use:  
-- AS EXTERNAL NAME TestStoredProc.[MyNS.StoredProcedures].PriceSum

请注意，sum 声明为intSQL Server数据类型，并且 CLR 存储过程中定义的值参数被指定为 SqlInt32 CLR 数据类型。当调用程序执行 CLR 存储过程时，SQL Server会自动将 SqlInt32 CLR 数据类型转换为intSQL Server数据类型。有关可以和不能转换哪些 CLR 数据类型的详细信息，请参阅映射 CLR 参数数据。

返回表格结果和消息

将表格结果和消息返回到客户端通过 SqlPipe 对象执行，该对象通过使用 Pipe 类的 SqlContext 属性获取。 SqlPipe 对象具有 Send 方法。通过调用 Send 方法，您可以通过管道将数据传输到调用应用程序。

下面是 SqlPipe.Send 方法的若干重载，包括发送 SqlDataReader 的一个方法以及只发送文本字符串的另一个方法。

返回消息

使用 SqlPipe.Send(string) 可以将消息发送到客户端应用程序。消息文本限制在 8000 个字符以内。如果消息超出 8000 个字符，该消息将被截断。

返回表格结果

若要将查询的结果直接发送到客户端，请对 Execute 对象使用 SqlPipe 方法的重载之一。这是将结果返回到客户端的最高效方法，因为数据不必复制到托管内存即传输到网络缓冲区。例如：

using System;  
using System.Data;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Execute a command and send the results to the client directly.  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void ExecuteToClient()  
   {  
   using(SqlConnection connection = new SqlConnection("context connection=true"))   
   {  
      connection.Open();  
      SqlCommand command = new SqlCommand("select @@version", connection);  
      SqlContext.Pipe.ExecuteAndSend(command);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub ExecuteToClient()  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT @@VERSION", connection)  
            SqlContext.Pipe.ExecuteAndSend(command)  
        End Using  
    End Sub  
End Class

若要通过进程内提供程序发送以前已执行的查询结果（或者使用 SqlDataReader 的自定义实现对数据进行预处理），则使用采用 Send 的 SqlDataReader 方法的重载。此方法在速度上稍慢于前面所述的直接方法，但它提供更高的灵活性，以便在将数据发送到客户端之前操作数据。

using System;  
using System.Data;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Execute a command and send the resulting reader to the client  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendReaderToClient()  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         connection.Open();  
         SqlCommand command = new SqlCommand("select @@version", connection);  
         SqlDataReader r = command.ExecuteReader();  
         SqlContext.Pipe.Send(r);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub SendReaderToClient()  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT @@VERSION", connection)  
            Dim reader As SqlDataReader  
            reader = command.ExecuteReader()  
            SqlContext.Pipe.Send(reader)  
        End Using  
    End Sub  
End Class

若要创建动态结果集，请填充该结果集并将其发送到客户端，您可以通过当前连接创建记录并且使用 SqlPipe.Send 发送它们。

using System.Data;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
using System.Data.SqlTypes;  
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Create a result set on the fly and send it to the client.  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendTransientResultSet()  
   {  
      // Create a record object that represents an individual row, including it's metadata.  
      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));  
  
      // Populate the record.  
      record.SetSqlString(0, "Hello World!");  
  
      // Send the record to the client.  
      SqlContext.Pipe.Send(record);  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Create a result set on the fly and send it to the client.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub SendTransientResultSet()  
        ' Create a record object that represents an individual row, including it's metadata.  
        Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )  
  
        ' Populate the record.  
        record.SetSqlString(0, "Hello World!")  
  
        ' Send the record to the client.  
        SqlContext.Pipe.Send(record)          
    End Sub  
End Class

下面是通过 SqlPipe 发送表格结果和消息的示例。

using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void HelloWorld()  
   {  
      SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);  
         SqlDataReader reader = command.ExecuteReader();  
         SqlContext.Pipe.Send(reader);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub HelloWorld()  
        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)  
            Dim reader As SqlDataReader  
            reader = command.ExecuteReader()  
            SqlContext.Pipe.Send(reader)  
        End Using  
    End Sub  
End Class

第一个 Send 将消息发送到客户端，第二个则使用 SqlDataReader 发送表格结果。

请注意，这些示例只用于说明用途。对于计算密集型应用程序，CLR 函数比简单的 Transact-SQL 语句更合适。与上一个示例几乎等效的 Transact-SQL 存储过程是：

CREATE PROCEDURE HelloWorld() AS  
BEGIN  
PRINT('Hello world!')  
SELECT ProductNumber FROM ProductMaster  
END;

注意

消息和结果集在客户端应用程序中以不同的方式检索。例如，SQL Server Management Studio结果集显示在“结果”视图中，消息显示在“消息”窗格中。

如果以上 Visual C# 代码保存在 MyFirstUdp.cs 文件中并且使用以下语句编译：

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs

或者，如果以上 Visual Basic 代码保存在 MyFirstUdp.vb 文件中并且使用以下语句编译：

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb

注意

从 SQL Server 2005 开始，不支持执行 Visual C++ 数据库对象 (，例如) 编译的/clr:pure存储过程。

可以使用以下 DDL 注册最终生成的程序集，以及调用的入口点：

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll';  
CREATE PROCEDURE HelloWorld  
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;  
EXEC HelloWorld;

另请参阅

CLR 用户定义函数
 CLR 用户定义类型
 CLR 触发器

通过

CLR 存储过程