如何从SQL Server存储过程调用Web服务 [英] How to invoke webservice from SQL Server stored procedure

问题描述

我想知道如何使用SQL Server存储过程中的Web服务.我有一个表，其中的列指定用户的城市和州.我想将此地址作为参数传递给Google Geocoding API Web服务，并获取经度和纬度.

I would like to know how to use a web service from a SQL Server stored procedure. I have a table in which the columns specify the city and state of the user. I want to pass this address as a parameter to Google Geocoding API web service and get the latitude and longitude.

我已经在c#中成功使用了此地理编码api.但是现在，我想在存储过程中使用它.

I have successfully made use of this geocoding api in c#. But now, I want to use it in a stored procedure.

有人可以建议如何完成此工作吗?还是请提供任何链接给我?

Can any one please suggest how to get this done? Or please provide me any links?

任何帮助将不胜感激！！

Any help will be appreciated!!

谢谢.

推荐答案

对于这样的事情，您不需要完整的Web服务实现.您可以使用SQLCLR(SQL Server的.NET集成)将请求提交到URL，以XML形式返回响应(除非您找到一个在 without 设置为UNSAFE的情况下可以正常工作的JSON库) )，然后解析该响应.

For something like this, you don't need a full web service implementation. You can use SQLCLR (SQL Server's .NET Integration) to submit the request to the URL, get the response back in XML (unless you can find a JSON library that will work without being set to UNSAFE), and then parse that response.

查看以下MSDN页面:

Look at the following MSDN pages:

• HttpWebRequest
• HttpWebResponse
• XmlDocument
• 要转义地址:
  • 如果您使用的是SQL Server 2005、2008或2008 R2，请使用 Uri.EscapeDataString ，因为它在.NET Framework v4.5之前可用
  • 如果您使用的是SQL Server 2012、2014或更高版本，则可以使用Uri.EscapeDataString，或者，如果服务器已更新到至少.NET Framework v4.5，则可以使用 WebUtility.UrlEncode
  • HttpWebRequest
  • HttpWebResponse
  • XmlDocument
  • To escape the address:
    • If you are using SQL Server 2005, 2008, or 2008 R2, then use Uri.EscapeDataString as it was available prior to .NET Framework v4.5
    • If you are using SQL Server 2012, 2014, or newer, then you can use either Uri.EscapeDataString or, if the server has been updated to at least .NET Framework v4.5, then you can alternatively use WebUtility.UrlEncode

    根据 Google Geocoding API文档，API URI格式应类似于以下内容:

    According to the Google Geocoding API documentation, the API URI should be formatted similarly to the following:

    https://maps.googleapis.com/maps/api/geocode/xml?address={EscapedAddress}&key={API_KEY}
    

    只需通过HttpWebRequest提交用这两个变量替换为其适当值的变量，然后调用HttpWebRequest.GetResponse，然后调用HttpWebResponse.GetResponseStream.并不要忘记调用HttpWebResponse的Close和Dispose方法(或在using块中实例化它)！

    Just submit that with those 2 variables substituted with their proper values via HttpWebRequest, then call HttpWebRequest.GetResponse, then call HttpWebResponse.GetResponseStream. And do not forget to call the Close and Dispose methods of HttpWebResponse (or instantiate it in a using block)!!

    其他说明:

    • 如果尚未完成，则必须(一次)在服务器级别启用"CLR集成":中的说明使用SP_OA *过程.从SQL Server 2005开始，那些OLE自动化过程已被弃用，并有望(有希望)在某天(希望很快)被删除.与SQLCLR相比，它们的效率和安全性也较低.
    • 在我对DBA.StackExchange上类似问题的回答中可以找到更多注释:将Web服务数据引入SQL Server
    • If not already done, you will have to enable (one time) "CLR Integration" at the server level: Enabling CLR Integration
    • Do not take the easy road and set the database to TRUSTWORTHY ON. Just sign the assembly with a password, then create an asymmetric key in the master database by pointing to your signed DLL, then create a login from that asymmetric key, and finally grant that login the UNSAFE ASSEMBLY permission. Then you can set the assembly WITH PERMISSION_SET = EXTERNAL_ACCESS.
    • Do not use the SP_OA* procedures as advocated by user3469363. Those OLE Automation procedures have been deprecated since SQL Server 2005 and will (hopefully) be removed someday (hopefully soon). They are also less efficient and less secure than SQLCLR.
    • Even more notes can be found in my answer to a similar question on DBA.StackExchange: Bringing web service data into SQL server

    这篇关于如何从SQL Server存储过程调用Web服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！