SQL Server 2005 - 如何从CLR存储过程传回表格结果与讯息（下）

亲爱的朋友们，阅读过上一篇文章“如何从 CLR 存储过程传回表格结果与讯息（上）”之后，相信大家已经了解如何使用 SqlPipe 对象的 Send 方法将文字串直接传送至用户端。接下来我们将示范如何使用 SqlPipe 对象的 ExecuteAndSend 方法来执行 SqlCommand 并将查询结果直接传送至用户端：

 

q          类别库项目 SendYouResultSet 实作 CLR 存储过程，示范如何使用 SqlPipe 对象的 ExecuteAndSend 方法将查询结果直接传送至用户端。事实上，它会接收两个数值参数，以便查询出“章立民研究室”数据表中介于该薪资范围的员工资料：

using Microsoft.SqlServer.Server;

namespace SendYouResultSet
{
 public class SendYouResultSetClass
 {
  [Microsoft.SqlServer.Server.SqlProcedure()]
  public static void SendYouResultSet(
    decimal LowerSalary, decimal HigherSalary)
  {
   SqlCommand command;
  
   // 使用内容连接来进行连接。
   using (SqlConnection connection =
     new SqlConnection("context connection=true"))
   {
    connection.Open();
   
    command = new SqlCommand(
      "SELECT 员工编号,姓名,目前薪资 FROM dbo.章立民研究室 " +
      "WHERE 目前薪资 BETWEEN @LowerSalary AND @HigherSalary",
      connection);
    command.Parameters.AddWithValue("@LowerSalary", LowerSalary);
    command.Parameters.AddWithValue("@HigherSalary", HigherSalary);
   
    // 调用 ExecuteAndSend 方法执行 SqlCommand
    // 并查询结果直接传送给用户端
    SqlContext.Pipe.ExecuteAndSend(command);
   }
  }
 }
}

请大家注意，此CLR 存储过程的两个参数都是输入参数，因此使用传值方式来传递。另外请特别注意，CLR 存储过程是使用 “内容连接” 来连接至 SQL Server（亦即程序中的 SqlConnection("context connection=true")）。

q          请继续执行如下所列之 SQL 指令码，登录组件 SendYouResultSet.dll、建立一个引用所登录之组件 SendYouResultSet.dll 的存储过程，最后启动此 CLR 存储过程。从图表 1 的执行画面可以看出，SQL Server 2005 成功利用组件所建构之 CLR 存储过程，透过 SqlPipe 对象来执行 SqlCommand，并将查询结果直接传送至前端：

USE 北风贸易;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'SendYouResultSet')
DROP PROCEDURE SendYouResultSet;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'SendYouResultSet')
DROP ASSEMBLY SendYouResultSet;
GO

-- 登录组件SendYouResultSet.dll
CREATE ASSEMBLY SendYouResultSet
FROM
'C:\SQL2005Demo\CH13\SendYouResultSet\SendYouResultSet\bin\SendYouResultSet.dll'
WITH permission_set = Safe;
GO

-- 建立一个会引用所登录之组件SendYouResultSet.dll 的存储过程
CREATE PROCEDURE dbo.SendYouResultSet
(
 @LowerSalary money, @HigherSalary money
)
AS EXTERNAL NAME
SendYouResultSet.[SendYouResultSet.SendYouResultSetClass].SendYouResultSet;
GO

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

DECLARE     @return_value int

-- 执行CLR 存储过程
EXECUTE @return_value = dbo.SendYouResultSet
        @LowerSalary = 50000,
        @HigherSalary = 70000;

图1

 

参考书籍：
《SQL Server 2005 完全实战》
《Visual C# 2005 文件 IO 与资料存取秘诀》

章立民研究室敬上