張小呆的碎碎唸

接觸 MS SQL 已經有一段時間，都沒有實作過 SQLCLR，坦白說真的有點汗顏。所以今天就心血來潮，就來實作一個簡單的 SQLCLR 的功能，題目是甚麼呢!? 那就挑經常會用的 Split 來下手。說實在的，這已經不是甚麼大不了的功能。因為已經有高手將這功能用 T-SQL 的方式時做出來了，是哪兩個呢!? 這小弟就先留在 參考 的部份加註上去唄，以下就來實作 Split 吧...

步驟一：新增一個 SQL Server 專案


步驟二：在 專案 按右鍵 選 加入 並點選 使用者定義函式


步驟三：撰寫 Split 功能的 SQL Function，要記得 Build 唷
using System.Collections; using System.Collections.Generic; public partial class MySplit { [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "fnSplitString")] public static IEnumerable fnSplit(SqlString strContent, SqlString splitWord) { int i = 1; Dictionary<int, string> result = new Dictionary<int, string>(); foreach (var item in strContent.Value.Split(splitWord.Value.ToCharArray(0, 1))) { result.Add(i, item); i++; } return result; } public static void fnSplitString(object row, ref SqlInt32 resultInt, ref SqlString resultString) { if (row != null) { KeyValuePair<int, string> returnValue = (KeyValuePair<int, string>)row; resultInt = returnValue.Key; resultString = returnValue.Value; } } };

步驟四：Enable SQLCLR
EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO

※假如是要用介面來設定的話，請參閱 參考 的部分

步驟五：執行 TRUSTWORTHY
Use 測試資料庫 ALTER DATABASE master SET TRUSTWORTHY ON Go

步驟六：建立組件
Use 測試資料庫 Create ASSEMBLY uFnSplit FROM 'C:Documents and SettingsAdmin桌面fnSplitfnSplitbinDebugfnSplit.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO

步驟七：在 測試資料庫 資料庫 中，建立一個 Function


步驟八：輸入程式碼
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION MyfnSplit ( @Content nvarchar(4000), @word nvarchar(20) ) RETURNS TABLE ( num int, result nvarchar(4000) ) AS EXTERNAL NAME uFnSplit.MySplit.fnSplit; GO

步驟九：結果


參考：
SQL CLR .Net Function - Split
應用C#和SQLCLR編寫SQL Server用戶定義函數
A SQL Split Function(適用於各版本的 SQL SERVER)
WITH的遞迴應用-Split欄位(適用 SQL SERVER 2005 及以後的版本)
KeyValuePair<(Of <(TKey, TValue>)>) 結構
TRUSTWORTHY 資料庫屬性
CLR 資料表值函數
clr enabled 選項
啟用 CLR，以 SQL Server 2005 為例
SQL Server 2008 介面區組態(Surface Area Configuration，SAC)：啟用 xp_cmdshell
SQL Server 專案和資料庫物件的屬性