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.

Answer 1

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.