教大家SQL Server中的CLR編程(用.NET為SQL Server編寫存儲過程)

軟件等級：

更新時間：2016-11-11

版本號：v5.7.10

MySQL Server x64官方正式版免費下載

立即下載

 教大家SQL Server中的CLR編程(用.NET為SQL Server編寫存儲過程)

最近在這方面做了一個調(diào)研，現(xiàn)在在這里分享一下心得。很早就知道可以用.NET為SQL Server2005及以上版本編寫存儲過程、觸發(fā)器和存儲過程的，不過之前開發(fā)的系統(tǒng)要么因為歷史原因用的是SQL2000要么根本用不著在SQL Server中啟用CLR，所以一直沒有嘗試。

首先要說明的是要在SQL Server中啟用CLR必須是在SQL Server2005及以上版本，其次在默認(rèn)情況下是沒有啟用CLR的，必須要顯示設(shè)置為啟用。比如我們要在ArticleCollectorDB數(shù)據(jù)庫中運行用.NET編寫的函數(shù)或者存儲過程，至少先要進行下面的SQL語句：

exec sp_configure 'clr enabled', 1;--在SQL Server中啟用CLR reconfigure; go --在ArticleCollectorDB數(shù)據(jù)庫中設(shè)置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 /// </summary> public sealed class SqlCLR { /// <summary> /// 判斷字符串是否匹配正則表達式 /// </summary> /// <cke:param name="source"></cke:param>要匹配的文本</cke:param> /// <cke:param name="pattern"></cke:param>進行匹配的正則表達式</cke:param> /// <cke:param name="options"></cke:param>正則表達式匹配選項，1為忽略大小寫，2為多行匹配，3為忽略大小寫且多行匹配</cke: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> /// <cke:param name="to"></cke:param>收件人郵件地址</cke:param> /// <cke:param name="from"></cke:param>發(fā)件人郵件地址</cke:param> /// <cke:param name="subject"></cke:param>郵件主題</cke:param> /// <cke:param name="body"></cke:param>郵件內(nèi)容</cke:param> /// <cke:param name="username"></cke:param>登錄smtp主機時用到的用戶名,注意是郵件地址'@'以前的部分</cke:param> /// <cke:param name="password"></cke:param>登錄smtp主機時用到的用戶密碼</cke:param> /// <cke:param name="smtpHost"></cke:param>發(fā)送郵件用到的smtp主機</cke: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;//設(shè)置郵件主題 message.IsBodyHtml = true;//設(shè)置郵件正文為html格式 message.Body = body;//設(shè)置郵件內(nèi)容 SmtpClient client = new SmtpClient(smtpHost); //設(shè)置發(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)絡(luò)資源和安全屬性的還必須添加響應(yīng)的屬性（如本例中的SendMail方法，如果沒有添加響應(yīng)的屬性在創(chuàng)建SQL Function/Procedure時會出現(xiàn)錯誤提示）。

現(xiàn)在我們開始遵循先為SQL Server創(chuàng)建程序集、后創(chuàng)建函數(shù)或者存儲過程的順序來操作，在操作過程中用到的SQL語句如下：

--在ArticleCollectorDB數(shù)據(jù)庫中設(shè)置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ū)別，極大地方便了編程。

希望我的這個大家有所幫助，記得在這篇日志下面或者主頁的留言板中留下你們的建議和反饋，這些對我們是最寶貴的財富，預(yù)祝大家快樂！有問題大家積極回帖討論下哈！