SQL Server 2005 - 如何从CLR存储过程传回表格结果与讯息（二）
引用了章立民先生的文章：http://www.cnblogs.com/liminzhang/archive/2007/01/16/621422.html
阅读过上一篇文章“如何从 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
