sqlserver使用clr调用ajax，在数据库内请求外部链接

文盲老顾

已于 2023-04-05 23:57:10 修改

阅读量552

点赞数 2

分类专栏： .net 数据库文章标签： clr ajax 外部链接 sql 函数

于 2019-09-11 14:52:48 首次发布

本文链接：https://blog.csdn.net/superwfei/article/details/100735416

版权

数据库同时被 2 个专栏收录

52 篇文章 13 订阅

订阅专栏

.net

26 篇文章 6 订阅

订阅专栏

第一步：使用vs新建一个类库项目，编写clr程序集

using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics.CodeAnalysis;
using System.IO;
using System.IO.Compression;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;

internal class AjaxResult
{
    private int _statusCode;
    private string _url;
    private string _html;
    public string Url
    {
        get
        {
            return _url;
        }
    }
    public string Html
    {
        get
        {
            return _html;
        }
    }
    public int StatusCode
    {
        get
        {
            return _statusCode;
        }
    }
    public AjaxResult(string url, string html, int statusCode)
    {
        _url = url;
        _html = html;
        _statusCode = statusCode;
    }
}
internal class AjaxIterator : IEnumerable
{
    private string _url;
    private string _method;
    private string _arguments;
    public AjaxIterator(string url, string method, string arguments)
    {
        _url = url;
        _method = method;
        _arguments = arguments;
    }
    public IEnumerator GetEnumerator()
    {
        Ajax ajax = new Ajax();
        ajax.Http(_url, _method.ToLower(), _arguments);
        string url = ajax.CurrentUrl;
        string html = ajax.Result;
        int statusCode = (int)ajax.StatusCode;
        yield return new AjaxResult(url, html, statusCode);
    }
}
public static partial class ajax
{
    [SqlFunction(FillRowMethodName = "FillAjaxRow", TableDefinition = "url nvarchar(500),html nvarchar(max),statusCode int")]
    public static IEnumerable Http(SqlString url, SqlString method, SqlString arguments)
    {
        return new AjaxIterator(url.Value, method.Value, arguments.Value);
    }
    [SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters")]
    public static void FillAjaxRow(object data, out SqlString url, out SqlString html, out SqlInt32 statusCode)
    {
        AjaxResult node = (AjaxResult)data;
        url = new SqlString(node.Url);
        html = new SqlString(node.Html);
        statusCode = new SqlInt32(node.StatusCode);
    }
}
public class Ajax
{
    private string _url = string.Empty;
    private string _result = "";
    private HttpStatusCode hsc = HttpStatusCode.OK;
    public string CurrentUrl
    {
        get
        {
            return _url;
        }
    }
    public string Result
    {
        get
        {
            return _result;
        }
    }
    public HttpStatusCode StatusCode
    {
        get
        {
            return hsc;
        }
    }
    private Encoding GetEncoding(string html)
    {
        if (Regex.IsMatch(html, @"(?<=<meta(?!\w)[^<>]*?)charset\s*=", RegexOptions.IgnoreCase))
        {
            string c = Regex.Match(html, @"(?<=<meta(?!\w)[^<>]*?charset\s*=\s*['""]?(?!['""]))\w[^\s'""/<>]*", RegexOptions.IgnoreCase).Value;
            try
            {
                Encoding en = Encoding.GetEncoding(Regex.Match(html, @"(?<=<meta(?!\w)[^<>]*?charset\s*=\s*['""]?(?!['""]))\w[^\s'""/<>]*", RegexOptions.IgnoreCase).Value);
                return en;
            }
            catch
            {
                return null;
            }
        }
        else
        {
            return null;
        }
    }
    public void Http(string url, string method, string QueryString)
    {
        string full_url = (("get" == method) ? ((QueryString.Length > 0) ? ((url.IndexOf("?") > 0) ? (url + "&" + QueryString) : (url + "?" + QueryString)) : url) : url);
        ServicePointManager.Expect100Continue = false;
        ServicePointManager.DefaultConnectionLimit = Int32.MaxValue;
        string html = "";
        HttpWebRequest http = (HttpWebRequest)WebRequest.Create(full_url);
        http.AllowAutoRedirect = true;
        http.UserAgent = "Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20100101 Firefox/11.0";
        http.Accept = "*/*";
        http.KeepAlive = false;
        http.Headers.Add("Accept-Encoding", "gzip, deflate");
        _url = url;
        if (method == "post")
        {
            http.Method = "POST";
            http.Accept = "application/json, text/javascript, */*";
            http.KeepAlive = true;
            byte[] pd = null;
            pd = new UTF8Encoding().GetBytes(QueryString);
            try
            {
                http.ContentType = "application/x-www-form-urlencoded";
                http.ContentLength = pd.Length;
                Stream ps = http.GetRequestStream();
                ps.Write(pd, 0, pd.Length);
                ps.Close();
                ps.Dispose();
            }
            catch (Exception ex)
            {
                _result = ex.Message;
                hsc = HttpStatusCode.BadRequest;
                return;
            }
        }
        try
        {
            HttpWebResponse hwr = (HttpWebResponse)http.GetResponse();
            hsc = hwr.StatusCode;
            Stream s = hwr.GetResponseStream();
            MemoryStream ms = new MemoryStream();
            string contentType = "normal";
            if (hwr.Headers.GetValues("Content-Encoding") != null)
            {
                string[] encd = hwr.Headers.GetValues("Content-Encoding");
                for (int i = 0; i < encd.Length; i++)
                {
                    if (encd[i] == "gzip")
                    {
                        contentType = "gzip";
                        break;
                    }
                    if (encd[i] == "deflate")
                    {
                        contentType = "deflate";
                        break;
                    }
                }
            }
            switch (contentType)
            {
                case "deflate":
                    DeflateStream ds = new DeflateStream(s, CompressionMode.Decompress);
                    ds.CopyTo(ms);
                    ds.Close();
                    ds.Dispose();
                    break;
                case "gzip":
                    GZipStream g = new GZipStream(s, CompressionMode.Decompress);
                    g.CopyTo(ms);
                    g.Close();
                    g.Dispose();
                    break;
                default:
                    s.CopyTo(ms);
                    break;
            }
            s.Close();
            s.Dispose();
            byte[] bt = ms.ToArray();
            ms.Close();
            ms.Dispose();
            html = Encoding.UTF8.GetString(bt);
            Encoding en_test = GetEncoding(html);
            if (en_test != null && en_test != Encoding.UTF8)
            {
                html = en_test.GetString(bt);
            }
            _result = html;
            hwr.Close();
        }
        catch (WebException ex)
        {
            if (ex.Response == null)
            {
                hsc = HttpStatusCode.BadRequest;
                _result = ex.Message;
                return;
            }
            hsc = ((HttpWebResponse)ex.Response).StatusCode;
            _result = ex.Message;
        }
    }
}

在这个类库随便命名了，编写好之后生成或发布

第二步：导入程序集

在sql server management studio里，找到你要操作的数据库 -> 可编程性 -> 程序集 -> 鼠标右键 -> 新建程序集

弹出上图所示的对话框，点浏览选择之前生成的dll文件

第三步：创建一个自定义函数，调用clr程序集

CREATE FUNCTION [dbo].[Ajax](@url [nvarchar](max), @method [nvarchar](max), @arguments [nvarchar](max))
RETURNS  TABLE (
	[url] [nvarchar](500) NULL,
	[html] [nvarchar](max) NULL,
	[statusCode] [int] NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [clr.ajax].[ajax].[Http]

这里，external name 之后的三个数据分别是[clr类库项目名].[clr类名].[方法名]

如图所示，我创建的clr项目名是clr.ajax，所以生成的dll是clr.ajax.dll，引用的方法就是[clr.ajax].[ajax].[Http]

第四步：设置数据库权限，允许进行外部访问

-- 设置clr enabled允许调用clr程序
sp_configure 'clr enabled',1
go
reconfigure 
go
-- 设置数据库允许方位外部
alter database [dbname] set trustworthy on
go
-- 修改程序集的设置，将权限集设置为外部访问
-- 通过sqlserver management studio的界面修改

这里的dbname就是你之前导入程序集的数据库了

第五步：使用自定义函数访问外部链接