第一步:使用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就是你之前导入程序集的数据库了
第五步:使用自定义函数访问外部链接
自定义函数有三个参数,第一个参数是url,第二个是method,也就是谓词,比如get、post,第三个是参数集,上图已有示例了
如果使用过程中报错,请参考SQL Server 2005 CLR 调用Web Service需要注意的几个问题
----------------------------------
Hmm.....写这么个clr,可不是为了让数据库去玩采集,是为了偷懒!
什么时候用数据库去ajax呢?举几个例子:
静态页网站,当数据库更新后,用触发器或队列调用更新静态页的程序
第三方设置,当本地数据库修改设置后,同步到第三方时
token更新等
注意,可千万别再数据库里玩批量采集哦