如何创建 SQL CLR 存储过程以从 Web 服务获取数据并将结果插入到 SQL Server 表中 [英] How to create a SQL CLR stored procedure to get data from a web service and insert results into a SQL Server table
问题描述
我需要从我们的 SQL Server 向 Web 服务提交一些数据，然后将结果捕获到 SQL Server 表中.
到目前为止，我一直在考虑创建一个 SQL CLR (C#) 存储过程来接受一个/一些参数，然后调用 Web 服务并将结果捕获到 SQL Server 表中；但我对任何可能有效的途径持开放态度.
我的问题是:
我是在吠叫正确的树，还是有更好的方法?
假设SQL CLR存储过程是最好的方法；我在这方面对 C# 比较陌生，希望有人能帮助我指明正确的方向.有没有人有一些示例代码或指针?我试过做一些谷歌搜索，可以找到一些东西
任何帮助将不胜感激.
这里有一些技术细节.
- Web 服务托管在 IIS 8.5 Web 服务器上
- 我确实可以访问供应商提供的 WSDL 文件(如果有帮助的话)
- 我可以在以下任何一个上运行 SQL 进程:SQL Server 2008 R2、SQL Server 2012、SQL Server 2014
- 我使用的是 Visual Studio 2015 版
- Web 服务需要一个 XML 来提交，并以 XML 格式返回其结果
为了稍微澄清一下，我想到 SQL CLR 存储过程的原因是我们已经有一个运行该导入的 T-SQL 存储过程，每天晚上批量清理数据，我们正在添加一个现有流程的新系统，供应商仅支持使用网络服务来收集我们需要的信息.我们正在努力使我们的进口流程保持一致.尽管如上所述我对更好的选择持开放态度，但某些方向会非常有帮助.
编辑 2:这是当前流程如何工作的快速概览.
用户在网站上执行搜索
系统根据用户输入的参数搜索数据库的表(搜索是一个存储过程).对于当前系统，使用预定的 SSIS 包和存储过程导入每晚提要.我们需要添加到搜索中的新附加系统仅支持用于查询和提取数据的 Web 服务，并且我们需要保留来自该系统的更多实时结果.(在这一步中，我们需要添加搜索功能以将信息提交到 Web 服务并使结果与其他结果合并.)
系统记录搜索结果以及各种业务逻辑计算的结果值.(政府监管机构要求记录和保留数据，因此仅从网络服务中获取结果还不够，我们需要将其与其他搜索结果一起登录.)
系统向用户显示新记录的信息.
<小时>
我决定我应该将我的问题分解成更小的步骤并专注于每个步骤.
1) 从存储过程调用 CLR 存储过程，为 CLR 提供一些输入参数.
2.1) 在 CLR 中存储 Proc 构建所需的 WebRequest XML.2.2) 在CLR存储的proc中捕获WebResponse XML
3) 将响应 XML 中我们需要的(已解析的)数据元素发布到 SQL 中(或将整个 XML 发布到 SQL 中并从那里解析出来.?
对于第 1 步)，我不确定如何让 CLR 存储过程接受输入参数 - 但我还没有搜索过这个，所以我仍然在意.
步骤 2.x) 我相信我已经弄清楚了(下面的代码)
第 3 步)我可以使用有关如何解析响应 XML 的帮助(下面的示例响应)
第 2 步代码(发布到 WebService 并捕获响应):
使用系统；使用 System.Collections.Generic;使用 System.Linq；使用 System.Text;使用 System.Xml；使用 System.Threading.Tasks;使用 System.Web；使用 System.Net；使用 System.IO；命名空间 WebServiceParseResultsTest{课程计划{public static void Main(string[] args){string api_URL = "https://MySubDom.MyDomain.com/Services/SearchUtil/Search.aspx";字符串 api_usr = "abcdef0123456789";字符串 api_pas = "0123456789abcdef";string rec_typ = "C3";字符串rec_sta =笔"；string searchParam = "a0123456789z";string XMLPostString = "<?xml version=\"1.0\"?> <REQUEST> <LOGIN api_password = \"" + api_pas + "\" api_user_id = \"" + api_usr + "\"/>";XMLPostString += "<SEARCH_QUERY record_status = \" "+rec_sta+" \" record_type = \" "+rec_typ+" \" SearchSysNum = \""+searchParam+"\"/>";XMLPostString += "";Console.WriteLine("-----------------");Console.WriteLine(XMLPostString);Console.WriteLine("-----------------");WaitForEnterKey();Console.WriteLine(DateTime.Now + " ");string postResponse = PostXMLString(XMLPostString, api_URL);Console.WriteLine(DateTime.Now + " SERVER RESPONSE: " + postResponse);Console.WriteLine(DateTime.Now + " ");Console.WriteLine(DateTime.Now + "按 Enter 退出");WaitForEnterKey();}public static void WaitForEnterKey(){Console.WriteLine(DateTime.Now + "按 Enter 继续");while (Console.ReadKey().Key != ConsoleKey.Enter) { }}公共静态字符串 PostXMLFile(字符串文件名，字符串 uri){//创建一个请求并传递 URL 以接收帖子.WebRequest 请求 = WebRequest.Create(uri);//我们将请求的 Method 属性设置为 POST.request.Method = "POST";//我们创建 POST 方法发送的内容并将其转换为字节数组.string postData = GetTextFromXMLFile(fileName);//this.GetTextFromXMLFile(fileName);byte[] byteArray = Encoding.UTF8.GetBytes(postData);//我们将 WebRequest 的 ContentType 设置为 xml.request.ContentType = "text/xml";//我们设置了 WebRequest 的 ContentLength.request.ContentLength = byteArray.Length;//我们获得了请求流.流数据流 = request.GetRequestStream();//将数据写入请求流.dataStream.Write(byteArray, 0, byteArray.Length);//创建流对象.数据流.关闭();//-----HttpWebResponse 响应；response = (HttpWebResponse)request.GetResponse();如果(响应.StatusCode == HttpStatusCode.OK){流 responseStream = response.GetResponseStream();string responseStr = new StreamReader(responseStream).ReadToEnd();返回 responseStr;}返回空；//------}//结束:PostXMLFile公共静态字符串 PostXMLString(string XMLtext, string uri){//创建一个请求并传递 URL 以接收帖子.WebRequest 请求 = WebRequest.Create(uri);//我们将请求的 Method 属性设置为 POST.request.Method = "POST";//我们创建 POST 方法发送的内容并将其转换为字节数组.字符串 postData = XMLtext;byte[] byteArray = Encoding.UTF8.GetBytes(postData);//我们将 WebRequest 的 ContentType 设置为 xml.request.ContentType = "text/xml";//我们设置了 WebRequest 的 ContentLength.request.ContentLength = byteArray.Length;//我们获得了请求流.流数据流 = request.GetRequestStream();//将数据写入请求流.dataStream.Write(byteArray, 0, byteArray.Length);//创建流对象.数据流.关闭();//-----HttpWebResponse 响应；response = (HttpWebResponse)request.GetResponse();如果(响应.StatusCode == HttpStatusCode.OK){流 responseStream = response.GetResponseStream();string responseStr = new StreamReader(responseStream).ReadToEnd();返回 responseStr;}返回空；//------}//END: PostXMLString私有静态字符串 GetTextFromXMLFile(字符串文件){StreamReader reader = new StreamReader(file);字符串 ret = reader.ReadToEnd();reader.Close();返回 ret;}}}示例响应 XML
<SEARCH_RESULTS><RECORD record_type="CC"record_id="0123456789abcdef0123456789abcdef"record_num="987"记录状态=笔"record_date="8/11/2017 9:22:57 PM"u_name="TEST20"create_date="2/1/2017 6:15:49 AM"/></SEARCH_RESULTS></响应> 第 3 步(解析来自 WEBSERVICE 的 XML 响应)
我决定尝试在 SQL 中执行此操作(我在 SQL 方面比 C# 强得多)这是我想出的.但是如果有人有关于如何在 C# 中做到这一点的好信息，我很乐意学习！
声明 @vMyXML XMLSET @vMyXML = '<SEARCH_RESULTS><RECORD record_type="CC"record_id="0123456789abcdef0123456789abcdef"record_num="987"记录状态=笔"record_date="8/11/2017 9:22:57 PM"u_name="TEST20"create_date="2/1/2017 6:15:49 AM"/><RECORD record_type="BC"record_id="1234567890bcdefa1234567890bcdefa"record_num="879"记录状态=四月"record_date="8/12/2017 10:23:58 PM"u_name="TEST21"create_date="3/2/2017 7:16:50 AM"/></SEARCH_RESULTS></响应>'选择Attribs.value('@record_type' , 'nvarchar(10)') 作为 [record_type], Attribs.value('@record_id' , 'nvarchar(50)') 作为 [record_id], Attribs.value('@record_num' , 'int') as [record_num], Attribs.value('@record_status' , 'nvarchar(25)') 作为 [record_status], Attribs.value('@record_date' , 'DateTime') as [record_date], Attribs.value('@u_name' , 'nvarchar(75)') 作为 [u_name], Attribs.value('@create_date' , 'DateTime') as [create_date]INTO AA_TMP_MyTestXMLResultsFROM @vMyXML.nodes('/RESPONSE/SEARCH_RESULTS/RECORD') as myXML(Attribs)从 AA_TMP_MyTestXMLResults 中选择 *删除表 AA_TMP_MyTestXMLResults SQLCLR 在这种情况下应该没问题，只要您小心并意识到在作为 SQL Server 的 CLR 主机的高度受限环境中工作的各种细微差别(即SQLCLR)，即:
- 您不能使用本机 Web 服务代码.相反，您需要使用
HttpWebRequest和HttpWebResponse.而且，这意味着您需要手动生成请求 XML，然后自己解析 XML 响应(而不是取回 .NET 对象). - 为了避免大多数人在处理网络调用时提到的性能瓶颈，您需要通过
ServicePointManager为您正在连接的 URI 增加 URI 连接限制.默认限制为 2，并且上面的任何调用都将等到这 2 个连接之一完成.我正在撰写一篇博客文章，将详细解释这一点，并附有示例，一旦发布，我将在此处发布链接. - 需要将程序集设置为
EXTERNAL_ACCESS.为了做到这一点，不要将数据库设置为TRUSTWORTHY ON.相反，签署程序集，在master中从该程序集创建一个非对称密钥，从该非对称密钥创建一个登录，最后授予该登录EXTERNAL ACCESS ASSEMBLY权限.
有关使用 SQLCLR 的更多信息，请参阅我在 SQL Server Central 上撰写的关于此主题的系列(需要免费注册才能阅读其内容):通往 SQLCLR 的阶梯.
此外，虽然不是免费选项，但如果您希望能够在无需处理编码、找出最佳实践、脚本编写等的情况下进行这些网络调用，请查看 SQL#(我写的).完整版具有 INET_GetWebPages，允许发出 Web 请求.而且，它具有 INET_GetConnectionLimitForURI、INET_GetCurrentConnectionCountForURI 和 INET_SetConnectionLimitForURI，可让您管理该 URI 连接限制并减少/避免该性能瓶颈.>
I need to submit some data to a web service from our SQL Server then capture the results into a SQL Server table.
Up to this point I have been looking at creating a SQL CLR (C#) stored procedure to accept a/some parameter(s) and then call the web service and capture the results into a SQL Server table; But I am open to any avenues that might work.
My questions are these:
Am I barking up the right tree so-to-speak, or is there a better way?
Assuming a SQL CLR stored procedure is the best way; I am relatively new to C# in this capacity and was hoping someone could help point me in the right direction. Does anyone have some example code or pointers? I've tried doing some Google searches and could find a few things
Any help would be greatly appreciated.
Here's a few technical details.
- The web service is hosted on an IIS 8.5 Web Server
- I do have access to a WSDL file the vendor provided (if that helps)
- I could run the SQL processes on any of the following: SQL Server 2008 R2, SQL Server 2012, SQL Server 2014
- I am using Visual Studio version 2015
- The web service both expects an XML for submitting, and returns its results in XML
Edit: for a small bit of added clarification the reason I was thinking of a SQL CLR stored procedure is that we already have a T-SQL stored procedure running that import, cleans up data in batch each night, we are adding a new system to the existing process and the vendor only supports using web service to gather the information we need . We are trying to keep our import processes together. Though as mentioned I am open to better alternatives, some direction would be exceptionally helpful.
Edit 2: Here's a quick overview of how the current process works.
A user performs a search on a website
System searches the tables of the database according to the parameters the user input (the search is a stored procedure). For current systems a nightly feed is imported using a scheduled SSIS package and stored procedures. The new additional system we are being required to add into the search only supports web-services for querying and extracting data, and we are being required to keep more real-time results from this system. (It is this step where we need to add the search capability to submit info to the web-service and get the results to merge in with the other results.)
System logs the results of the search, and resulting values from various business logic calculations. (the logging & retention of data is required by gov't regulators, so it's not good enough to just get the results from the web-service, we need it logged in with our other search results. )
System shows user the newly logged information.
I've decided I should have broken my question out into smaller steps and focus on each step.
1) Call a CLR Stored Proc from a Stored Proc, feeding the CLR some input paramaters.
2.1) in the CLR stored Proc build the WebRequest XML needed. 2.2) in the CLR stored proc capture the WebResponse XML
3) Post the (parsed) data elements we need from the response XML into SQL (or post the whole XML into SQL and parse it out from there. ?
For Step 1) I'm unsure how to make a CLR Stored Proc accept input parameters - but I haven't searched this yet so that's on me still.
Step 2.x) I believe I have this figured out (code below)
Step 3) I could use help with how to parse the response XML (example response below)
STEP 2 CODE (Posting to WebService and capturing Response):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Threading.Tasks;
using System.Web;
using System.Net;
using System.IO;
namespace WebServiceParseResultsTest
{
class Program
{
public static void Main(string[] args)
{
string api_URL = "https://MySubDom.MyDomain.com/Services/SearchUtil/Search.aspx";
string api_usr = "abcdef0123456789";
string api_pas = "0123456789abcdef";
string rec_typ = "C3";
string rec_sta = "PEN";
string searchParam = "a0123456789z";
string XMLPostString = "<?xml version=\"1.0\"?> <REQUEST> <LOGIN api_password = \"" + api_pas + "\" api_user_id = \"" + api_usr + "\" />";
XMLPostString += "<SEARCH_QUERY record_status = \" "+rec_sta+" \" record_type = \" "+rec_typ+" \" SearchSysNum = \""+searchParam+"\" />";
XMLPostString += "</REQUEST>";
Console.WriteLine("-----------------");
Console.WriteLine(XMLPostString);
Console.WriteLine("-----------------");
WaitForEnterKey();
Console.WriteLine(DateTime.Now + " <NN_Test_XML_POST>");
string postResponse = PostXMLString(XMLPostString, api_URL);
Console.WriteLine(DateTime.Now + " SERVER RESPONSE: " + postResponse);
Console.WriteLine(DateTime.Now + " </NN_Test_XML_POST>");
Console.WriteLine(DateTime.Now + " Press Enter to Exit");
WaitForEnterKey();
}
public static void WaitForEnterKey()
{
Console.WriteLine(DateTime.Now + " Press Enter to Continue");
while (Console.ReadKey().Key != ConsoleKey.Enter) { }
}
public static string PostXMLFile(string fileName, string uri)
{
// Create a request and pass the URL to receive the post.
WebRequest request = WebRequest.Create(uri);
// We set the Method property of the request to POST.
request.Method = "POST";
// We create what is being sent by the POST method and convert it to byte array.
string postData = GetTextFromXMLFile(fileName);
//this.GetTextFromXMLFile(fileName);
byte[] byteArray = Encoding.UTF8.GetBytes(postData);
// We set the ContentType of the WebRequest to xml.
request.ContentType = "text/xml";
// We set the ContentLength of the WebRequest.
request.ContentLength = byteArray.Length;
// We get the request stream.
Stream dataStream = request.GetRequestStream();
// write the data to the request stream.
dataStream.Write(byteArray, 0, byteArray.Length);
// create the Stream object.
dataStream.Close();
//-----
HttpWebResponse response;
response = (HttpWebResponse)request.GetResponse();
if (response.StatusCode == HttpStatusCode.OK)
{
Stream responseStream = response.GetResponseStream();
string responseStr = new StreamReader(responseStream).ReadToEnd();
return responseStr;
}
return null;
//------
} // END: PostXMLFile
public static string PostXMLString(string XMLtext, string uri)
{
// Create a request and pass the URL to receive the post.
WebRequest request = WebRequest.Create(uri);
// We set the Method property of the request to POST.
request.Method = "POST";
// We create what is being sent by the POST method and convert it to byte array.
string postData = XMLtext;
byte[] byteArray = Encoding.UTF8.GetBytes(postData);
// We set the ContentType of the WebRequest to xml.
request.ContentType = "text/xml";
// We set the ContentLength of the WebRequest.
request.ContentLength = byteArray.Length;
// We get the request stream.
Stream dataStream = request.GetRequestStream();
// write the data to the request stream.
dataStream.Write(byteArray, 0, byteArray.Length);
// create the Stream object.
dataStream.Close();
//-----
HttpWebResponse response;
response = (HttpWebResponse)request.GetResponse();
if (response.StatusCode == HttpStatusCode.OK)
{
Stream responseStream = response.GetResponseStream();
string responseStr = new StreamReader(responseStream).ReadToEnd();
return responseStr;
}
return null;
//------
} //END: PostXMLString
private static string GetTextFromXMLFile(string file)
{
StreamReader reader = new StreamReader(file);
string ret = reader.ReadToEnd();
reader.Close();
return ret;
}
}
}
EXAMPLE RESPONSE XML
<RESPONSE version="1.3">
<SEARCH_RESULTS>
<RECORD record_type="CC"
record_id="0123456789abcdef0123456789abcdef"
record_num="987"
record_status="PEN"
record_date="8/11/2017 9:22:57 PM"
u_name="TEST20"
create_date="2/1/2017 6:15:49 AM"
/>
</SEARCH_RESULTS>
</RESPONSE>
Step 3 (Parsing XML RESPONSE FROM WEBSERVICE)
I decided to try and do this in SQL (I'm much stronger in SQL than C#) Here's what I came up with. But if anyone has good information on how to do this in C# I'd love to learn!
DECLARE @vMyXML XML
SET @vMyXML = '<RESPONSE version="1.3">
<SEARCH_RESULTS>
<RECORD record_type="CC"
record_id="0123456789abcdef0123456789abcdef"
record_num="987"
record_status="PEN"
record_date="8/11/2017 9:22:57 PM"
u_name="TEST20"
create_date="2/1/2017 6:15:49 AM"
/>
<RECORD record_type="BC"
record_id="1234567890bcdefa1234567890bcdefa"
record_num="879"
record_status="APR"
record_date="8/12/2017 10:23:58 PM"
u_name="TEST21"
create_date="3/2/2017 7:16:50 AM"
/>
</SEARCH_RESULTS>
</RESPONSE>'
SELECT
Attribs.value('@record_type' , 'nvarchar(10)') as [record_type]
, Attribs.value('@record_id' , 'nvarchar(50)') as [record_id]
, Attribs.value('@record_num' , 'int') as [record_num]
, Attribs.value('@record_status' , 'nvarchar(25)') as [record_status]
, Attribs.value('@record_date' , 'DateTime') as [record_date]
, Attribs.value('@u_name' , 'nvarchar(75)') as [u_name]
, Attribs.value('@create_date' , 'DateTime') as [create_date]
INTO AA_TMP_MyTestXMLResults
FROM @vMyXML.nodes('/RESPONSE/SEARCH_RESULTS/RECORD') as myXML(Attribs)
Select * from AA_TMP_MyTestXMLResults
DROP TABLE AA_TMP_MyTestXMLResults
SQLCLR should be ok in this scenario, as long as you are careful and aware of the various nuances of working in the highly restricted environment that is SQL Server's CLR host (i.e. SQLCLR), namely:
- You cannot use native web service code. Instead, you need to use
HttpWebRequestandHttpWebResponse. And, that means that you need to generate the request XML by hand, and then parse the XML response yourself (as opposed to getting back a .NET object). - In order to avoid the performance bottle neck that most people refer to when dealing with networking calls, you need to increase the URI Connection Limit via
ServicePointManagerfor the URI you are connecting to. The default limit is 2 and any calls above that will wait until one of those 2 connections completes. I am working on a blog post that will explain this in detail, with examples, and I will post the link here once it is published. - The Assembly will need to be set to
EXTERNAL_ACCESS. In order to do that, do not set the Database toTRUSTWORTHY ON. Instead, sign the Assembly, create an Asymmetric Key inmasterfrom that Assembly, create a Login from that Asymmetric Key, and finally grant that Login theEXTERNAL ACCESS ASSEMBLYpermission.
For more information on working with SQLCLR in general, please see the series I am writing on this topic on SQL Server Central (free registration is required to read their content): Stairway to SQLCLR.
Also, while not a free option, if you want the ability to make these web calls without having to deal with the coding, figuring out best practices, scripting, etc, then take a look at the Full version of SQL# (which I wrote). The Full version has INET_GetWebPages which allows for making web requests. And, it has INET_GetConnectionLimitForURI, INET_GetCurrentConnectionCountForURI, and INET_SetConnectionLimitForURI which allow you to manage that URI connection limit and reduce/avoid that performance bottleneck.
这篇关于如何创建 SQL CLR 存储过程以从 Web 服务获取数据并将结果插入到 SQL Server 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
