昨天完成了一個最簡單的在數據庫中創建標量值函數,今天主要完成表值函數,存儲過程和用戶定義類型在和.NET結合下的使用方法.
1,表值函數
所謂表值函數就是說這個函數返回的結果是一個Table,而不是單個的值.
在.NET 中創建這樣的函數,返回的結果是一個IEnumerable接口.這個接口非常靈活,所有.NET數組集合等都是實現了該接口的.下面我們舉一個簡單的例子來說明.
在VS2005中創建一個類Student,這個就是我們要返回的表的內容,類下面有屬性int Age,string sName,DateTime Birthday,int SID;
然后在另外一個類UserFunction中寫入如下代碼:

[SqlFunction(FillRowMethodName="FillRow")]
public static IEnumerable GetStudent()
{
Hashtable hash = new Hashtable();
for(int i=0;i<3;i++)
{
Student s = new Student();
s.SID = i;
...
hash.Add(i, s);
}
return hash.Values;
}
public static void FillRow(object obj, ref SqlInt32 id, ref SqlString name, ref SqlDateTime bir, ref SqlInt32 age)
{
Student s = (obj as Student);
id = s.SID;
name = s.sName;
bir = s.Birthday;
age = s.Age;

}

第一個屬性中指定FillRowMethodName就是為了將返回的IEnumerable接口中的數據進行轉換,將數據庫無法認識的集合轉換為數據庫人生的字段.下面的函數FillRow就是具體轉換的過程.
這樣寫完成以后,在數據庫那邊添加好這個程序集,然后就可以創建表值函數了:

create function BuildTable()
returns table(SID int,[sName] nvarchar(100),Birthday datetime,Age int)
as
external name SQLFunction.[SQLFunction.UserFunction].GetStudent

這兒就不用太多的解釋了,就是將名為SQLFunction的程序集中的[名字空間.類].方法添加到BuildTable函數中.
這兒需要說明一下就是數據庫中的類型和.NET中的類型的對應問題.int,datetime就不說了,主要是.NET中的string,在數據庫中沒有string類型,在FillRow中指出了類型SqlString,而這個類型的對應是nchar,nvarchar.這兒不能對應char,varchar,我不知道為什么必須是對應nchar的.所以上面我們寫的是[sName] nvarchar(100).
大功告成,測試一下,輸入語句select * from BuildTable()看看返回你的表沒有.
2.存儲過程
CLR存儲過程和CLR函數非常相似,不過有幾點更高的能力:
CLR存儲過程可以有一個返回值,也可以寫輸出參數,可以返回消息給客戶程序,可以調用DDL和DML語句.
.NET創建存儲過程要編寫為靜態函數,然后加上SqlProcedure屬性.
比如我們寫一個簡單的存儲過程

[SqlProcedure]
public static int Add(int a, int b)
{
return a + b;
}

然后在數據庫中寫入:

create procedure Add2Num
@a int,@b int
as
external name SQLFunction.[SQLFunction.UserFunction].[Add]

整個代碼我就不用解釋了,和前面創建函數一樣.
我們運行看看結果:

declare @a int
exec @a=Add2Num 10,12
print @a

3.用戶定義類型(UDT)
要創建UDT類必須符合"UDT規范",.NET中的約束如下:
他們必須帶SqlUserDefinedType 屬性
必須帶有Serializable屬性
必須實現INullable接口
必須博阿訇公開和靜態的Parse和ToString方法以用于轉換數據類型字符串或逆向轉換.
必須暴露數據元素為公開字段或公開屬性.
好,那我們就創建一個簡單的UDT復數類如下:

[Serializable]
[SqlUserDefinedType(Format.Native)]
[StructLayout(LayoutKind.Sequential)]
public class Complex:INullable
{
bool isNull=false;
double real, imag;
public bool IsNull
{
get { return isNull; }
}
public double Real
{
get { return real; }
set { real = value; }
}
public double Imag
{
get { return imag; }
set { imag = value; }
}
public override string ToString()
{
if (isNull)
{
return "NULL";
}
else
{
return real + "," + imag;
}
}
public static Complex Parse(SqlString s)
{
if (s == null || s.IsNull)
{
return null;
}
else
{
Complex c = new Complex();
string str = Convert.ToString(s);
string[] st = str.Split(',');
c.real = Convert.ToDouble(st[0]);
c.imag = Convert.ToDouble(st[1]);
return c;
}
}
}

編譯好,在數據庫中添加程序集后,我們運行如下代碼:

create type Complex
external name SQLFunction.[SQLFunction.Complex]

這樣我們就創建好了用戶定義類型Complex.
數據庫事例代碼中有相關內容,參見:
\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\UserDefinedDataType