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

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

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

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

解决方法

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

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

解决方法

猜你在找的MsSQL相关文章