軟件等級(jí)：

更新時(shí)間：2016-11-11

版本號(hào)：v5.7.10

MySQL Server x64官方正式版免費(fèi)下載

立即下載

 教大家SQL Server中的CLR編程(用.NET為SQL Server編寫(xiě)存儲(chǔ)過(guò)程)

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

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

exec sp_configure 'clr enabled', 1;--在SQL Server中啟用CLR reconfigure; go --在ArticleCollectorDB數(shù)據(jù)庫(kù)中設(shè)置TRUSTWORTHY為ON ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON

這時(shí)可能會(huì)得到提示要重新啟動(dòng)SQL Server，如果有此提示則重新啟動(dòng)一下。

接著我們?cè)?span style="font-family: Times New Roman">VS中進(jìn)行編碼，在這里我們將分別編寫(xiě)一個(gè)名為IsMatch的函數(shù)和一個(gè)名為SendMail存儲(chǔ)過(guò)程。在VS中創(chuàng)建一個(gè)名為NetSkycn.Data的類(lèi)庫(kù)項(xiàng)目，添加一個(gè)SqlCLR的類(lèi)，代碼如下：

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> /// 判斷字符串是否匹配正則表達(dá)式 /// </summary> /// <cke:param name="source"></cke:param>要匹配的文本</cke:param> /// <cke:param name="pattern"></cke:param>進(jìn)行匹配的正則表達(dá)式</cke:param> /// <cke:param name="options"></cke:param>正則表達(dá)式匹配選項(xiàng)，1為忽略大小寫(xiě)，2為多行匹配，3為忽略大小寫(xiě)且多行匹配</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主機(jī)時(shí)用到的用戶(hù)名,注意是郵件地址'@'以前的部分</cke:param> /// <cke:param name="password"></cke:param>登錄smtp主機(jī)時(shí)用到的用戶(hù)密碼</cke:param> /// <cke:param name="smtpHost"></cke:param>發(fā)送郵件用到的smtp主機(jī)</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ā)送郵件身份驗(yàn)證方式 //注意如果發(fā)件人地址是abc@def.com，則用戶(hù)名是abc而不是abc@def.com client.Credentials = new NetworkCredential(userName, password); client.Send(message); } } }

編譯通過(guò)之后，記住類(lèi)庫(kù)的物理全路徑，比如：F:\VS2008\netskycn\NetSkycn.Data\bin\Release\NetSkycn.Data.dll，在這里要強(qiáng)調(diào)幾點(diǎn)：一、對(duì)于將來(lái)提供給SQL Server調(diào)用的函數(shù)或者存儲(chǔ)過(guò)程必須是靜態(tài)方法，并且還必須帶有SqlFunction或者SqlProcedure屬性；二、對(duì)于一些需要訪問(wèn)外部網(wǎng)絡(luò)資源和安全屬性的還必須添加響應(yīng)的屬性（如本例中的SendMail方法，如果沒(méi)有添加響應(yīng)的屬性在創(chuàng)建SQL Function/Procedure時(shí)會(huì)出現(xiàn)錯(cuò)誤提示）。

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

--在ArticleCollectorDB數(shù)據(jù)庫(kù)中設(shè)置TRUSTWORTHY為ON ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON --如果已經(jīng)存在該對(duì)象則刪除 IF EXISTS(SELECT * FROM SYS.SYSOBJECTS WHERE NAME='SendMail' AND XTYPE='PC') DROP PROCEDURE SendMail --如果已經(jīng)存在該對(duì)象則刪除 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,有三個(gè)參數(shù)， --[SqlCLR]是SQL Server中程序集名 --[NetSkycn.Data.SqlCLR]是.NET中的類(lèi)的全名（命名空間及類(lèi)名） --[IsMatch]是.NET中類(lèi)的函數(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,有三個(gè)參數(shù)， --[SqlCLR]是SQL Server中程序集名 --[NetSkycn.Data.SqlCLR]是.NET中的類(lèi)的全名（命名空間及類(lèi)名） --[SendMail]是.NET中類(lèi)的函數(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

如果沒(méi)有得到任何錯(cuò)誤提示，則表示創(chuàng)建函數(shù)和存儲(chǔ)過(guò)程成功。至此我們會(huì)看到如下情形：

這表示創(chuàng)建成功。

測(cè)試創(chuàng)建函數(shù)的SQL語(yǔ)句（查找article表中title字段是3至5個(gè)字段的數(shù)據(jù)）：

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

測(cè)試創(chuàng)建存儲(chǔ)過(guò)程的SQL語(yǔ)句：

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è)版測(cè)試通過(guò)。

可以看出在一些SQL語(yǔ)句不夠靈活的情況下，可以使用.NET來(lái)編寫(xiě)存儲(chǔ)過(guò)程和函數(shù)，通過(guò)以上步驟之后和調(diào)用SQL語(yǔ)句寫(xiě)的存儲(chǔ)過(guò)程和函數(shù)沒(méi)有區(qū)別，極大地方便了編程。

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