|
?介绍
??? SQL SERVER2005提供了在.NET中自定义函数、存储过程、聚合、触发器以及类型的功能。于是，我们可以在数据库的函数、存储过程或类型整合.NET对象方法等。在之前的一篇文章，我们演示了如何将正则表达式功能整合到SQL CLR中。在本文中，还是演示这种整合，但是这次是从SQL CLR中调用一个Web Service。我们将使用AdventureWorks这个样例数据库，定义一个函数，根据http://www.webservicex.net/CurrencyConvertor.asmx这个Web Service提供的汇率，进行从欧元到美元的外汇转换。（这个WebService提供的汇率，只用于演示，不能作为真实金融交易的数据。）
本文没有详细的描述所有的细节，读者应该具有一定的在SQL SERVER2005下开发CLR的基础。
定义转换函数
????? 首先，在Visual Studio 2005中，根据开发模板，选择新建一个C#或者VB.NET的SQL SERVER项目。（注意：在Beta版中，必须在安装VS2005之前安装SQL SERVER 2005）
????? *在一些非正式的VS2005的版本中，直接使用工具引用WebService生成的代理类不能在SQL SERVER CLR中使用。原因是这种代理类生成了不能使用在CLR中的同步方法。具体可参考Vineet Rao 在 blogs.msdn.com中的文章。解决的方法是利用C:/Program Files/Microsoft Visual Studio 8/SDK/v2.0/Bin目录下的WSDL.exe工具生成老式异步方法的代理类，只用这种类才能在CLR中以External ACCESS的权限加载。如果我们使用“新”的代理类，必须使用Unsafe的安全级别，这种方式应尽量避免。我们可以使用一个XML配置文件来生成需要规范的代理类。创建一个以下内容的XML文件：
<wsdlParameters xmlns
='http://microsoft.com/webReference/'> <language>c#</language>
<protocol>Soap</protocol>
<nologo>true</nologo>
<sharetypes>false</sharetypes>
<webReferenceOptions>
<codeGenerationOptions>properties oldAsync</codeGenerationOptions>
</webReferenceOptions>
</wsdlParameters>
??? 假设保存路径为：c:/oldwsdlconfig.xml（如果使用VB.net项目，则替换使用<language>vb</language>元素）。然后，使用以下命令行生成代理类：
C:/Program Files/Microsoft Visual Studio 8/SDK/v2.0/Bin/wsdl.exe /par:c:/ oldwsdlconfig.xml http://www.webservicex.net/CurrencyConvertor.asmx
??? 运行结束后，将生成CurrencyConvertor.cs(或者.vb)文件。在VS中使用“添加现有项”定位到文件的位置后，将CurrencyConvertor.cs(或者.vb)文件增加到现有项目中。同时在项目中增加对System.Web.Services的引用：
??? 下面，我们防止代码在使用WebService通信中使用序列化和反序列化的过程（创建专门进行序列化的程序集）。在VS中选择解决方案资源管理器，右键选择属性。C#和VB.NET的设置方法稍有不同：
C#：
?
?
??? 选择“生成”标签，在底部选择“生成序列化程序集”为“开”。
同时，在项目属性窗口中，我们设置权限等级：在数据库标签中，设置“权限级别”为“外部”。这样，我们声明这个程序集是“Safe”的，不会作出危害性的过程，外部调用等。但是，它仍然不如声明为“Safe”的程序集安全，因为它进行了网络通信，访问硬盘设备等行为。
?
?
最后，可以进行主程序开发工作了：编写货币转化函数。这个函数输入sql money类型的参数，乘上欧元对美元的汇率，返回一个sql money类型的数据。编写的方法：在解决方案资源管理器中的项目上，右键选择新增用户自定义函数。并编写如下代码：
C# ?
public partial class UserDefinedFunctions
{
? [Microsoft.SqlServer.Server.SqlFunction]
? public static SqlMoney EUR2USD(SqlMoney amount)
? {
??? CurrencyConvertor cc = new CurrencyConvertor();
??? return new SqlMoney(cc.ConversionRate(Currency.EUR,Currency.USD)
????? * amount.ToDouble());
? }
};
VB.NET ?
Partial Public Class UserDefinedFunctions
? <Microsoft.SqlServer.Server.SqlFunction()> _
? Public Shared Function EUR2USD(ByVal amount As SqlMoney) As SqlMoney
??? Dim cc As New CurrencyConvertor()
??? Return New SqlMoney(amount.ToDouble() * _
??? cc.ConversionRate(Currency.EUR,Currency.USD))
? End Function
End Class
接下来，便可以生成并部署项目了。
?
?
在SQL Management Studio调整
?
?
现在可以SQL Server 2005 Management Studio尝试运行代码。在SQL Server 2005 Management Studio中导航到我们部署代码的数据库（比如Adventureworks），可以看到在“可编程行”文件夹下，“程序集”选项中有一个已加载的程序级，并且创建了一个标量函数：
如果这时我们运行函数，会得到一个错误。在对象资源管理器中选择数据库右键打开，新建查询。在查询窗口执行以下T-SQL语句：
SELECT dbo.EUR2USD(1)
?
?
当我们执行语句的时候，不但不能获得希望的结果，反而会得到以下错误：
Msg 6522,Level 16,State 2,Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'EUR2USD':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted,consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(),LoadFile(),Load(byte[]) and LoadModule() have been disabled by the host.
原因是少部署了一个DLL。当我们查看VS项目的BIN目录，会发现生成了两个DLL文件：WebServiceConsume.dll 和 WebServiceConsume.XmlSerializers.dll.后面一个DLL用来在WEB SERVICE调用时候进行序列化和反序列化，它没有被自动部署到SQL SERVER数据库中。我们需要进行手工部署：右键点击SQL management studio中对象资源管理器中“程序集”文件夹，选择“新建程序集”…
在显示的窗口中浏览选择指定WebServiceConsume.XmlSerializers.dll的位置。你可以不改变权限级别的“Safe”，这个程序集不进行外部通信。选择确定后，刷新“程序集”文件夹，可以看到刚才新建的程序集。
再次运行刚才的T－SQL语句：它已经能够成功运行了，提供汇率的Web Service能够被整合到CLR的函数直接调用了。
? ? Web Services和效率
? ? 最后叮嘱：慎重使用类似的WebService调用。依赖网络通信的调用，速度较慢。所以如果你要将这些功能处理大数据量的表时，要非常慎重。测试一下，使用下面的查询，获取所有的欧元和美元数据（504 rows,AdventureWorks database）：
? ? select StandardCost,dbo.EUR2USD(StandardCost) from Production.Product
? ? 大概需要4分44秒才能完成查询，因为需要调用WebService 504次。然而，我们可以重写成以下的方式： declare @rate float select @rate = dbo.EUR2USD(1) select StandardCost,StandardCost * @rate from AdventureWorks.Production.Product 这样，我们只需要调用WebService一次，将汇率存储到本地变量。除非WebService的结果在执行期间改变了，后一种方法比前者快300多倍。 慎重的使用WebService：如果WebService的调用结果被重复的使用，尽量将结果缓存到本地SQL变量或者临时表，避免不必要的重复调用。但同时注意，不能使用CLR来缓存数据。因为程序集被加载到SQL SERVER后，时不允许储存静态或者共享状态对象的，除非你声明程序集为“Unsafe”。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
