SQL Server通过程序集调用WebAPI
1|0类库
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;
}
}
2|0数据库
2|1配置
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
2|2新建程序集
1|0方法一
1|0方法二
实际上就是通过方法一生成脚本
--USE [DB]
GO
CREATE ASSEMBLY [WebAPIHelper]
AUTHORIZATION [dbo]
FROM xx
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
2|3新建函数
--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
2|4调用
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)
__EOF__

本文作者:vvull
本文链接:https://www.cnblogs.com/vvull/p/17104082.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力!
本文链接:https://www.cnblogs.com/vvull/p/17104082.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?