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 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是博主的最大动力!
posted @   vvull  阅读(201)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?
点击右上角即可分享
微信分享提示