本節主要介紹使用CLR創建標量函數，表值函數和聚合函數。
所謂標量函數指的就是此函數只返回一個值。表值函數返回值是一個表。聚合函數是在select語句中使用的，用來聚合一個結果集，類似於Sum()或是Count()等內置的函數，而且真正的自定義聚合函數目前只能用CLR來實現。
下面的例子使用了SQLServer自帶的pubs數據庫。
1.CLR標量函數
1.1無參函數
///
/// 標量函數，不帶參數
///
///
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read,
IsDeterministic = true)]
public static SqlString UF_Scalar_SayHello()
{
string returnValue = "null";
//由於程序是在SQL Server內執行，所以連接字符串寫成"context connection=true"即可
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand com = new SqlCommand("select top 1 [au_lname] from [dbo].[authors]",conn);
using (SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection))
{
if (dr.Read())
returnValue = dr.GetString(0);//返回au_lname
}
}
return returnValue;//返回"null”
}
CLR函數用Microsoft.SqlServer.Server.SqlFunction特征進行修飾。裡面的參數含義為：DataAccess = DataAccessKind.Read表示可訪問數據表。關於SqlFunctionAttribute的屬性將附錄在文章的最後。
///
/// 標量函數，帶參數
///
///
///
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString UF_Scalar_SayHelloByPar(SqlString par)
{
return par;
}
2.CLR表值函數
表值與標量函數有些不同。因為要返回一個數據集合，所以一定要用一個填充數據的方法，在屬性中用FillRowMethodName來表示，且返回值應該為IEnumerable類型。代碼如下：
1.首先自定義返回類型
public class ReturnData
{
public SqlString Name { get; set; }
public SqlString PassWord { get; set; }
public ReturnData(string name, string passWord)
{
this.Name = name;
this.Password = passWord;
}
}
2.寫CLR表值函數
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read,
FillRowMethodName = "FillRow_ReturnData",//這裡是此函數的具體填充方法
IsDeterministic = true)]
public static IEnumerable UF_Table_GetReturnData()
{
List returnDataList = new List();
returnDataList.Add(new ReturnData("a", "a"));
returnDataList.Add(new ReturnData("b", "b"));
returnDataList.Add(new ReturnData("c", "c"));
return returnDataList;
}
3.寫填充方法
public static void FillRow_ReturnData(object returnDataObj,
out SqlString name,
out SqlString passWord)
{
ReturnData item = returnDataObj as ReturnData;
name = "";
passWord = "";
if (item != null)
{
name = item.Name;
password = item.PassWord;
}
}
這樣一個表值函數就寫好了。確定有點麻煩，但是表值在某種情況下，也是不可替代的。
3.CLR聚合函數
用戶自定義的CLR聚合類中必須四個函數:Init,Accumulate,Merge,Terminate。Init用戶初始化，Accumulate用來實現具體的聚合算法，Merge用來執行每一次的聚合邏輯順序，Terminate用來將聚合的結果返回。
下面的代碼顯示了字符串的自定義聚合
#region Aggregation
[Serializable]
[StructLayout(LayoutKind.Sequential)]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
MaxByteSize=8000)]
public class StringAgg : IBinarySerialize
{
private StringBuilder strBuffer;
public void Init()
{
strBuffer = new StringBuilder();
}
public void Accumulate(SqlString str)
{
strBuffer.Append(string.Format("{0},", str));
}
public void Merge(StringAgg Group)
{
Accumulate(Group.Terminate());
}
public SqlString Terminate()
{
return strBuffer.ToString();
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
strBuffer = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(strBuffer.ToString());
}
#endregion
}
#endregion;
4.創建函數的SQL腳本及調用方法
關於CLR Assembly的創建方法前面已經講過了，這裡不再重復
--創建函數
create function UF_Scalar_SayHello()
returns nvarchar(32)
as EXTERNAL NAME CLRDemoAssemly.UserDefinedFunctions.UF_Scalar_SayHello
go
create function UF_Scalar_SayHelloByPar(@Par nvarchar(32))
returns nvarchar(32)
as EXTERNAL NAME CLRDemoAssemly.UserDefinedFunctions.UF_Scalar_SayHelloByPar
go
create function UF_Table_GetReturnData()
returns table(Name nvarchar(32),PassWord nvarchar(32))
as EXTERNAL NAME CLRDemoAssemly.UserDefinedFunctions.UF_Table_GetReturnData
go
create AGGREGATE StringAgg(@Par nvarchar(32))
returns nvarchar(max)
EXTERNAL NAME CLRDemoAssemly.StringAgg
go
select dbo.UF_Scalar_SayHello()
go
select dbo.UF_Scalar_SayHelloByPar('Hello TJVictor')
go
select * from dbo.UF_Table_GetReturnData()
go
select dbo.StringAgg(au_lname) from dbo.authors
5.SqlFunctionAttribute的屬性
名稱 說明
DataAccess 指示函數是否需要訪問存儲在 SQL Server 的本地實例中的用戶數據。
FillRowMethodName 方法的名稱，該方法與 TVF 協定所使用的表值函數 (TVF) 在同一個類中。
IsDeterministic 指示用戶定義的函數是否是確定性的。
IsPrecise 指示函數是否涉及不精確的計算，如浮點運算。
Name 函數在 SQL Server 中注冊時所使用的名稱。
SystemDataAccess 指示函數是否需要訪問存儲在 SQL Server 的系統目錄或虛擬系統表中的數據。
TableDefinition 如果方法用作表值函數 (TVF)，則為一個字符串，該字符串表示結果的表定義。
TypeId 當在派生類中實現時，獲取該 Attribute 的唯一標識符。
6.SqlUserDefinedAggregateAttribute的屬性
名稱 說明
Format 序列化格式為 Format 的值之一。如果選擇Native，則聚合類一定要被[StructLayout(LayoutKind.Sequential)]修飾；如果選擇UserDefined，則聚合類一定要繼承IBinarySerialize接口，自己寫序列化方法。
IsInvariantToDuplicates 指示聚合是否與重復值無關。
IsInvariantToNulls 指示聚合是否與空值無關。
IsInvariantToOrder 指示聚合是否與順序無關。
IsNullIfEmpty 指示在沒有對任何值進行累積時聚合是否返回空引用。
MaxByteSize 聚合實例的最大大小。
Name 聚合的名稱。
TypeId 當在派生類中實現時，獲取該 Attribute 的唯一標識符。
7.附錄完整程序
vIEw plaincopy to clipboardprint?
using System;
using System.Data;
using System.Data.SqlClIEnt;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
public partial class UserDefinedFunctions
{
#region Scalar
/// <summary>
/// 標量函數，不帶參數
/// </summary>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read)]
public static SqlString UF_Scalar_SayHello()
{
string returnValue = "null";
//由於程序是在SQL Server內執行，所以連接字符串寫成"context connection=true"即可
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand com = new SqlCommand("select top 1 [au_lname] from [dbo].[authors]", conn);
using (SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection))
{
if (dr.Read())
returnValue = dr.GetString(0);
}
}
return returnValue;
}
/// <summary>
/// 標量函數，帶參數
/// </summary>
/// <param name="par"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString UF_Scalar_SayHelloByPar(SqlString par)
{
return par;
}
#endregion
#region Table
/// <summary>
/// 表值函數。
/// </summary>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read,
FillRowMethodName = "FillRow_ReturnData",
IsDeterministic = true)]
public static IEnumerable UF_Table_GetReturnData()
{
List<ReturnData> returnDataList = new List<ReturnData>();
returnDataList.Add(new ReturnData("a", "a"));
returnDataList.Add(new ReturnData("b", "b"));
returnDataList.Add(new ReturnData("c", "c"));
return returnDataList;
}
public class ReturnData
{
public SqlString Name { get; set; }
public SqlString PassWord { get; set; }
public ReturnData(string name, string passWord)
{
this.Name = name;
this.Password = passWord;
}
}
public static void FillRow_ReturnData(object returnDataObj,
out SqlString name,
out SqlString passWord)
{
ReturnData item = returnDataObj as ReturnData;
name = "";
passWord = "";
if (item != null)
{
name = item.Name;
password = item.PassWord;
}
}
#endregion
};
#region Aggregation
[Serializable]
[StructLayout(LayoutKind.Sequential)]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
MaxByteSize=8000)]
public class StringAgg : IBinarySerialize
{
private StringBuilder strBuffer;
public void Init()
{
strBuffer = new StringBuilder();
}
public void Accumulate(SqlString str)
{
strBuffer.Append(string.Format("{0},", str));
}
public void Merge(StringAgg Group)
{
Accumulate(Group.Terminate());
}
public SqlString Terminate()
{
return strBuffer.ToString();
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
strBuffer = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(strBuffer.ToString());
}
#endregion
}
#endregion;
