C#代码:
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
using System;
using System.Collections;
using System.Net;
using System.IO;
using System.Text;
namespace SqlClr
{
/// <summary>
/// Author : yenange
/// Date : 2014-10-31
/// Description: 在SQL Server环境中执行的CLR方法,注意提供给SQL Server调用的方法必须有SqlFunction/SqlProcedure Attribute
/// </summary>
public sealed class SqlCLR
{
#region [函数]
/// <summary>
/// 判断字符串是否匹配正则表达式
/// </summary>
/// <param name="source">要匹配的文本</param>
/// <param name="pattern">进行匹配的正则表达式</param>
/// <param name="options">正则表达式匹配选项,1为忽略大小写,2为多行匹配,3为忽略大小写且多行匹配</param>
/// <returns></returns>
[SqlFunction(IsDeterministic = true)]
public static SqlBoolean IsMatch(string source, string pattern, int options)
{
if (string.IsNullOrEmpty(source) || string.IsNullOrEmpty(pattern))
{
return SqlBoolean.False;
}
RegexOptions regexOptions = RegexOptions.None;
int optionIgnoreCase = 1;
int optionMultiline = 2;
if ((options & optionIgnoreCase) != 0)
{
regexOptions = regexOptions | RegexOptions.IgnoreCase;
}
if ((options & optionMultiline) != 0)
{
regexOptions = regexOptions | RegexOptions.Multiline;
}
return (SqlBoolean)(Regex.IsMatch(source, pattern, regexOptions));
}
/// <summary>
/// 判断是否为中文
/// </summary>
/// <param name="source"></param>
/// <returns></returns>
[SqlFunction]
public static SqlBoolean IsChinese(string source)
{
if (string.IsNullOrEmpty(source) || source.Trim() == string.Empty)
{
return false;
}
source = source.Trim();
bool r = System.Text.RegularExpressions.Regex.IsMatch(source, @"[\u4e00-\u9fa5]+$");
return (SqlBoolean)r;
}
/// <summary>
/// 根据url获取html
/// </summary>
/// <param name="url">url</param>
/// <returns></returns>
[SqlFunction(IsDeterministic = true)]
public static string Fun_GetHTML(string url)
{
string html = string.Empty;
html = GetAccess(url, "");
return html;
}
/// <summary>
/// "GET"
/// </summary>
/// <param name="url">web url</param>
/// <param name="Referer">web referer</param>
/// <returns>return the web access result </returns>
public static string GetAccess(string url, string Referer)
{
try
{
HttpWebRequest res = (HttpWebRequest)WebRequest.Create(url);
CookieContainer mycookiecontainer = new CookieContainer();
res.CookieContainer = mycookiecontainer;
res.Method = "GET";
//res.Proxy = null;
ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };
ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3;
//ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback();
res.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8";
//res.Headers.Add("Accept-Encoding", "gzip, deflate");
res.Headers.Add("Accept-Language", "zh-cn,zh;q=0.8,en-us;q=0.5,en;q=0.3");
//res.KeepAlive = false;
res.ProtocolVersion = HttpVersion.Version11;
if (Referer != "")
{
res.Referer = Referer;
}
res.UserAgent = "Mozilla/5.0 (Windows NT 5.1; rv:30.0) Gecko/20100101 Firefox/30.0";
using (HttpWebResponse resp = (HttpWebResponse)res.GetResponse())
{
//resp.Cookies = Form1.mycookiecontainer.GetCookies(res.RequestUri);
using (Stream responseStream = resp.GetResponseStream())
{
//如果网页流压缩了,要加下面一句
//responseStream = new GZipStream(responseStream, CompressionMode.Decompress);
using (StreamReader mySreamReader = new StreamReader(responseStream, Encoding.Default))//GB2312,utf-8,GBK
{
string responseData = mySreamReader.ReadToEnd();
//MessageBox.Show(responseData);
responseStream.Close();
mySreamReader.Close();
resp.Close();
return responseData;
}
}
}
}
catch (Exception ex)
{
return ex.ToString();
}
}
#endregion
}//end of class
}//end of namespace
SQL:
--请注意!!!!!
--A. 请将 [master] 替换为当前库
--B. 请将主程序集的dll路径替换为当前库中的路径
--C. 出现错误:在 master 数据库中记录的数据库所有者 SID 与在数据库 'dbName' 中记录的数据库所有者 SID 不同。
-- 应该通过使用 ALTER AUTHORIZATION 语句重置数据库 'db_Study' 的所有者来更正此情况。
-- Sp_changedbowner 'sa',true
USE [master]
GO
--1. 在SQL Server中启用CLR
exec sp_configure 'clr enabled', 1
reconfigure;
go
--2. 在目标库 [master] 设置TRUSTWORTHY为ON
ALTER DATABASE [master] SET TRUSTWORTHY ON
--3. 删除主程序集已有对象db
IF OBJECT_ID('[dbo].[Fun_DBA_IsMatch]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_DBA_IsMatch]
GO
IF OBJECT_ID('[dbo].[Fun_DBA_IsChinese]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_DBA_IsChinese]
GO
IF OBJECT_ID('[dbo].[Fun_DBA_GetHTML]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_DBA_GetHTML]
GO
--4. 删除主程序集 SqlClr
IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='SqlClr')
DROP ASSEMBLY SqlClr
GO
--5. 创建主程序集: SqlClr
CREATE ASSEMBLY SqlClr FROM 'D:\Tools\DBA\SqlClr\SqlClr.dll' WITH PERMISSION_SET = UNSAFE
GO
-- =============================================
-- Author: 叶南阁
-- Create date: 2014-11-25
-- Description: 正则表达式匹配
-- =============================================
CREATE FUNCTION [dbo].[Fun_DBA_IsMatch]
(
@source AS NVARCHAR(MAX), --要匹配的文本
@pattern AS NVARCHAR(200), --进行匹配的正则表达式
@option INT=3 --正则表达式匹配选项,1为忽略大小写,2为多行匹配,3为忽略大小写且多行匹配
)
RETURNS BIT
AS
EXTERNAL NAME [SqlClr].[SqlClr.SqlCLR].[IsMatch];
GO
-- =============================================
-- Author: yenange
-- Create date: 2014-11-25
-- Description: 判断字符串是否为中文
-- =============================================
CREATE FUNCTION [dbo].[Fun_DBA_IsChinese]
(
@source AS NVARCHAR(MAX) --要匹配的文本
)
RETURNS BIT
AS
EXTERNAL NAME [SqlClr].[SqlClr.SqlCLR].[IsChinese];
GO
-- =============================================
-- Author: yenange
-- Create date: 2016-10-20
-- Description: 根据Url获取html
-- =============================================
CREATE FUNCTION [dbo].[Fun_DBA_GetHTML]
(
@url AS NVARCHAR(MAX) --url
)
RETURNS NVARCHAR(MAX)
AS
EXTERNAL NAME [SqlClr].[SqlClr.SqlCLR].[Fun_GetHTML];
GO
--测试
SELECT dbo.Fun_DBA_GetHTML('http://www.ip138.com/ips138.asp?ip=183.58.24.207&action=2') AS html