SQL Server 2005 - 实作CLR存储过程

存储过程（Stored Procedures）是SQL Server 2005程序设计中最重要的一环，您将会发现，应用系统中的许多作业都会交由存储过程来完成。

  在以下的操作步骤中，我们将带领大家利用Visual Studio 2005整合式开发环境，实际建立一个CLR存储过程（采用Visual C#），并示范如何使用它： 
 
  1.如图表1所示，选取Microsoft Visual Studio 2005菜单指令来开启Visual Studio 2005的整合式开发环境。

图表1

  2. 从「项目」菜单中选取「新增／项目」指令。

  3．如图表2所示，选取「类别库」模板，然后依序指定项目名称（SayHello）、存放位置、以及方案名称（SayHello）之后，按一下「确定」按钮。

图表2

  4．于Visual Studio 2005整合式开发环境的「方案总管」中，使用鼠标右键按一下预设建立的Class1.cs并从快捷菜单中选取「删除」指令以便将它删除。

  5．从「项目」菜单中选取「加入新项目」指令。

  6． 如图表3所示，于「加入新项目」对话框中，选取「类别」模板，然后以SayHelloClass.cs作为类别档的文件名称，然后按一下「加入」按钮。

图表3

 

 

 

 

7．于程序代码编辑器中，将SayHelloClass类别的程序代码改写如下：

using Microsoft.SqlServer.Server;namespace SayHello
{
public class SayHelloClass
{
   [ Microsoft.SqlServer.Server.SqlProcedure() ]
   public static void SayHello(ref string greeting)
  {
   SqlMetaData columnInfo = new SqlMetaData("问候", SqlDbType. NVarchar , 12 );
   SqlDataRecord greetingRecord = new SqlDataRecord(new SqlMetaData [] { columnInfo });
   greetingRecord.SetString( 0 , "大家好，我是章立民！");
   // 呼叫 Pipe 对象的 Send 方法将单一数据列结果集（也就是 SqlDataRecord 对象）
   // 直接传送给客户端
   SqlContext. Pipe .Send(greetingRecord);
   // 将文字符串指派给输出参数。
   greeting = "现在时间是 " + DateTime .Now.ToString() + " -- - 章立民研究室向您问好！";
  }
}
}

  上述程序代码中的SayHello程序是一个公用静态方法，而它也就是CLR存储过程所实际呼叫的对象。

  8．按一下工具列中的「储存」按钮。

  9． 如图表4所示，使用鼠标连按两下「方案总管」中的Properties节点，一开始会显示出「应用程序」索引卷标页面，您可以去设定组件名称与跟命名空间。不过以本实作演练而言，请采用默认值，不要更改。

图表4

  10． 按一下左侧的「编译」索引卷标来切换至其页面，并且将「建置输出路径」设定成 bin/（如图表5所示），然后按一下工具列中的「储存」按钮。

 

图表6

  11．从「建置」菜单中选取「建置SayHello」指令。

  12．好的，我们现在已经完成组件的建置作业，现在必须回到SQL Server Management Studio中，以便登录组件并建立指向该组件的存储过程.

 

 

13．请您于SQL Server Management Studio中执行以下的程序代码，以便使用create ASSEMBLY陈述式来登录组件SayHello.dll，然后使用create PROCEDURE陈述式建立一个会参考所登录之组件SayHello.dll的存储过程：

USE 北风贸易;
　　 GO
　　 IF exists ( select * FROM sys.procedures where [ name ] = ’SayHello’)
　　 drop PROCEDURE SayHello;
　　 GO
　　 IF exists ( select * FROM sys.assemblies where [ name ] = ’SayHello’)
　　 drop ASSEMBLY SayHello;
　　 GO
　　 -- 登录组件 SayHello.dll（请务必确认组件所在路径正确）
　　 create ASSEMBLY SayHello
　　 FROM ’C:/SQL2005Demo/CH13/SayHello/SayHello/bin/SayHello.dll’
　　 WITH permission_set = Safe;    ｜
　　 GO                            ﹂──＞组件的位置路径

　　 -- 建立一个会参考所登录之组件 SayHello.dll 的存储过程
　　 create PROCEDURE dbo.SayHello
　　(
   　　 @Greeting nvarchar ( 80 ) OUTPUT
　　)
　　 AS EXTERNAL NAME SayHello. [ SayHello.SayHelloClass ] .SayHello;
　　 GO                                      ↓
                                 [ 命名空间.类别名称 ]

  14．请您继续于SQL Server Management Studio中执行以下的程序代码，以便尝试执行我们所建立的CLR存储过程（执行结果如图表6所示）：

图表5　

　　

USE 北风贸易;
　　 GO

　　 exec sp_configure ’clr enabled’, ’ 1 ’;
　　 GO
　　 RECONFIGURE ;
　　 GO

　　 DECLARE @return_value int , @Greeting nvarchar ( 80 );

　　 -- 执行CLR存储过程并取得传回值与输出变量的值
　　 execute @return_value = dbo.SayHello
         @Greeting = @Greeting OUTPUT;

　　 -- 检视传回值与输出变数的值
　　 select @return_value AS 传回值,
           　　 @Greeting AS "输出参数 @Greeting 之值";