Call Web API 2 method using c# CLR stored procedure by HTTPClient

Question

I have a WPF application that consumes services of MVC web API 2. Created RestClient wrapper using HTTPClient to call async methods such as PostAsync and GetAsync. For ex my POST method wrapper would be like:

using (var client = new HttpClient(new HttpClientHandler()
                                       { AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip }))
{
    var content = new FormUrlEncodedContent(postObject);

    SetupClient(client, methodName, apiUrl, postObject, headerContent);

    if (apiKey != null && appId != null)
        await SetAuthorizationHeader(client, methodName, apiUrl, appId, apiKey, content).ConfigureAwait(false);

    using (HttpResponseMessage response = Task.Run(() => client.PostAsync(apiUrl, content)).Result)
    {
        response.EnsureSuccessStatusCode();

        using (HttpContent httpContent = response.Content)
        {
            if (response.IsSuccessStatusCode)
            {
                result = response.Content.ReadAsAsync<T>().Result;
            }
        }
    }
}

Which is working fine. Right now I am trying to call some of the API calls through C# CLR stored procedure by creating SQL Server Database Project.

C# CLR stored procedure will be like :

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SQLRestClient(SqlString weburl, SqlString postBody, SqlString appIdString, SqlString apiKeyString, SecureString baseAddress, out SqlString returnval)
{
    string apiUrl = Convert.ToString(weburl);
    string baseAddressString = Convert.ToString(baseAddress);

    string result = string.Empty;
    var appId = ConvertToSecureString(Convert.ToString(appIdString));
    var apiKey = ConvertToSecureString(Convert.ToString(apiKeyString));

    try
    {
        string methodName = HttpMethod.Post.Method.ToUpper();

        using (var client = new HttpClient(new HttpClientHandler()
        {
            AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip
        }))
        {
            var content = new FormUrlEncodedContent(postObject);

            SetupClient(client, methodName, apiUrl, postObject, headerContent);

            if (apiKey != null && appId != null)
                await SetAuthorizationHeader(client, methodName, apiUrl, appId, apiKey, content).ConfigureAwait(false);

            using (HttpResponseMessage response = Task.Run(() => client.PostAsync(apiUrl, content)).Result)
            {
                response.EnsureSuccessStatusCode();

                using (HttpContent httpContent = response.Content)
                {
                    if (response.IsSuccessStatusCode)
                    {
                        result = response.Content.ReadAsStringAsync();
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        SqlContext.Pipe.Send(ex.Message.ToString());
    }

    returnval = result;
}

When I try to generate the DLL of this procedure I am getting build error. Its because the compiler is not recognizing Assembly references such as

System.Net.Http.dll

As I gone through this thread

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

I found a solution to use HttpWebRequest instead of HttpClient. Since I have been using HttpClient throughout my application I don't want to switch to HttpWebRequest.

Can anyone suggest any other way so that I can generate the CLR stored procedure dll by using HttpClient. Any help will be appreciated and thanks in advance.

Solomon Rutzky · Accepted Answer · 2017-06-14T15:23:28.983

No, HttpClient is found in System.Net.Http, and that library is not one of the supported .NET Framework libraries. You can add that library manually, but you shouldn't as it requires being set to UNSAFE and that will require setting the Database to TRUSTWORTHY ON since you don't have the MS signing Certificate. It also isn't guaranteed to work as SQLCLR only allows pure MSIL Assemblies; mixed-mode Assemblies will not load. And Assemblies that are currently pure MSIL can change to mixed in a .NET Framework update. If that happens for an unsupported framework library that you loaded, then your project stops working and you have to rewrite it to not use that library.

You also shouldn't be using async calls in SQLCLR. Those also require the Assembly to be marked as UNSAFE and are not a good idea in general in this environment.

The best, safest, most reliable option is to use HttpWebRequest and HttpWebResponse.

score 3 · Answer 2 · answered Jun 14 '17 at 14:18

Only a subset of the .net libraries are available by default on SQL server, which I think this page lists.

WebRequest is part of the System.Net namespace loaded by System.dll, which is why you can use it easily as part of a CLR, and why the solution you looked at probably uses it.

You can load additional assemblies into SQL for use by CLR's as described in the "Unsupported Libraries" section of the page. So I think in theory you could load whatever assemblies are required to use HttpClient, although you might find there are several if you want to use the extensions.

e.g. HttpClient is part of System.Net.Http.dll
and .PostAsJsonAsync is part of System.Net.Http.Formatting.dll

There are also potential security requirements for assemblies, that ideally they are signed etc., unless you disable those checks when you import the assemblies.

I did mess around with looking at this before, and although I didn't spend too much time trying to get HttpClient working, I ended up using WebRequest because it was much easier to not have to worry about deploying other assemblies outside of the one I was creating.

--
For reference, in case the link breaks, these are the libraries listed on the first link:

The libraries/namespaces supported by CLR integration in SQL Server are:

CustomMarshalers

Microsoft.VisualBasic

Microsoft.VisualC

mscorlib

System

System.Configuration

System.Data

System.Data.OracleClient

System.Data.SqlXml

System.Deployment

System.Security

System.Transactions

System.Web.Services

System.Xml

System.Core.dll

System.Xml.Linq.dll

Call Web API 2 method using c# CLR stored procedure by HTTPClient

2 Answers2