json 从存储过程调用 Web Api 2 端点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36160076/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call a Web Api 2 endpoint from a stored procedure
提问by ff8mania
some legacy SQL stored procedure (SQL Server 2008) needs to get some information from a web service (Web Api 2) that I provided.
一些旧的 SQL 存储过程 (SQL Server 2008) 需要从我提供的 Web 服务 (Web Api 2) 中获取一些信息。
How to call the endpoint (in GET), retrieve the data (JSON format) and translatethe data received in a temporary table to be used in the rest of the sproc?
如何调用端点（在 GET 中）、检索数据（JSON 格式）并转换临时表中接收到的数据以用于 sproc 的其余部分？
Thanks
谢谢
回答by Vadim K.
The best way would be to create Used-defined CLR functionand call your Web API from there, so you can use full power of C# and .Net libraries to do web calls and parse Json. There is plenty of information on the internet about that. For example: https://blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/. This is not about WebAPI in particular, but you can get the idea from there.
最好的方法是创建使用定义的 CLR 函数并从那里调用您的 Web API，这样您就可以使用 C# 和 .Net 库的全部功能来进行 Web 调用和解析 Json。互联网上有很多关于这方面的信息。例如：https: //blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/。这不是特别关于 WebAPI，但您可以从那里获得想法。
Please note that it would require deployment of your custom assembly with CLR Function(s) to the SQL Server.
请注意，这需要将带有 CLR 函数的自定义程序集部署到 SQL Server。
Edit:
编辑：
There is a way to do it in TSQL using OLE Automation. See an example here, but it is is much harder, less documented and you will probably spend time inventing you own bicycle instead of using ready solutions from with CLR functions.
有一种方法可以使用OLE 自动化在 TSQL 中做到这一点。请参阅此处的示例，但它更难，文档更少，您可能会花时间发明自己的自行车，而不是使用 CLR 函数的现成解决方案。
回答by S.Mohamed Mahdi Ahmadian zadeh
You can do it easily without writing CLR :
您无需编写 CLR 即可轻松完成：
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText
Exec sp_OADestroy @Object
回答by Geraldo Diaz
Try using this CLR Stored procedure: SQL-APIConsumer
尝试使用此 CLR 存储过程：SQL-APIConsumer
exec [dbo].[APICaller_POST]
@URL = 'http://localhost:5000/api/auth/login'
,@BodyJson = '{"Username":"gdiaz","Password":"password"}'
