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

我想要做的是使用远程服务器处理搜索查询,然后将结果插回到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
        }
    }
}

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!