如何创建 SQL CLR 存储过程以从 Web 服务获取数据并将结果插入到 SQL Server 表中

我需要从我们的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 存储过程运行该导入，每晚批量清理数据，我们正在向现有进程添加一个新系统，供应商仅支持使用 Web 服务来收集我们需要的信息。我们正在努力保持我们的进口流程。虽然如前所述，我对更好的选择持开放态度，但一些方向将非常有帮助。

编辑 2：以下是当前流程工作原理的快速概述。

用户在网站上执行搜索
系统根据用户输入的参数搜索数据库的表(搜索是一个存储过程)。对于当前系统，使用计划的 SSIS 包和存储过程导入夜间源。我们需要添加到搜索中的新附加系统仅支持用于查询和提取数据的 Web 服务，并且我们需要从该系统保留更多实时结果。(在此步骤中，我们需要添加搜索功能以将信息提交到 Web 服务，并获取结果以与其他结果合并。
系统记录搜索结果以及各种业务逻辑计算的结果值。(数据的记录和保留是政府监管机构的要求，所以仅仅从网络服务中获取结果是不够的，我们需要它与我们的其他搜索结果一起登录。
系统向用户显示新登录的信息。

我

决定我应该把我的问题分解成更小的步骤，专注于每一步。

1) 从存储进程调用 CLR 存储过程，为 CLR 提供一些输入参数。

2.1)在CLR存储的Proc中构建所需的WebRequest XML。 2.2)在CLR存储过程中捕获WebResponse XML

3)将响应XML中我们需要的(解析的)数据元素发布到SQL中(或将整个XML发布到SQL中并从那里解析出来)。

对于步骤 1)我不确定如何使 CLR 存储过程接受输入参数 - 但我还没有搜索过这个，所以这仍然在我身上。

步骤 2.x)我相信我已经弄清楚了(下面的代码)

步骤3)我可以在如何解析响应 XML 方面提供帮助(下面的示例响应)

步骤 2 代码(发布到 Web 服务并捕获响应)：

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;
}
}
}

示例响应 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>

步骤 3(解析来自网络服务的 XML 响应)

我决定尝试在SQL中执行此操作(我在SQL中比C#强得多) 这是我想到的。但是，如果有人对如何在 C# 中执行此操作有很好的信息，我很乐意学习！

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在这种情况下应该没问题，只要您小心并了解在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 的阶梯。

此外，虽然不是一个免费选项，但如果您希望能够在不处理编码、找出最佳实践、脚本等的情况下进行这些 Web 调用，请查看 SQL# 的完整版本(我编写的)。完整版具有INET_GetWebPages允许发出网络请求。而且，它具有INET_GetConnectionLimitForURI、INET_GetCurrentConnectionCountForURI和INET_SetConnectionLimitForURI，允许您管理该 URI 连接限制并减少/避免该性能瓶颈。

步骤 2 代码(发布到 Web 服务并捕获响应)：

示例响应 XML

步骤 3(解析来自网络服务的 XML 响应)

相关内容

最新更新

热门标签：