sql server clr 整合系列之四 建立一個clr的資料表值函式—實用的Split函數

大家都知道在C#把一個字串以指定的字元分成一個數組，實用split方法很容易辦到，但是在t-sql 中卻。。。。 在我的項目中，有如下應用：批量讀取一批資料，然後處理每行的資料，處理完後，把處理成功的id和處理失敗的id返回給資料庫，這就涉及到一個批次更新的一個問題，你是把id一個一個寫成批處理更新回資料庫還是每個id就開啟一次資料庫，更新呢？其實，只要t-sql支援數組就能夠做到。好了，我們現在就讓t-sql支援數組！

大家都說我的描述很不好，這次我乾脆直接用代碼+注釋來表示了。using System;
using System.Collections;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace StringSplit
{
    public class Stringhelp
    {
        //這個特性定義了一個sql資料表值函式，此函數返回的表的定義為：String nvarchar(200)
        //並且指定了填充這個表的行的方法是FillRow 方法
        //注意這個方法返回的一定是一個IEnumerable類型的，並且為公開，靜態，這個方法的入參就是sql函數的入參
        [SqlFunction(TableDefinition = " String nvarchar(200)", FillRowMethodName = "FillRow")]
        public static IEnumerable GetStrings(string x,char y)
        {
            //返回一個string 數組，這個數組符合IEnumerable介面，當然你也可以返回hashtable等類型。
            return x.Split(y);
        }
        //填充返回表的行的方法，這個方法有一定的規定：
        //一定是空返回的void類型，並且入參的第一個必須為object，其後面的參數都必須為out類型
        //參數的類型，個數和順序由返回表的列結構決定！（在TableDefinition = " String nvarchar(200)"中定義的表結構）
        public static void FillRow(object row,out string splitedstring)
        {
            //這個object 其實就是GetStrings(string x,char y)函數返回的迭代，這樣你直接賦值給那個列就可以了。
             splitedstring = (String)row;
        }
    }
}

這樣，編譯此類庫，實用如下sql 語句將dll建立到資料庫中：
create assembly ArrayAssembly
from 'd:\stringSplit.dll'
WITH permission_set = Safe;
注意更改dll的對應地址。
然後如果是第一次做clr整合應用，需要把clr功能開啟，使用如下語句開啟：
EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO

然後開啟了的朋友可以建立這個資料表值函式了，使用如下語句：
create function splitstring(@x nvarchar (200),@y nchar(1))
RETURNS TABLE(StringElement nvarchar(200))
AS EXTERNAL NAME ArrayAssembly.[StringSplit.Stringhelp].GetStrings;
GO
這個語句要注意命名規則，規則我在以前的文章中講過了。
然後可以測試這個資料表值函式了。

select * from splitstring('xiangxiang,zangjifei',',')

返回結果：

這樣，就可以很容易的使用了。
比如：
select * from dbo.T_test as t where t.id in  (select * from splitstring('12,23',','))