CLR（公共语言运行时）存储过程

2017-04-27

存储过程是不能在标量表达式中使用的例程。与标量函数不同，它们可以向客户端返回表格结果和消息，调用数据定义语言（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 用于在数据库中创建过程，并将总和指定为 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

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

返回表格结果和消息

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

这些是方法的 SqlPipe.Send 多个重载，包括一个重载，另一个只发送 SqlDataReader 文本字符串。

返回消息

用于 SqlPipe.Send(string) 将消息发送到客户端应用程序。消息的文本限制为 8000 个字符。如果消息超过 8000 个字符，将被截断。

返回表格结果

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

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对数据进行预处理），请使用采用方法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);  
      }  
   }  
}

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 开始，不支持使用编译的 /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 User-Defined 函数
 CLR User-Defined 类型
 CLR 触发器

通过