程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server CLR 極速入門，啟用、設計、部署、運行 10分鐘內搞定

SQL Server CLR 極速入門，啟用、設計、部署、運行 10分鐘內搞定

編輯：關於SqlServer

首先總結：SQL Server CLR 設計簡單，部署方便，效率很高，很安全，隨數據庫移動。
測試用例，視頻演示在此下載：http://download.csdn.Net/source/2279319
環境：SQL Server 2005/2008，Visual Studio 2005/2008

在SQL Server裡啟用CLR：

在SQL Server裡執行以下命名，來啟用CLR

exec sp_configure 'clr enabled',1 --1,啟用clr 0,禁用clr

reconfigure

用Visual Studio設計功能，並部署到相關數據庫：

打開Visual Studio-->新建項目-->數據庫-->SQL Server項目-->添加數據庫引用裡新建鏈接（一會將會把CLR部署到這個數據庫上）-->右擊解決方案，添加“用戶自定義函數”
這時，系統會生成一個示例文件 Function1.cs 內容：

using System;

using System.Data;

using System.Data.SqlClIEnt;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString Function1()

{

// 在此處放置代碼

return new SqlString("Hello");

}

using System;
using System.Data;
using System.Data.SqlClIEnt;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Function1()
    {
        // 在此處放置代碼
        return new SqlString("Hello");
    }
}

現在可以直接右擊解決方案，選擇“部署”，狀態欄裡顯示“部署已成功”

在SQL Server裡執行我們部署的CLR函數：

再次進入SQL Server,進入到相關數據庫，執行 Select dbo.Function1(),全顯示執行結果："Hello"
這個函數你可以在 “數據庫-->可編程性-->函數-->標量值函數” 裡看到

OK,這就是整個流程，Very Easy.

更高級應用：

當然我們用CLR 不是只為了讓他生成一個Hello就完事的，這裡來說明一下柳永法(yongfa365)的用途：

去年給公司設計了個OA系統，公司的一些文件內容都非常長，所以選擇了varchar(max),初期感覺查詢速度還挺快，後來覺得越來越慢。

初步分析結果顯示：

數據有近8000條
有3000多條數據len(txtContent)得到結果在4000字符以上
使用“數據庫引擎優化顧問”，對其優化提速為"0%"
SQL語句類似：SELECT * FROM dbo.Articles WHERE txtContent LIKE '%柳永法%'
以前做過的所有系統，從沒有遇到這種問題

近一步分析結果：

數據條數很少，速度卻這麼慢，分析可能是數據庫引擎問題換台機器試問題依舊，排除
like效率問題，以前的系統都是條數多，而這次遇到的是每條數據裡字段內容很長，like除了在數據條數大時會出現性能問題外，還跟每條的字段內容長度有關。在網上查詢並測試確認，確實是數據內容長度問題，而這個系統裡是不可能使用 like '柳永法%'這樣可以使用索引的查詢的。

想來想去只能是使用全文索引，但總會有一些記錄查不出來,而這個要求就這麼高，所以暫時放棄。這時想到了SQL Server CLR,以前只是聽過，覺得可能有用，都收藏了起來，現在打開Chrome,把Google Bookmark上收藏的關於SQL Server的CLR的鏈接全部打開研究了幾分鐘，自己寫了個函數，部署，測試，哈哈……。忒玄妙了，以前的txtContent LIKE '%柳永法%'用時10到12秒，而用我寫的SQL Server CLR函數dbo.ContainsOne(txtContent,'柳永法')=1只用了1秒左右，夠神奇吧。

執行以下語句三次，相當於8年後數據量，有6萬多條數據
INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent FROM dbo.Articles

INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent  FROM dbo.Articles

再執行測試，一般的 like用時82秒，而clr用時5秒，夠有看頭吧。

函數及測試語句如下：

1 [Microsoft.SqlServer.Server.SqlFunction]
2 public static SqlBoolean ContainsOne(SqlChars input, string search)
3 {
4 return new string(input.Value).Contains(search);
5 }
6
7 SELECT COUNT(*) FROM dbo.Articles WHERE dbo.ContainsOne(txtContent,'柳永法')=1
8

另外，我比較熱衷於正則表達式，所以我還想給SQL Server增加一個正則表達式替換的功能，寫起來也非常容易：

1 [Microsoft.SqlServer.Server.SqlFunction]
2 public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
3 {
4 return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
5 }

娃哈哈，一切都這麼的順利，這麼的得心應手，怎能不讓我推薦,在此貼上我寫的一此函數：

  1 using System;
  2 using System.Data;
  3 using System.Data.SqlClIEnt;
  4 using System.Data.SqlTypes;
  5 using Microsoft.SqlServer.Server;
  6 using System.Text.RegularExpressions;
  7 using System.Collections.Generic;
  8 using System.IO;
  9
10 /*
11 請先在SQL Server裡執行以下命名，來啟用CLR
12 exec sp_configure 'clr enabled',1 --1,啟用clr 0,禁用clr
13 reconfigure
14 */
15
16 public partial class UserDefinedFunctions
17 {
18     /// <summary>
19     /// SQL CLR 使用正則表達式替換，eg:
20     /// select dbo.RegexReplace('<span>柳永法http://www.yongfa365.com/</span>','<.+?>','')
21     /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
22     /// --結果：柳永法http://www.yongfa365.com/
23     /// </summary>
24     /// <param name="input">源串，或字段名</param>
25     /// <param name="pattern">正則表達式</param>
26     /// <returns>替換後結果</returns>
27     [Microsoft.SqlServer.Server.SqlFunction]
28     public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
29     {
30         return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
31     }
32
33
34
35
36     /// <summary>
37     /// SQL CLR 使用正則表達式替換，eg:
38     /// select dbo.RegexSearch('<span>柳永法</span>','<.+?>','')
39     /// select * from Articles where dbo.RegexSearch(txtContent,'柳永法')=1;
40     /// </summary>
41     /// <param name="input">源串，或字段名</param>
42     /// <param name="pattern">正則表達式</param>
43     /// <returns>查詢結果，1,0</returns>
44     [Microsoft.SqlServer.Server.SqlFunction]
45     public static SqlBoolean RegexSearch(SqlChars input, string pattern)
46     {
47         return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
48     }
49
50     /// <summary>
51     /// SQL CLR 使用.Net的Contains查找是否滿足條件,eg:
52     /// select dbo.ContainsOne('我是柳永法，','柳永法');
53     /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
54     /// </summary>
55     /// <param name="input">源串，或字段名</param>
56     /// <param name="search">要搜索的字符串</param>
57     /// <returns>返回是否匹配,1,0</returns>
58     [Microsoft.SqlServer.Server.SqlFunction]
59     public static SqlBoolean ContainsOne(SqlChars input, string search)
60     {
61         return new string(input.Value).Contains(search);
62     }
63
64
65     /// <summary>
66     /// SQL CLR 使用.Net的Contains查找是否滿足其中之一的條件,eg:
67     /// select dbo.ContainsAny('我是柳永法，','柳,永,法');
68     /// select * from Articles where dbo.ContainsAny(txtContent,'柳,永,法')=1;
69     /// </summary>
70     /// <param name="input">源串，或字段名</param>
71     /// <param name="search">要搜索的字符串，以","分隔，自己處理空格問題</param>
72     /// <returns>返回是否匹配,1,0</returns>
73     [Microsoft.SqlServer.Server.SqlFunction]
74     public static SqlBoolean ContainsAny(SqlChars input, string search)
75     {
76         string strTemp=new string(input.Value);
77         foreach (string item in search.Split(','))
78         {
79             if (strTemp.Contains(item))
80             {
81                 return true;
82             }
83         }
84         return false;
85     }
86
87     /// <summary>
88     /// SQL CLR 使用.Net的Contains查找是否滿足所有的條件,eg:
89     /// select dbo.ContainsAll('我是柳永法，','柳,永,法');
90     /// select * from Articles where dbo.ContainsAll(txtContent,'柳,永,法')=1;
91     /// </summary>
92     /// <param name="input">源串，或字段名</param>
93     /// <param name="search">要搜索的字符串，以","分隔，自己處理空格問題</param>
94     /// <returns>返回是否匹配,1,0</returns>
95     [Microsoft.SqlServer.Server.SqlFunction]
96     public static SqlBoolean ContainsAll(SqlChars input, string search)
97     {
98         string strTemp = new string(input.Value);
99         foreach (string item in search.Split(','))
100         {
101             if (!strTemp.Contains(item))
102             {
103                 return false;
104             }
105         }
106         return true;
107     }
108
109 };
110

重要提示：

官方說明裡有其dll部署方法，比較麻煩，推薦直接用Visual Studio部署，方便快捷。
SQL Server CLR 部署到某個數據庫後，便成為那個數據庫的一部分，即便備份及還原到其它機器上，它依然具有CLR帶來的功能。
SqlString 數據類型轉換成 nvarchar(4,000)，而 SqlChars 轉換成 nvarchar(max)。盡可能使用 nvarchar(max) 並且最大程度地保證靈活性。然而，如果所有相關字符串包含的字符都少於 4,000 個，使用 nvarchar(4,000) 則性能可得到顯著改善。
CLR裡返回的bool對應SQL Server裡的bit,即：1/0/Null,而不是true/false,所以，沒法直接用dbo.ContainsOne(txtContent,'柳永法')實現bool形，而得這麼用：dbo.ContainsOne(txtContent,'柳永法')=1

參考：
SQL Server CLR 集成簡介：http://msdn.microsoft.com/zh-cn/library/ms254498(VS.80).ASPx
SQL Server 2005 正則表達式使模式匹配和數據提取變得更容易：http://msdn.microsoft.com/zh-cn/magazine/cc163473.ASPx
SQLCLR（一）入門：http://www.cnblogs.com/DavidFan/archive/2007/05/08/738557.Html
應用C#和SQLCLR編寫SQL Server用戶定義函數：http://blog.csdn.Net/zhzuo/archive/2009/05/24/4212982.ASPx#mark4