SQL Server通过程序集调用WebAPI

类库

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Security;
using System.Text;
using System.Threading.Tasks;

public class WebAPIHelper
{
    [SqlFunction]
    public static string Call(string url, string method, string token, string pms)
    {
        if (method.ToUpper() == "GET")
        {
            return Get(url, token);
        }
        else
        {
            return Post(url, pms, token);
        }

    }

    private static string Post(string URL, string model, string token = "")
    {
        HttpWebRequest request = null;
        if (URL.ToLowerInvariant().StartsWith("https"))
        {
            request = WebRequest.Create(URL) as HttpWebRequest;
            ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback((sender, c, ch, ss) => { return true; });
            request.ProtocolVersion = HttpVersion.Version11;
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;
            request.KeepAlive = true;
            ServicePointManager.CheckCertificateRevocationList = true; ServicePointManager.DefaultConnectionLimit = 100;
            ServicePointManager.Expect100Continue = false;
        }
        else
        {
            request = WebRequest.Create(URL) as HttpWebRequest;
        }

        request.Method = "POST";
        request.ContentType = "application/json";
        request.Accept = "*/*";
        if (!string.IsNullOrEmpty(token))
        {
            request.Headers.Add("Authorization", "Bearer " + token);
        }
        Encoding Encoding = Encoding.UTF8;
        //string loginJSON = JsonConvert.SerializeObject(model);//序列化
        string loginJSON = model;
        using (var requestStream = request.GetRequestStream())
        {
            var bytes = Encoding.GetBytes(loginJSON);
            requestStream.Write(bytes, 0, bytes.Length);
        }
        var WebResponse = request.GetResponse() as HttpWebResponse;

        var Stream = WebResponse.GetResponseStream();
        StreamReader SR = new StreamReader(Stream);
        string s = SR.ReadToEnd();
        return s;
    }

    private static string Get(string URL, string token = "")
    {
        HttpWebRequest request = null;
        if (URL.ToLowerInvariant().StartsWith("https"))
        {
            request = WebRequest.Create(URL) as HttpWebRequest;
            ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback((sender, c, ch, ss) => { return true; });
            request.ProtocolVersion = HttpVersion.Version11;
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;
            request.KeepAlive = true;
            ServicePointManager.CheckCertificateRevocationList = true; ServicePointManager.DefaultConnectionLimit = 100;
            ServicePointManager.Expect100Continue = false;
        }
        else
        {
            request = WebRequest.Create(URL) as HttpWebRequest;
        }

        request.Method = "GET";
        request.ContentType = "application/json";
        request.Accept = "*/*";
        if (!string.IsNullOrEmpty(token))
        {
            request.Headers.Add("Authorization", "Bearer " + token);
        }
        Encoding Encoding = Encoding.UTF8;
        var WebResponse = request.GetResponse() as HttpWebResponse;

        var Stream = WebResponse.GetResponseStream();
        StreamReader SR = new StreamReader(Stream);
        string s = SR.ReadToEnd();
        return s;
    }

}

数据库

配置

exec sp_configure 'show advanced options', '1';
reconfigure;
go
exec sp_configure 'clr enabled', '1'
reconfigure;
go
alter database [DB] set trustworthy on;	--The database for storing the assembly
reconfigure;
go
exec sp_configure 'show advanced options', '0';
reconfigure;
go

新建程序集

方法一

方法二

实际上就是通过方法一生成脚本

--USE [DB]
GO
CREATE ASSEMBLY [WebAPIHelper]
AUTHORIZATION [dbo]
FROM xx
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

新建函数

--drop function [dbo].[fn_CallWebAPI]
create function [dbo].[fn_CallWebAPI]
(
	@url nvarchar(max)		--URL of the API
	, @method nvarchar(max)		--POST/GET
	, @token nvarchar(max)		--Credential	
	, @pms nvarchar(max)		--Parameters in JSON format
)
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [WebAPIHelper].[WebAPIHelper].[Call]
GO																					  

调用

declare @pms nvarchar(max);
--set @pms='{"Username":"Test","Password":"Test"}'   --参数
set @pms = (select 'Test' as 'Username', 'Test' as 'Password' for json path, without_array_wrapper);
select [dbo].[fn_CallWebAPI]('http://xxx/GetToken', 'POST', '', @pms)