在SQL SERVER 2005中调用Web Service

翻译 2006年05月25日 16:20:00

SQL SERVER 2005中调用Web Service

原文:Nico Jacobs

出处:U2U nv/sa http://www.u2u.net/Article.aspx?ART=WebServicesinSQL05

改编,翻译:刘凡 liufan76@msn.com

(译者注:本人根据VS2005 RTM版和SQL SERVER 2005对涉及到Beta环境的步骤说明进行了调整。根据http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx和实际测试,在VS 2005 RTM版中不需要采用手工的方式生成WebService代理类。)

 

介绍

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.NETSQL 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#

选择“生成”标签,在底部选择“生成序列化程序集”为“开”。

VB.NET

选择“编译”标签,点击“高级”选项,在窗口底部选择“生成序列化程序集”为“开”。

同时,在项目属性窗口中,我们设置权限等级:在数据库标签中,设置“权限级别”为“外部”。这样,我们声明这个程序集是“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”,这个程序集不进行外部通信。选择确定后,刷新“程序集”文件夹,可以看到刚才新建的程序集。

再次运行刚才的TSQL语句:它已经能够成功运行了,提供汇率的Web Service能够被整合到CLR的函数直接调用了。

 

Web Services和效率

最后叮嘱:慎重使用类似的WebService调用。依赖网络通信的调用,速度较慢。所以如果你要将这些功能处理大数据量的表时,要非常慎重。测试一下,使用下面的查询,获取所有的欧元和美元数据(504 rows, AdventureWorks database):

select StandardCost, dbo.EUR2USD(StandardCost) from Production.Product

大概需要444秒才能完成查询因为需要调用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”。

 

Dr. Nico Jacobs

U2U (www.u2u.net)组织的培训师和顾问. 关注Microsoft SQL Server ADO.NET领域. 可以通过nico@u2u.bereach Nico联系。

 

相关文章推荐

SQL Server 2005 Service Broker 初探

Microsoft SQL Server 2005 Transact SQL (T-SQL) 语言 摘要:本文概述了如何利用 SQL Server 2005 中新增的 Service Broker...

VS2005+JQuery 调用Web service

最近学习Jquery 调用Web service遇到些问题,最后解决。 写出来希望对大家有帮助。   step1:打Ajax补丁:ASPAJAXExtSetup 可以在网上下载很多,下载后解压缩...
  • cheug
  • cheug
  • 2012-07-20 08:59
  • 668

SQL Server 2005中设置Reporting Services发布web报表的匿名访问

SQL Server 2005中设置Reporting Services发布web报表的匿名访问        一位朋友提出个问题:集成到SQL Server 2005中的Reporting Serv...

Web.Config和Sql Server2005连接字符串总结

转自http://blog.csdn.net/Sunboy_2050/archive/2009/12/04/4942616.aspx  SQL Native Client ODBC Driver  ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)