SQL Server CLR系列(3)-发送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{    /// <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":"[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'    );

注意要把项目的安全性改成无限制