通过HTTPClient使用c# CLR存储过程调用Web API 2方法

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:

我有一个WPF应用程序，它使用MVC web API 2的服务。使用HTTPClient创建RestClient包装器来调用异步方法，如PostAsync和GetAsync。例如，我的POST方法包装如下:

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.

这是工作正常。现在，我正在尝试通过创建SQL Server数据库项目，通过c# CLR存储过程调用一些API调用。

C# CLR stored procedure will be like :

c# CLR存储过程如下:

[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

当我尝试生成这个过程的DLL时，我得到了构建错误。这是因为编译器不识别诸如此类的程序集引用

System.Net.Http.dll

System.Net.Http.dll

As I gone through this thread

当我通过这条线

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

从SQL Server 2012或SQL CLR c#发送HTTP POST请求

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.

我找到了一个使用HttpWebRequest而不是HttpClient的解决方案。由于我在整个应用程序中一直使用HttpClient，所以我不想切换到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.

有谁能提出其他方法，以便我可以使用HttpClient生成CLR存储过程dll。如有任何帮助，我们将不胜感激。

2 个解决方案

#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.

不，HttpClient是在System.Net中找到的。而这个库不是受支持的。net框架库之一。您可以手动添加该库，但是不应该这样做，因为它要求将数据库设置为不安全的，这需要将数据库设置为可信的，因为您没有MS签名证书。它也不能保证作为SQLCLR工作，只允许纯MSIL程序集;混合模式程序集将不会加载。目前纯MSIL的程序集可以在. net框架更新中更改为混合。如果您加载了一个不受支持的框架库，那么您的项目就停止工作了，您必须重写它，以避免使用该库。

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.

您也不应该在SQLCLR中使用异步调用。这些也要求大会被标记为不安全的，在这种环境中一般来说不是一个好主意。

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

最好、最安全、最可靠的选项是使用HttpWebRequest和HttpWebResponse。

#2

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

默认情况下，SQL server上只有.net库的一个子集可用，我认为这个页面列出了这些。

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.

WebRequest是系统的一部分。Net命名空间，由系统加载。dll，这就是为什么你可以很容易地使用它作为CLR的一部分，以及为什么你看到的解决方案可能会使用它。

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.

您可以将附加的程序集加载到SQL*CLR使用，如页面的“不支持库”部分所述。因此，我认为在理论上，您可以加载使用HttpClient所需的任何程序集，但是如果您希望使用扩展，您可能会发现有几个程序集。

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

HttpClient是System.Net.Http的一部分。dll和。postasjsonasync是system . net . http . form .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.

我以前确实研究过这个问题，虽然我没有花太多时间尝试让HttpClient工作，但是我最终使用了WebRequest，因为不用担心在我创建的程序集之外部署其他程序集要容易得多。

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

SQL Server中CLR集成支持的库/名称空间如下:

• CustomMarshalers
• CustomMarshalers
• Microsoft.VisualBasic
• Microsoft.VisualBasic
• Microsoft.VisualC
• Microsoft.VisualC
• mscorlib
• mscorlib
• System
• 系统
• System.Configuration
• System.Configuration
• System.Data
• System.Data
• System.Data.OracleClient
• System.Data.OracleClient
• System.Data.SqlXml
• System.Data.SqlXml
• System.Deployment
• System.Deployment
• System.Security
• System.Security
• System.Transactions
• System.Transactions
• System.Web.Services
• System.Web.Services
• System.Xml
• System.Xml
• System.Core.dll
• System.Core.dll
• System.Xml.Linq.dll
• System.Xml.Linq.dll