原文: 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

    {

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

        /// 發送郵件

        /// </summary>

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

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

            //注意如果發件人地址是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 ，在這里要強調幾點：一、對于將來提供給 SQL?Server 調用的函數或者存儲過程必須是靜態方法，并且還必須帶有 SqlFunction 或者 SqlProcedure 屬性；二、對于一些需要訪問外部網絡資源和安全屬性的還必須添加響應的屬性（如本例中的 SendMail 方法，如果沒有添加響應的屬性在創建 SQL?Function/Procedure 時會出現錯誤提示）。

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

    --在ArticleCollectorDB數據庫中設置TRUSTWORTHY為ON

ALTER DATABASE [ArticleCollectorDB] SET TRUSTWORTHY ON

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

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

GO

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

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

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

--[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)

)

AS 

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

GO
  

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

這表示創建成功。

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

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

測試創建存儲過程的 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 中文企業版、 SQL?Server?2008 英文企業版測試通過。

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

周公

2012-05-12

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