SQL Server中的CLR程式設計——用.NET為SQL Server編寫存儲過程和函數

很早就知道可以用.NET為SQL Server2005及以上版本編寫存儲過程、觸發器和存儲過程的，不過之前開發的系統要麼因為曆史原因用的是SQL2000要麼根本用不着在SQL Server中啟用CLR，是以一直沒有嘗試。最近因為項目的原因，在這方面做了一個調研，現在在這裡分享一下心得。

首先要說明的是要在SQL Server中啟用CLR必須是在SQL Server2005及以上版本，其次在預設情況下是沒有啟用CLR的，必須要顯示設定為啟用。比如我們要在ArticleCollectorDB資料庫中運作用.NET編寫的函數或者存儲過程，至少先要進行下面的SQL語句：

exec sp_configure 'clr enabled', 1;--在SQL Server中啟用CLR 

reconfigure; 

go 

--在ArticleCollectorDB資料庫中設定TRUSTWORTHY為ON 

ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON 

這時可能會得到提示要重新啟動SQL Server，如果有此提示則重新啟動一下。

接着我們在VS中進行編碼，在這裡我們将分别編寫一個名為IsMatch的函數和一個名為SendMail存儲過程。在VS中建立一個名為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環境中執行的CLR方法，注意提供給SQL Server調用的方法必須有SqlFunction/SqlProcedure Attribute 

/// 作者：周公 

/// 建立日期：2012-05-09 

/// 部落格位址：http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com 

/// 新浪微網誌位址：http://weibo.com/zhoufoxcn 

/// </summary> 

public sealed class SqlCLR 

/// 判斷字元串是否比對正規表達式 

/// <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)); 

/// 發送郵件 

/// <param name="to">收件人郵件位址</param> 

/// <param name="from">發件人郵件位址</param> 

/// <param name="subject">郵件主題</param> 

/// <param name="body">郵件内容</param> 

/// <param name="username">登入smtp主機時用到的使用者名,注意是郵件位址'@'以前的部分</param> 

/// <param name="password">登入smtp主機時用到的使用者密碼</param> 

/// <param name="smtpHost">發送郵件用到的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;//設定郵件内容 

SmtpClient client = new SmtpClient(smtpHost); 

//設定發送郵件身份驗證方式 

//注意如果發件人位址是[email protected]，則使用者名是abc而不是[email protected] 

client.Credentials = new NetworkCredential(userName, password); 

client.Send(message); 

編譯通過之後，記住類庫的實體全路徑，比如：F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll，在這裡要強調幾點：一、對于将來提供給SQL Server調用的函數或者存儲過程必須是靜态方法，并且還必須帶有SqlFunction或者SqlProcedure屬性；二、對于一些需要通路外部網絡資源和安全屬性的還必須添加響應的屬性（如本例中的SendMail方法，如果沒有添加響應的屬性在建立SQL Function/Procedure時會出現錯誤提示）。

現在我們開始遵循先為SQL Server建立程式集、後建立函數或者存儲過程的順序來操作，在操作過程中用到的SQL語句如下：

--如果已經存在該對象則删除 

IF EXISTS(SELECT * FROM SYS.SYSOBJECTS WHERE NAME='SendMail' AND XTYPE='PC')  

DROP PROCEDURE SendMail 

IF EXISTS(SELECT * FROM SYS.SYSOBJECTS WHERE NAME='IsMatch' AND XTYPE='FS')  

DROP FUNCTION IsMatch 

--如果已經存在SqlCLR程式集則删除該程式集 

IF EXISTS(SELECT * FROM SYS.ASSEMBLIES WHERE NAME='SqlCLR')  

DROP ASSEMBLY SqlCLR 

--在SQL Server中建立程式集,，建立的程式集名為SqlCLR 

CREATE ASSEMBLY SqlCLR FROM 'F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll' WITH PERMISSION_SET = UNSAFE 

GO 

--從CLR程式集中建立函數，函數名為IsMatch,有三個參數， 

--[SqlCLR]是SQL Server中程式集名 

--[NetSkycn.Data.SqlCLR]是.NET中的類的全名（命名空間及類名） 

--[IsMatch]是.NET中類的函數名 

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];  

--[SendMail]是.NET中類的函數名 

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) 

) 

EXTERNAL NAME [SqlCLR].[NetSkycn.Data.SqlCLR].[SendMail];  

如果沒有得到任何錯誤提示，則表示建立函數和存儲過程成功。至此我們會看到如下情形：

<a href="http://blog.51cto.com/attachment/201205/013926112.jpg" target="_blank"></a>

這表示建立成功。

測試建立函數的SQL語句（查找article表中title字段是3至5個字段的資料）：

select * from article where dbo.IsMatch(Title,'^[\u4e00-\u9fa5]{3,5}$',3)=1 

測試建立存儲過程的SQL語句：

exec [dbo].SendMail @to='[email protected]',@from='[email protected]',@subject='test',@body='This mail was sent by SQL Procedure',@userName='webmaster',@password='123',@smtpHost='smtp.qq.com' 

以上代碼在SQL Server 2005中文企業版、SQL Server 2008英文企業版測試通過。

可以看出在一些SQL語句不夠靈活的情況下，可以使用.NET來編寫存儲過程和函數，通過以上步驟之後和調用SQL語句寫的存儲過程和函數沒有差別，極大地友善了程式設計。

本文轉自周金橋51CTO部落格，原文連結： http://blog.51cto.com/zhoufoxcn/859245，如需轉載請自行聯系原作者