SQL Server 发送Http请求 函数

1、开启Sql Server通讯配置

sp_configure 'show advanced options', 1;
go

RECONFIGURE;
go

sp_configure 'Ole Automation Procedures', 1;
go

RECONFIGURE;
go

EXEC sp_configure 'Ole Automation Procedures';
go

2、发送web请求存储过程

CREATE proc [dbo].[SP_HTTPRequest] 
      @URI varchar(2000) = '',      
      @methodName varchar(50) = '', 
      @requestBody varchar(8000) = '', 
      @responseText varchar(8000) output
as
PRINT 'Ln 16'
SET NOCOUNT ON
IF    @methodName = ''
BEGIN
      select FailPoint = 'Method Name must be set'
      return
END
PRINT 'Ln 23'
set   @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255) 
PRINT 'Ln 28'
EXEC @hResult = sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @objectID OUT
PRINT 'Ln 29'
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
                  source = @source, 
                  description = @desc, 
                  FailPoint = 'Create failed', 
                  MedthodName = @methodName 
      goto destroy 
      return
END
PRINT 'Ln 42'
-- open the destination URI with Specified method 
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Open failed', 
            MedthodName = @methodName 
      goto destroy 
      return
END
-- set request headers 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'application/json; charset=utf-8'
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return
END

declare @len int
set @len = len(@requestBody) 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len 
IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return
END

-- send the request 
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody 
IF    @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Send failed', 
            MedthodName = @methodName 
      goto destroy 
      return
END
declare @statusText varchar(1000), @status varchar(1000) 
-- Get status text 
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out

-- Get response text 
exec sp_OAGetProperty @objectID, 'responseText', @responseText out

IF @hResult <> 0 
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'ResponseText failed', 
            MedthodName = @methodName 
      goto destroy 
      return
END
select @status, @statusText, @methodName,@responseText
destroy: 
      exec sp_OADestroy @objectID 
SET NOCOUNT OFF
GO

3、调用存储过程【注意：2012数据库中，存储过程发送http【post】或【get】请求时，参数是url格式类型，不能传对象，有中文乱码新增一个 UrlEncode 函数】

/*get请求*/
DECLARE	@return_value int,
		@responseText varchar(8000)

EXEC	@return_value = [dbo].[SP_HTTPRequest]
		@URI = N'http://localhost:51398/api/roles',
		@methodName = N'GET',
		@requestBody = N'',
		@responseText = @responseText OUTPUT
SELECT @responseText as N'@responseText'
GO

/*2012数据库发送【post】或者【get】请求【注意：参数是url格式的形式传参】*/
DECLARE	@return_value int,
		@responseText varchar(8000)
EXEC	@return_value = [dbo].[SP_HTTPRequest]
		@URI = N'http://localhost:51398/api/roles/create?name=111',
        @URL = N'http://localhost:51398/api/roles/create?name='+dbo.UrlEncode('中文测试')
		@methodName = N'POST',
		@requestBody = N'',
		@responseText = @responseText OUTPUT

SELECT	@responseText as N'@responseText'
SELECT	'Return Value' = @return_value
GO

/*2017数据库发送【post】或者【get】请求*/
DECLARE	@return_value int,
		@responseText varchar(8000)

EXEC	@return_value = [dbo].[SP_HTTPRequest]
		@URI = N'http://localhost:51398/api/roles/create',
		@methodName = N'POST',
		@requestBody = N'{"name":"111"}',
		@responseText = @responseText OUTPUT

SELECT	@responseText as N'@responseText'
SELECT	'Return Value' = @return_value
GO

4、url参数编码，解决中文乱码问题

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[UrlEncode]
(
    @Param NVARCHAR(2000)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @HexStr VARCHAR(MAX)
    --Use system function to convert input string to hex string
    SET @HexStr = master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(MAX), @Param))
    --Remove the starting '0x'
    SET @HexStr = RIGHT(@HexStr, LEN(@HexStr)-2)
    --Declare required variables
    DECLARE @I INT, @Len INT
    DECLARE @Output VARCHAR(MAX), @S CHAR(4), @C CHAR(1)
    DECLARE @LoByte TINYINT, @HiByte TINYINT
    --Get length
    SET @Len=LEN(@HexStr)/4
    --Start with first character
    SET @I=0
    --Prepare the output string
    SET @Output=''
    WHILE @I<@Len
        BEGIN
        SET @S=SUBSTRING(@HexStr, @I*4 + 1, 4)
        IF RIGHT(@S, 2)='00'
            BEGIN
            --Try to convert 2 hex digits to char
            SET @LoByte = ASCII(SUBSTRING(@S, 2, 1)) - 48
            IF @LoByte>10 SET @LoByte = @LoByte - 39 --0x61'a'-> 10
            SET @HiByte = ASCII(SUBSTRING(@S, 1, 1)) - 48
            IF @HiByte>10 SET @HiByte = @HiByte - 39
            SET @C=CHAR(@LoByte + @HiByte * 16)
            --If it's a reserved character, don't encode
            IF @C LIKE '[A-Za-z0-9()''*-._! ]'
                SET @Output = @Output + @C 
            ELSE
                SET @Output = @Output + '%' + LEFT(@S, 2)
            END
        ELSE
            SET @Output = @Output + '%u' + RIGHT(@S, 2) + LEFT(@S, 2)
        --Move to next hex 
        SET @I = @I + 1
        END
    
RETURN @Output
END
GO

--select dbo.FN_URLDecode('%E5%B9%BF%E4%B8%9C%E7%9C%81%E7%8F%A0%E6%B5%B7%E5%B8%82%E4%BF%A1%E6%81%AF%E5%B7%A5%E7%A8%8B%E6%9C%89%E9%99%90%E5%85%AC%E5%8F%B8')
GO

5、Controller

/// <summary>
/// 2012数据库存储过程发送http请求，注意是[FromQuery][FromQuery][FromQuery]
/// System.Web.HttpUtility.UrlDecode(request.name, System.Text.Encoding.UTF8)
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
[HttpPost("create")]
public IActionResult Create([FromQuery] CreateUserRequest request)
{
  string aaa = "";
  string name = System.Web.HttpUtility.UrlDecode(request.name, System.Text.Encoding.UTF8);
  string sex = System.Web.HttpUtility.UrlDecode(request.sex, System.Text.Encoding.UTF8);
  string age = System.Web.HttpUtility.UrlDecode(request.age, System.Text.Encoding.UTF8);
  LogUtils.GetInstance().Info(request.name);
  return Ok();
}

/// <summary>
/// 2017数据库存储过程发送http请求，正常写就行
/// System.Web.HttpUtility.UrlDecode(request.name, System.Text.Encoding.UTF8)
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
[HttpPost("create")]
public IActionResult Create([FromBody] CreateUserRequest request)
{
  string aaa = "";
  string name = System.Web.HttpUtility.UrlDecode(request.name, System.Text.Encoding.UTF8);
  string sex = System.Web.HttpUtility.UrlDecode(request.sex, System.Text.Encoding.UTF8);
  string age= System.Web.HttpUtility.UrlDecode(request.age, System.Text.Encoding.UTF8);
  LogUtils.GetInstance().Info(request.name);
  return Ok();
}

*
*
*