SQL Server CLR系列(3)-发送POST请求

最新推荐文章于 2024-07-29 10:08:29 发布

原创最新推荐文章于 2024-07-29 10:08:29 发布 · 2.3k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #c#

数据库专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何在SQL Server中通过CLR实现一个HTTP POST请求的自定义函数，使用C#编写代码，并提供了在Test.sql中测试请求的方法。

1.新建一个HTTP请求的自定义函数，C#代码如下：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Text;
using System.IO;

public partial class UserDefinedFunctions
{
    /// <summary>
    ///  发送Http请求
    /// </summary>
    /// <param name="url">请求地址</param>
    /// <param name="json">请求参数json格式</param>
    /// <param name="method">POST/GET</param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString HttpRequest(SqlString url, SqlString json, SqlString method)
    {
        string result = "";
        HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url.Value);
        req.Method = method.Value;
        req.ContentType = "application/x-www-form-urlencoded";

        #region 添加Post 参数
        byte[] data = Encoding.UTF8.GetBytes(json.Value);
        req.ContentLength = data.Length;
        using (Stream reqStream = req.GetRequestStream())
        {
            reqStream.Write(data, 0, data.Length);
            reqStream.Close();
        }
        #endregion

        HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
        Stream stream = resp.GetResponseStream();
        //获取响应内容  
        using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
        {
            result = reader.ReadToEnd();
        }
        return new SqlString(result);
    }
};

2.Test.sql中测试请求

SELECT
    dbo.HttpRequest(
        'http://localhost:8080/trtapi-web/trtsdms/inStock/orderBlock',
        'json={"erp_bilno":"+201709110001+","erp_refno":"","erp_vend":"2","erp_line":"2","erp_batch":"20170531","erp_works":"XP2","erp_store":"010  ","erp_matno":"C41-4-3","erp_quant":"2.33","erp_statu":"A","erp_qty":"0","erp_tkdat":"2017-05-31T14:49:34.740","erp_vddat":"2020-05-01T00:00:00","erp_units":"","erp_flag":"I","erp_makdat":"2017-05-31T00:00:00","erp_mark":"0","erp_mtype":"161","erp_check":"admin9","erp_receive":"哈哈哈","erp_chkdat":"2017-06-05T00:00:00"}',
        'POST'
    );