本文共 7900 字，大约阅读时间需要 26 分钟。

第一步：使用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, @"(?<=
   
    <>]*?)charset\s*=", RegexOptions.IgnoreCase))        {            string c = Regex.Match(html, @"(?<=
    
     <>]*?charset\s*=\s*['""]?(?!['""]))\w[^\s'""/<>]*", RegexOptions.IgnoreCase).Value;            try            {                Encoding en = Encoding.GetEncoding(Regex.Match(html, @"(?<=
     
      <>]*?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 CALLERAS 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',1goreconfigure go-- 设置数据库允许方位外部alter database [dbname] set trustworthy ongo-- 修改程序集的设置，将权限集设置为外部访问-- 通过sqlserver management studio的界面修改

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

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

自定义函数有三个参数，第一个参数是url，第二个是method，也就是谓词，比如get、post，第三个是参数集，上图已有示例了

如果使用过程中报错，请参考

----------------------------------

Hmm.....写这么个clr，可不是为了让数据库去玩采集，是为了偷懒！

什么时候用数据库去ajax呢？举几个例子：

静态页网站，当数据库更新后，用触发器或队列调用更新静态页的程序

第三方设置，当本地数据库修改设置后，同步到第三方时

token更新等

注意，可千万别再数据库里玩批量采集哦

转载地址：http://sxvxi.baihongyu.com/