从SQL Server 2012或SQL CLR C＃发送HTTP POST请求

Is there a generally accepted standard way for sending HTTP requests from SQL Server 2012?

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

What I am trying to do is to use a remote server to process a search query and then insert the results back into the SQL Server 2012 database. The remote server offers a web api that accepts POST requests, with JSON content.

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

I have a working solution, which however requires to load several assemblies into SQL Server. Some of these assemblies are not fully supported (e.g. System.Net.Http.dll), giving a warning like this:

我有一个有效的解决方案,但需要将多个程序集加载到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.

警告:Microsoft .NET Framework程序集“system.net.http,version = 4.0.0.0,culture = neutral,publickeytoken = b03f5f7f11d50a3a,processorarchitecture = msil。”您正在注册未在SQL Server托管环境中进行全面测试,并且不受支持。将来,如果升级或维护此程序集或.NET Framework,则CLR集成例程可能会停止工作。有关更多详细信息,请参阅SQL Server联机丛书。

I was wondering if there is a better/safer way which does not require to load all these assemblies?

我想知道是否有更好/更安全的方式不需要加载所有这些组件?

CLR code for my stored procedure:

我的存储过程的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
        }
    }
} 

1 个解决方案

#1

Like Joe suggested using HttpWebRequest instead of HttpClient works without having to use unsupported assemblies:

就像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();
    }
}