从SQL CLR调用web服务

本文关键字：web 服务 调用 CLR SQL | 更新日期: 2023-09-27 18:15:27

我有一个SQL Server 2012存储过程，返回一个表。我必须修改该SP以向返回的表添加一个附加值。不幸的是，附加价值来自于对web服务的调用。根据我的研究，我收集的主要方法是使用SQL中的OLE自动化过程(sp_OA…)或SQLCLR存储过程。考虑到sp_OA…过程运行时，单个返回值是一个VARCHAR(10)注册键，并且对服务的调用很少(每小时10到20次)，我猜SQLCLR方法是可行的。此外，web服务托管在我们的内部网上，外部世界无法访问。

有更好的方法来完成我需要的吗?更好意味着更高的性能，更好的安全性，更容易编码和维护

从SQL CLR调用web服务

请不要使用sp_OA* OLE自动化程序。它们似乎并没有被正式弃用，但是SQLCLR取代了OLE自动化过程和扩展存储过程。

是的，在SQLCLR中很容易做到这一点。你可以找到使用WCF的例子(如@CodeCaster的答案所示)或使用HttpWebRequest/HttpWebResponse(我在这个答案中有更多的信息:如何从SQL Server存储过程调用web服务)。另外，请注意，有时您还需要添加序列化程序集:在CLR集成中使用web服务和Xml序列化

编码与维护
Web服务提供了一个很好的API，但是如果您更改了结构，您将不得不重新编译和重新部署其中的至少一部分。假设交换的信息足够简单，我倾向于认为将其视为标准的web请求会增加很多灵活性。您可以创建一个通用的web请求函数(标量或TVF)，它接受参数和URI，构造正确格式的XML请求并将其发送到URI。然后，它获得响应并仅返回XML。这样就转移了一点责任，因为现在需要解析XML响应，而不是获取一个漂亮的对象。但是，XML很容易在SQL Server中解析，并且您可以在任何地方重用该函数。而且，如果要更新远程服务，更新存储过程以更改传递给Web服务的查询字符串和/或更改XML响应的解析是一个简单的ALTER PROCEDURE，应该易于测试。无需重新编译/重新部署SQLCLR程序集。


不管你想要一个多么"纯粹"的web服务调用，最重要的是，安全方面，要不要偷懒，转到TRUSTWORTHY ON(正如@CodeCaster回答的链接页面所示，不幸的是，互联网上的大多数其他例子)。确保此安全的正确方法是执行以下操作:

  • 签署您的组装
  • 在[master]数据库中，从程序集的DLL创建一个非对称密钥。
  • 同时，在[master]中，从非对称密钥
    • 创建一个登录
  • 授予您的新登录EXTERNAL ACCESS ASSEMBLY权限
  • 创建PERMISSION_SET为EXTERNAL_ACCESS的组件，不是 UNSAFE

查看详细信息:

  • 一般使用SQLCLR，请访问:SQLCLR Info
  • 使用模块签名，请访问:模块签名信息
  • 不使用TRUSTWORTHY ON，请阅读:请，请，请停止使用冒充，可信和跨数据库所有权链接

可以使用SQL CLR调用WCF服务。

如果你不想这样，你可以用c#写一个Windows Service来监视或轮询表的变化。根据您实现此服务的方式，对新记录的反应几乎是立即的。请参阅如何通知一个windows服务(c#)数据库表的变化(sql 2005)?

然后可以从c#执行服务调用，执行所需的工作并将结果存储在列中。

当您需要更多信息时，例如在交换过程中获得的额外变量，您可以引入一个新表来存储这些信息，以及您感兴趣的实际结果。

相关文章：