很早就知道可以用 .NET 為 SQL?Server2005 及以上版本編寫存儲過程、觸發(fā)器和存儲過程的，不過之前開發(fā)的系統(tǒng)要么因為歷史原因用的是 SQL2000 要么根本用不著在 SQL?Server 中啟用 CLR ，所以一直沒有嘗試。最近因為項目的原因，在這方面做了一個調(diào)研，現(xiàn)在在這里分享一下心得。
首先要說明的是要在 SQL?Server 中啟用 CLR 必須是在 SQL?Server2005 及以上版本，其次在默認情況下是沒有啟用 CLR 的，必須要顯示設置為啟用。比如我們要在 ArticleCollectorDB 數(shù)據(jù)庫中運行用 .NET 編寫的函數(shù)或者存儲過程，至少先要進行下面的 SQL 語句：
exec sp_configure 'clr enabled', 1;--在SQL Server中啟用CLR
reconfigure;
go
--在ArticleCollectorDB數(shù)據(jù)庫中設置TRUSTWORTHY為ON
ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON
這時可能會得到提示要重新啟動 SQL?Server ，如果有此提示則重新啟動一下。
接著我們在 VS 中進行編碼，在這里我們將分別編寫一個名為 IsMatch 的函數(shù)和一個名為 SendMail 存儲過程。在 VS 中創(chuàng)建一個名為 NetSkycn.Data 的類庫項目，添加一個 SqlCLR 的類，代碼如下：
using System.Data.SqlTypes;
using System.Net;
using System.Net.Mail;
using System.Security.Permissions;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
namespace NetSkycn.Data
{
/// <summary>
/// 在SQL Server環(huán)境中執(zhí)行的CLR方法，注意提供給SQL Server調(diào)用的方法必須有SqlFunction/SqlProcedure Attribute
/// 作者：周公
/// 創(chuàng)建日期：2012-05-09
/// 博客地址：http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com
/// 新浪微博地址：http://weibo.com/zhoufoxcn
/// </summary>
public sealed class SqlCLR
{
/// <summary>
/// 判斷字符串是否匹配正則表達式
/// </summary>
/// <param name="source">要匹配的文本</param>
/// <param name="pattern">進行匹配的正則表達式</param>
/// <param name="options">正則表達式匹配選項，1為忽略大小寫，2為多行匹配，3為忽略大小寫且多行匹配</param>
/// <returns></returns>
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
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>
/// 發(fā)送郵件
/// </summary>
/// <param name="to">收件人郵件地址</param>
/// <param name="from">發(fā)件人郵件地址</param>
/// <param name="subject">郵件主題</param>
/// <param name="body">郵件內(nèi)容</param>
/// <param name="username">登錄smtp主機時用到的用戶名,注意是郵件地址'@'以前的部分</param>
/// <param name="password">登錄smtp主機時用到的用戶密碼</param>
/// <param name="smtpHost">發(fā)送郵件用到的smtp主機</param>
[SqlProcedure]
[SmtpPermission(SecurityAction.Assert)]
[SecurityPermission(SecurityAction.Assert)]
public static void SendMail(string to, string from, string subject, string body, string userName, string password, string smtpHost)
{
MailAddress addressFrom = new MailAddress(from);
MailAddress addressTo = new MailAddress(to);
MailMessage message = new MailMessage(addressFrom, addressTo);
message.Subject = subject;//設置郵件主題
message.IsBodyHtml = true;//設置郵件正文為html格式
message.Body = body;//設置郵件內(nèi)容
SmtpClient client = new SmtpClient(smtpHost);
//設置發(fā)送郵件身份驗證方式
//注意如果發(fā)件人地址是abc@def.com，則用戶名是abc而不是abc@def.com
client.Credentials = new NetworkCredential(userName, password);
client.Send(message);
}
}
}
編譯通過之后，記住類庫的物理全路徑，比如： F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll ，在這里要強調(diào)幾點：一、對于將來提供給 SQL?Server 調(diào)用的函數(shù)或者存儲過程必須是靜態(tài)方法，并且還必須帶有 SqlFunction 或者 SqlProcedure 屬性；二、對于一些需要訪問外部網(wǎng)絡資源和安全屬性的還必須添加響應的屬性（如本例中的 SendMail 方法，如果沒有添加響應的屬性在創(chuàng)建 SQL?Function/Procedure 時會出現(xiàn)錯誤提示）。
現(xiàn)在我們開始遵循先為 SQL?Server 創(chuàng)建程序集、后創(chuàng)建函數(shù)或者存儲過程的順序來操作，在操作過程中用到的 SQL 語句如下：
--在ArticleCollectorDB數(shù)據(jù)庫中設置TRUSTWORTHY為ON
ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON
--如果已經(jīng)存在該對象則刪除
IF EXISTS(SELECT * FROM SYS.SYSOBJECTS WHERE NAME='SendMail' AND XTYPE='PC')
DROP PROCEDURE SendMail
--如果已經(jīng)存在該對象則刪除
IF EXISTS(SELECT * FROM SYS.SYSOBJECTS WHERE NAME='IsMatch' AND XTYPE='FS')
DROP FUNCTION IsMatch
--如果已經(jīng)存在SqlCLR程序集則刪除該程序集
IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='SqlCLR')
DROP ASSEMBLY SqlCLR
--在SQL Server中創(chuàng)建程序集,，創(chuàng)建的程序集名為SqlCLR
CREATE ASSEMBLY SqlCLR FROM 'F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll' WITH PERMISSION_SET = UNSAFE
GO
--從CLR程序集中創(chuàng)建函數(shù)，函數(shù)名為IsMatch,有三個參數(shù)，
--[SqlCLR]是SQL Server中程序集名
--[NetSkycn.Data.SqlCLR]是.NET中的類的全名（命名空間及類名）
--[IsMatch]是.NET中類的函數(shù)名
CREATE FUNCTION [dbo].[IsMatch]
(
@source AS NVARCHAR(200),
@pattern AS NVARCHAR(200),
@option INT=3
)
RETURNS BIT
AS
EXTERNAL NAME [SqlCLR].[NetSkycn.Data.SqlCLR].[IsMatch];
GO
--從CLR程序集中創(chuàng)建函數(shù)，函數(shù)名為IsMatch,有三個參數(shù)，
--[SqlCLR]是SQL Server中程序集名
--[NetSkycn.Data.SqlCLR]是.NET中的類的全名（命名空間及類名）
--[SendMail]是.NET中類的函數(shù)名
CREATE PROCEDURE [dbo].[SendMail]
(
@to AS NVARCHAR(200),
@from AS NVARCHAR(200),
@subject AS NVARCHAR(200),
@body AS NVARCHAR(MAX),
@userName AS NVARCHAR(200),
@password AS NVARCHAR(200),
@smtpHost AS NVARCHAR(200)
)
AS
EXTERNAL NAME [SqlCLR].[NetSkycn.Data.SqlCLR].[SendMail];
GO
如果沒有得到任何錯誤提示，則表示創(chuàng)建函數(shù)和存儲過程成功。至此我們會看到如下情形：
這表示創(chuàng)建成功。
測試創(chuàng)建函數(shù)的 SQL 語句（查找 article 表中 title 字段是 3 至 5 個字段的數(shù)據(jù)）：
select * from article where dbo.IsMatch(Title,'^[\u4e00-\u9fa5]{3,5}$',3)=1
測試創(chuàng)建存儲過程的 SQL 語句：
exec [dbo].SendMail @to='test@qq.com',@from='webmaster@qq.com',@subject='test',@body='This mail was sent by SQL Procedure',@userName='webmaster',@password='123',@smtpHost='smtp.qq.com'
以上代碼在 SQL?Server?2005 中文企業(yè)版、 SQL?Server?2008 英文企業(yè)版測試通過。
可以看出在一些SQL語句不夠靈活的情況下，可以使用.NET來編寫存儲過程和函數(shù)，通過以上步驟之后和調(diào)用SQL語句寫的存儲過程和函數(shù)沒有區(qū)別，極大地方便了編程。
周公
2012-05-12
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
