如何从 SQL Server 存储过程调用 webservice

2021-12-11 00:00:00 geocoding web-services sql-server-2008 sql-server sqlclr

我想知道如何使用 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 格式返回响应(除非您可以找到一个可以工作而无需设置为 的 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 地理编码 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方法(或在中实例化它)使用块)！！

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 集成":启用 CLR 集成
不要轻易走捷径，将数据库设置为TRUSTWORTHY ON.只需使用密码对程序集进行签名，然后通过指向您签名的 DLL 在 master 数据库中创建一个非对称密钥，然后从该非对称密钥创建一个登录名，最后授予该登录名 UNSAFE组装权限.然后你可以设置程序集WITH PERMISSION_SET = EXTERNAL_ACCESS.
不要像用户 3469363 提倡的那样使用 SP_OA* 程序.自 SQL Server 2005 以来，那些 OLE 自动化过程已被弃用，并且(希望)有一天(希望很快)被删除.它们的效率和安全性也低于 SQLCLR.
在我对 DBA.StackExchange 上类似问题的回答中可以找到更多注释:将 Web 服务数据引入 SQL 服务器

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

相关文章