如何更新Sql Server里的CLR程序集

这样大大增强了Sql Server的功能，为编程开发带来了方便。怎样创建CLR存储过程网上的介绍文章已经很多了，本文不讨论了。 作者在使用过程中遇到了这样的问题，就是程序集assembly的更新问题。在Sql Server中程序集不能修改，只能删了重建。见下图： 接下来如果想用drop命令把它删掉，就会出现这个：

由于对象 'IsLegalDate' 引用了 'ZSqlExtend'，DROP ASSEMBLY 失败。原来基于这个程序集肯定要建很多存储过程和函数，这些对象引用了这个程序集，DROP ASSEMBLY 失败。可以单击上图中“查看依赖关系”。怎么办呢？那只能把引用这个程序集的所有对象都删掉，这样一来工作量可就大了，而且重建程序集后还要建这些存储过程和函数。这些存储过程和函数也有可能被引用，也删不掉。这样我们更新程序集岂不是太麻烦了？笔者想出一个变通的办法，与广大网友交流。首先ZSqlExtend的代码如下，里面有一个IsLegalDate函数。用VS2008编译后的文件是ZSqlExtend.dll。using System.Data.SqlTypes

using System.Data.SqlClient

using System.Text.RegularExpressionsnamespace ZSqlExtend_NS{public class ZSqlExtend_cls{[SqlFunction(DataAccess = DataAccessKind.Read)]

public static bool IsLegalDate(int iyear, int imonth, int iday){bool ild = falsetry{DateTime newdate = new DateTime(iyear, imonth, iday)

DateTime dMin = new DateTime(1900, 1, 1)

DateTime dMax = new DateTime(2079, 6, 7)

ild = newdate >= dMin &&newdate <dMax}catch{ild = false}return ild

}} //END CLASS }根据这个dll文件在Sql Server中创建程序集和函数：create assembly ZSqlExtend

from 'E:\hs1\ZSqlExtend\bin\ZSqlExtend.dll' with permission_set = Safe

go CREATE FUNCTION IsLegalDate

(@y int,@m int,@d int)

RETURNS bit

AS EXTERNAL NAME ZSqlExtend.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDate

go 然后再建一个类库项目，比如我再做一个ZSqlExtend_dummy的类库项目，用VS2008编译后的文件是ZSqlExtend_dummy.dll，这个是ZSqlExtend.dll的代替品。把ZSqlExtend.dll的内容在这个再写一遍，不过这个是假的，所以可以写成这样：using System.Data.SqlTypes

using System.Data.SqlClient

using System.Text.RegularExpressionsnamespace ZSqlExtend_NS{public class ZSqlExtend_cls{[SqlFunction(DataAccess = DataAccessKind.Read)]

public static bool IsLegalDate(int iyear, int imonth, int iday){return true

} } //END CLASS }把这个“假的”ZSqlExtend_dummy.dll也注册进Sql Server。create assembly ZSqlExtend_dummy

from 'E:\hs1\ZSqlExtend_dummy\bin\ZSqlExtend_dummy.dll' with permission_set = Safego我们的第一步工作就做完了。现在我在项目ZSqlExtend里修改了IsLegalDate，需要重建程序集，就出现了本文一开始的情况。当然你可以先把IsLegalDate删掉，但是我不能这么做，因为很多表的计算字段和视图引用了IsLegalDate。所以我只能让IsLegalDate和程序集ZSqlExtend脱钩，然后将程序集ZSqlExtend删除重建。要想脱钩就得用那个ZSqlExtend_dummy了，因为它其中的函数签名和ZSqlExtend完全一样。函数IsLegalDate是不能删了，但我们可以修改它，把它改在别的程序集上。Sql Server中执行：alter FUNCTION IsLegalDate

(@y int,@m int,@d int)

RETURNS bit

AS EXTERNAL NAME ZSqlExtend_dummy.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDategoOK，此时程序集ZSqlExtend已经可以删除重建了，在Sql Server中运行：drop assembly ZSqlExtendcreate assembly ZSqlExtend

from 'E:\hs1\ZSqlExtend\bin\ZSqlExtend.dll' with permission_set = Safe

go当然还要把函数IsLegalDate再改回来alter FUNCTION IsLegalDate

(@y int,@m int,@d int)

RETURNS bit

AS EXTERNAL NAME ZSqlExtend.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDatego这里需要注意2个问题：1、也许你会问，既然要把函数IsLegalDate改掉，让它和程序集ZSqlExtend无关，何不直接用语句：ALTER FUNCTION [dbo].[IsLegalDate](@y [int], @m [int], @d [int])

RETURNS [bit]beginreturn 1end哈哈，你会得到如下提示： 无法对 'dbo.IsLegalDate' 进行更改，因为它是不兼容的对象类型。2、为什么要再编译个DLL呢？把编译好的ZSqlExtend.dll改个名那个“假的”不就有了吗。

（中国软件网讯）

1.如果以前部署过当前程序集，则必须先删除引用当前程序集的所有模块(各类函数和存储过程等)，然后删除当前程序集。

2.重新部署当前程序集，然后再创建相关模块(各类函数和存储过程等)。

以下是一个sql模板，有需要的可以套一下：

-- ================================= 删除先 ================================= --

-- MyCLRSqlserver 为程序集部署名称

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyCLRSqlserver' and is_user_defined = 1)BEGIN

-- 删除集合函数。

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StrJoin]') AND type = N'AF')

DROP AGGREGATE dbo.StrJoin

-- 删除标量函数。

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[___fun_CLR_ChangeSalarieScope]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION dbo.___fun_CLR_ChangeSalarieScope

-- 卸载程序集。

DROP ASSEMBLY [MyCLRSqlserver]END

-- ================================= 重新创建 ================================= --

-- 注册程序集(如果是在服务器上部署，需要将此dll复制到服务器，然后修改文件地址)。

CREATE ASSEMBLY [MyCLRSqlserver] FROM 'G:AAXX.dll'

---------------------- [dbo].[StrJoin] ----------------------

CREATE AGGREGATE [dbo].[StrJoin]

(@Value [nvarchar](4000))

RETURNS[nvarchar](4000)

EXTERNAL NAME [MyCLRSqlserver].[MyCLRSqlserver.StrJoin]GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'StrJoin.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=14 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO

-- ================================= END ================================= --

以上是手工创建过程比较麻烦，当然如果模块不多的话这样也没问题，如果很多的话可以查当前数据库的系统视图assembly_modules获取所有CLR模块，然后写代码自动生成模块DROP和CREATE过程。

---