c# - 从 SQL Server 2012 或 SQL CLR C# 发送 HTTP POST 请求

Question

从 SQL Server 2012 发送 HTTP 请求是否有普遍接受的标准方式？

我想做的是使用远程服务器处理搜索查询，然后将结果插入回 SQL Server 2012 数据库。远程服务器提供了一个接受 POST 请求和 JSON 内容的 web api。

我有一个可行的解决方案，但是需要将多个程序集加载到 SQL Server 中。其中一些程序集没有得到完全支持(例如 System.Net.Http.dll)，给出这样的警告:

Warning: The Microsoft .NET Framework assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

我想知道是否有更好/更安全的方法不需要加载所有这些程序集？

我的存储过程的 CLR 代码:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendSearchRequestProcedure (string query, string table)
{
    RunAsync(query,table).Wait();
}

static async Task RunAsync(string query, string table)
{
    using (var client = new HttpClient())
    {
        HttpResponseMessage response;

        client.BaseAddress = new Uri("http://localhost:9000/");
        client.DefaultRequestHeaders.Accept.Clear();
        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

        var search = new Search() { Query = query, Table = table };

        response = await client.PostAsJsonAsync("api/search/", search);

        if (!response.IsSuccessStatusCode)
        {
            // handle error
        }
    }
} 

Answer 1

就像 Joe 建议的那样，使用 HttpWebRequest 而不是 HttpClient 无需使用不受支持的程序集即可工作:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendRequest (string query, string table)
{
    string address = "http://localhost:9000/api/search";
    HttpWebRequest request = (HttpWebRequest) WebRequest.Create(address);
    request.ContentType = "application/json; charset=utf-8";
    request.Method = "POST";

    using (var streamWriter = new StreamWriter(request.GetRequestStream()))
    {
        string json = "{\"Query\":\""+query+"\",\"Table\":\""+table+"\"}";

        streamWriter.Write(json);
        streamWriter.Flush();
    }

    var httpResponse = (HttpWebResponse)request.GetResponse();
    using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
    {
        var result = streamReader.ReadToEnd();
    }
}