SQLServerCLR发送POST请求的代码实例

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
{
    /// 
    ///  发送Http请求
    /// 
    ///请求地址
    ///请求参数json格式
    ///POST/GET
    /// 
    [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(
        'https://localhost:8080/trtapi-web/trtsdms/inStock/orderBlock',
        'json={"erp_bilno":"[phone]+","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'
    );