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

2019年09月11日 14:52:48 文盲老顾阅读数 12 文章标签： clr ajax 外部链接 sql 函数更多

分类专栏： .net clr sql

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

第一步：使用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更新等

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