Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sending HTTP POST request from SQL Server 2012 or SQL CLR C#

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 image 846
dennis Avatar asked Feb 10 '15 15:02

dennis


People also ask

What is CLR SQL Server 2012?

SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft . NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.

What is CLR stored procedure in SQL Server?

What are the CLR Stored procedures. The CLR is a common language runtime, and the SQL Server stored procedures are a collection of the SQL Queries and the command logic. The stored procedures are compiled and stored in the database. The CLR stored procedures are the combination of the CLR and stored procedure.


1 Answers

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();
    }
}
like image 126
dennis Avatar answered Oct 12 '22 11:10

dennis