SQL SERVER CLR存储过程实现

 最近做一个项目,需要做一个SQL SERVER 2005的CLR的存储过程,研究了一下CLR的实现。为方便以后再使用,在这里总结一下我的实现流程,也供对CLR感兴趣但又不知道如何实现的朋友们做一下参考,如果有不同意见,望多指教。在这里先以实现CLR存储过程为例子来进行说明。

      1。首先,启用数据库的CLR功能

          sp_configure ‘clr enabled‘, 1;
          GO
          RECONFIGURE;
          GO

       2。即然是要创建CLR存储过程,那么首先就得用C#(或者其它语言)编写一个类库实现存储过程要执行的逻辑。创建的这个类库可以有两种方法:一种是创建一个普通的C# Class library;另一个是用VS2008创建一个SQL Server Project,方法:New Project/Database Projects/Microsoft SQL Server/SQL CLR/SQL Server Project.

无论使用哪一种方法,最终都应满足下面这两个条件:

         (1)对于SQL Server 2005,所使用的.net程序集必须是.NET 2.0的。

         (2)要做为存储过程的方法,必须是static,public,且加有SqlProcedure附加属性。

        

1    [SqlProcedure]
2    public static void ExecSQLCLRProc(int messageID, int iVersion)
3    {
4   
5    }

 

在这里我使用的是用VS建一个SQL Server Project,因为它可以自动为我们实现以上这两个步骤。有了[SqlProcedure]这个属性,SQL Server就可以使用这个公开的方法创建存储过程了。

 

   3。建好CLR Project后,就需要将CLR实施到数据库了。

        可以用两种方法实施:

        (1):在VS2008中使用Deploy操作,就可以自动将建好的存储过实施到数据库。

        (2):在T-SQL里执行:

              CREATE ASSEMBLY FROM ‘DLL Path‘ WITH PERMISSION_SET = SAFE|EXTERNAL_ACCESS|UNSAFE为DLL创建程序集。

              Create Proc NCIP_ParseMessage
                  params
              AS 
              EXTERNAL NAME methodpath

              依据程序集里公开的方法创建存储过程。

 

    重点说明的是,SQL Server对CLR程序集提供了三种访问权限级别:SAFE,EXTERNAL_ACCESS,UNSAFE。如果要实施的是SAFE的级别,那么直接使用上面的两种的方法中的任何一种就可以实施了。但是由于我在存储过程中要访问网络资源,所以要用EXTERNAL_ACCESS或UNSAFE的权限级别实现

   4。实现EXTERNAL_ACCESS权限级别的CLR存储过程。(不推荐使用UNSAFE)

        (1)对SQL CLR使用strong name.先用SN.exe生成一个SNK文件,右击项目-properties-Signing-选中Sign the assembly,选择生成的SNK文件。

        (2)在数据库里为DLL生成一个KEY。

             Use master
             CREATE ASYMMETRIC KEY SQLCLRKey FROM EXECUTABLE FILE = ‘DLL Path‘  
             CREATE LOGIN loginname FROM ASYMMETRIC KEY SQLCLRKey 
             GRANT EXTERNAL ACCESS ASSEMBLY TO loginname

       然后在再用 3 中的方法就可以实现。

  5。多个程序集相互引用。如果有多个程序集,并且之前存在引用的话,那么可能还需要做以下的操作:

       (1)在每个被引用的project的AssemblyInfo.cs加下面的代码:

1 using System.Security;
2 [assembly: AllowPartiallyTrustedCallers]

      (2)如果是用SQL Server Project,还要将项目属性/Assembly name 改为唯一的名字

      (3)为每一个别Project生成一个SNK文件,并创建相应的ASYMMETRIC KEY,LOGIN,授于LOGIN相应的访问权限。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。