如何更新Sql Server里的CLR程序集
Sql Server 2005/2008增加了对Clr的支持,开发者可以用.net语言如C#、VB.net等编写Sql Server的存储过程、函数。这样大大增强了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.RegularExpressions;
namespace ZSqlExtend_NS
{
public class ZSqlExtend_cls
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static bool IsLegalDate(int iyear, int imonth, int iday)
{
bool ild = false;
try
{
DateTime newdate = new DateTime(iyear, imonth, iday);
//smalldatetime范围1900-01-01 到 2079-06-06
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.RegularExpressions;
namespace 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_dummyfrom 'E:\hs1\ZSqlExtend_dummy\bin\ZSqlExtend_dummy.dll' with permission_set = Safe;
go
我们的第一步工作就做完了。现在我在项目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].IsLegalDate;
go
OK,此时程序集ZSqlExtend已经可以删除重建了,在Sql Server中运行:
drop assembly ZSqlExtend;
create 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].IsLegalDate;
go
这里需要注意2个问题:
1、也许你会问,既然要把函数IsLegalDate改掉,让它和程序集ZSqlExtend无关,何不直接用语句:
ALTER FUNCTION [dbo].[IsLegalDate]
(@y [int], @m [int], @d [int])
RETURNS [bit]
begin
return 1;
end
哈哈,你会得到如下提示:
无法对 'dbo.IsLegalDate' 进行更改,因为它是不兼容的对象类型。
2、为什么要再编译个DLL呢?把编译好的ZSqlExtend.dll改个名那个“假的”不就有了吗。原来Sql Server在建程序集时把dll文件变成二进制流存入数据库,如果两个dll完全相同就是文件名不一样,那会收到如下提示:
CREATE ASSEMBLY 失败,因为根据 MVID,源程序集应与已按名称 "ZSqlExtend" 注册的程序集完全相同。