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

问题描述

是否有从SQL Server 2012？

发送HTTP请求一个普遍接受的标准方法

我所试图做的是使用远程服务器处理搜索查询，然后将结果插回的SQL Server 2012数据库。远程服务器提供接受POST请求的Web API，使用JSON内容。

我有一个工作解决方案，但需要几集加载到SQL Server。其中一些组件不完全支持（例如System.Net.Http.dll），使这样的警告：

  

警告：Microsoft .NET框架集system.net.http，版本= 4.0.0.0，文化=中性公钥= b03f5f7f11d50a3a，的ProcessorArchitecture = MSIL。您正在注册没有在SQL Server的托管环境中经过全面测试，并且不支持。今后，如果您升级或维修该组件或.NET Framework，您的CLR集成例程可能会停止工作。详情请参阅SQL Server联机丛书的更多细节。

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

CLR code为我的存储过程：

  [Microsoft.SqlServer.Server.SqlProcedure]
公共静态无效SendSearchRequestProcedure（查询字符串，字符串表）
{
    RunAsync（查询表）.Wait（）;
}静态异步任务RunAsync（查询字符串，字符串表）
{
    使用（VAR的客户=新的HttpClient（））
    {
        HTT presponseMessage响应;        client.BaseAddress =新的URI（HTTP：//本地主机：9000 /）;
        client.DefaultRequestHeaders.Accept.Clear（）;
        client.DefaultRequestHeaders.Accept.Add（新MediaTypeWithQualityHeaderValue（应用/ JSON））;        VAR搜索=新搜索（）{查询=查询，表=表};        响应=等待client.PostAsJsonAsync（API /搜索/，搜索）;        如果（！response.IsSuccessStatus code）
        {
            //处理错误
        }
    }
}
 

解决方案

就像乔使用建议的HttpWebRequest 而不是的HttpClient 工作，而无需使用不支持的组件：

  [Microsoft.SqlServer.Server.SqlProcedure]
公共静态无效sendRequest将（查询字符串，字符串表）
{
    字符串的地址=HTTP：//本地主机：9000 / API /搜索;
    HttpWebRequest的要求=（HttpWebRequest的）WebRequest.Create（地址）;
    request.ContentType =应用/ JSON的;字符集= UTF-8;
    request.Method =POST;    使用（VAR的StreamWriter =新的StreamWriter（request.GetRequestStream（）））
    {
        JSON字符串={\\查询\\：\\+查询+\\，\\表\\：\\+表+\\};        streamWriter.Write（JSON）;
        streamWriter.Flush（）;
    }    VAR HTT presponse =（HttpWebResponse）request.GetResponse（）;
    使用（VAR的StreamReader =新的StreamReader（HTT presponse.GetResponseStream（）））
    {
        VAR的结果= streamReader.ReadToEnd（）;
    }
}
 

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

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.

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:

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 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
        }
    }
} 

解决方案

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屋！