通过

CLR 存储过程

  • 项目

存储过程是不能用于标量表达式的例程。 与标量函数不同，存储过程可以向客户端返回表格结果和消息、调用数据定义语言 (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 的详细信息，请参阅使用表值参数（数据库引擎）。

从 CLR 存储过程中返回结果

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

OUTPUT 参数和 CLR 存储过程

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

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

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

C#

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);
            }
         }         
      }
   }
}

Visual Basic

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 声明为 int SQL Server 数据类型，并且在 CLR 存储过程中定义的 value 参数指定为 SqlInt32 CLR 数据类型。 在调用程序执行 CLR 存储过程时，SQL Server 自动将 SqlInt32 CLR 数据类型转换为 int SQL Server 数据类型。 有关可以转换和不能转换的 CLR 数据类型的详细信息，请参阅 映射 CLR 参数数据。

返回表格结果和消息

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

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

返回消息

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

返回表格结果

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

[C#]

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);
      }
   }
}

[Visual Basic]

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 的自定义实现对数据进行预处理），则使用采用 SqlDataReader 的 Send 方法的重载。 此方法比上述直接方法稍慢，但它提供更高的灵活性，以便在数据发送到客户端之前操纵数据。

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);
      }
   }
}

[Visual Basic]

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);
   }
}

[Visual Basic]

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);
      }
   }
}

[Visual Basic]

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 开始，不再支持执行使用 /clr:pure 编译的 Visual C++ 数据库对象（例如存储过程）。

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

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

请参阅

概念

CLR 用户定义类型

CLR 触发器

其他资源

CLR 用户定义函数