从 SQL Server 2012 或 SQL CLR C# 发送 HTTP POST 请求 [英] Sending HTTP POST request from SQL Server 2012 or SQL CLR C#

问题描述

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

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

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

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 中.其中一些程序集不完全受支持(例如 System.Net.Http.dll)，给出如下警告:

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:

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

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.

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

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

我的存储过程的 CLR 代码:

CLR code for my stored procedure:

[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 可以工作，而无需使用不受支持的程序集:

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

[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 请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！