SQLCLR（一）入門

SQL Server 2005發(fā)布后，可以編寫出能夠在SQL Server中運(yùn)行的.net代碼了。同IIS一樣，SQL Server 2005也是.net程序的宿主。
首先安裝sqlserver2005和visual studio 2005。啟用sqlserver2005的clr ，應(yīng)先在sqlsever的查詢分析器按實(shí)際情況執(zhí)行下邊的語句

exec?sp_configure?'clr?enabled'--查看狀態(tài)
exec?sp_configure?'clr?enabled',1--1,啟用clr\0,禁用clr
reconfigure?with?override--按提示(配置選項(xiàng)?'clr?enabled'?已從?0?更改為?1。請(qǐng)運(yùn)行?RECONFIGURE?語句進(jìn)行安裝。)運(yùn)行此句

快速開發(fā)的方法莫過于直接使用visual studio 2005。

第一步，打vs新建項(xiàng)目類型-數(shù)據(jù)庫-SQL Server項(xiàng)目。
第二步，數(shù)據(jù)庫引用連接。如果沒有可選連接，可以添加新連接。

點(diǎn)添加新引用按鈕，添加新的連接。

第三步，現(xiàn)在可以添加用戶自定義類型(UDT)、存儲(chǔ)過程、用戶自定義函數(shù)、聚合和觸發(fā)器中的任意一種了。
?
現(xiàn)在動(dòng)手做一個(gè)小的自定義函數(shù)的例子。
添加一個(gè)"用戶定義的函數(shù)(F)"，

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?TestFunction(string?input)
????{
????????//?在此處放置代碼
????????return?"hello,"?+?input;
????}
};

項(xiàng)目右鍵-部署，狀態(tài)欄顯示部署成功后，在sqlserver2005 查詢分析器選擇對(duì)應(yīng)的數(shù)據(jù)庫執(zhí)行下面的語句。

SELECT?[dbo].[TestFunction]?('david?fan')

結(jié)果如下

SQLCLR（二）存儲(chǔ)過程和自定義函數(shù)

自定義函數(shù)和存儲(chǔ)過程在.net里其實(shí)都是方法。只是方法上方標(biāo)注[Microsoft.SqlServer.Server.SqlProcedure]
和[Microsoft.SqlServer.Server.SqlFunction]不同而已。自定義函數(shù)又分TVF函數(shù)和Scalar兩種，最大區(qū)別在于TVF返回表后者返回Scalar（標(biāo)量），這一篇我們做一下比較。
先看兩段代碼
存儲(chǔ)過程:
using?System;
using?System.Data;
using?System.Data.SqlClient;
using?System.Data.SqlTypes;
using?Microsoft.SqlServer.Server;


public?partial?class?StoredProcedures
{
????//這里是告訴sqlserver，這個(gè)方法要注冊(cè)成存儲(chǔ)過程
????//我感覺[Attribute]這個(gè)東西翻譯成標(biāo)簽更形像:)
????[Microsoft.SqlServer.Server.SqlProcedure]
????public?static?void?TestStoredProcedure(string?name,?ref?string?outstr)
????{
????????//?在此處放置代碼
????????outstr?=?"hello,"?+?name;

????????using?(SqlConnection?cn?=?new?SqlConnection())
????????{
????????????//使用上下文鏈接也就是當(dāng)前數(shù)據(jù)庫鏈接
????????????cn.ConnectionString?=?"context?connection=true";
????????????using?(SqlCommand?cmd?=?cn.CreateCommand())
????????????{
????????????????cmd.CommandText?=?"Select?*?from?userinfo";
????????????????cn.Open();
????????????????//SqlContext.Pipe.Send這個(gè)方法輸出結(jié)果集
????????????????//接受SqlDataReader,SqlDataRecord和string
????????????????SqlContext.Pipe.Send(cmd.ExecuteReader());
????????????????//你也可以用下邊這樣
????????????????//SqlContext.Pipe.ExecuteAndSend(cmd);
????????????}
????????}
????}
};
執(zhí)行存儲(chǔ)過程
DECLARE?@name?nvarchar(4000)
DECLARE?@outstr?nvarchar(4000)
set?@name='david?fan'
--?TODO:?在此處設(shè)置參數(shù)值。
EXECUTE?[TestProject].[dbo].[TestStoredProcedure]?
???@name
??,@outstr?OUTPUT
print?@outstr
結(jié)果如下

輸出參數(shù)返回值
?
自定義函數(shù)
一，TVF函數(shù)
示例函數(shù)的作用是搜索目錄下的某一類型的文件
using?System;
using?System.Data;
using?System.Data.Sql;
using?System.Data.SqlTypes;
using?Microsoft.SqlServer.Server;
using?System.Collections;
using?System.IO;
using?System.Security.Principal;

public?partial?class?UserDefinedFunctions
{
????//需要返回一個(gè)表時(shí)用TVF(streaming?table-valued?function)
????//可以用select?from?語句查詢這個(gè)方法的返回
????//TVF需要返回Ienumerable接口,例如:Array，這里返回一個(gè)數(shù)組

????//FillRowMethodName為填充表行的方法
????//TableDefinition為表結(jié)構(gòu)，對(duì)應(yīng)FillRowMethodName方法的參數(shù)
????[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName?=?"BuildRow",
?????TableDefinition?=?"Name?nvarchar(32),?Length?bigint,?Modified?DateTime")]
????public?static?IEnumerable?FileListCs(string?directoryName,?string?pattern)
????{
????????FileInfo[]?files;
???????//模擬當(dāng)前SQL安全上下文
????????WindowsImpersonationContext?OriginalContext=?SqlContext.WindowsIdentity.Impersonate();
????????try
????????{
????????????DirectoryInfo?di?=?new?DirectoryInfo(directoryName);
????????????files?=?di.GetFiles(pattern);
????????}
????????finally
????????{
????????????if?(OriginalContext?!=?null)
????????????{
????????????????OriginalContext.Undo();
????????????}
????????}
????????return?files;
????}

????public?static?void?BuildRow(object?Obj,
??????????ref?SqlString?fileName,
??????????ref?SqlInt64?fileLength,
??????????ref?SqlDateTime?fileModified)
????{
????????if?(Obj?!=?null)
????????{
????????????FileInfo?file?=?(FileInfo)Obj;
????????????fileName?=?file.Name;
????????????fileLength?=?file.Length;
????????????fileModified?=?file.LastWriteTime;
????????}
????????else
????????{
????????????fileName?=?SqlString.Null;
????????????fileLength?=?SqlInt64.Null;
????????????fileModified?=?SqlDateTime.Null;
????????}
????}
}因?yàn)檫@個(gè)函數(shù)對(duì)于sqlserver來講要訪問外部資源，所以需要配置一下項(xiàng)目和sqlserver2005
項(xiàng)目右鍵屬性，數(shù)據(jù)庫,權(quán)限級(jí)別選外部

打開sqlserver2005查詢分析器執(zhí)行下邊語句 TestProject 為我的數(shù)據(jù)庫名，你的如果不是，當(dāng)然需要修改了。
ALTER?DATABASE?TestProject?SET?TRUSTWORTHY?ON;成功后，項(xiàng)目右鍵部署。

查詢分析器中執(zhí)行
SELECT?*?FROM?[TestProject].[dbo].[FileListCs]?(
???'c:\'
??,'*.txt')結(jié)果如下

二，Scalar 函數(shù)
這類函數(shù)返回類型如圖，像SqlString這類sqlserver的scalar類型

下面就是這類函數(shù)的一個(gè)小例子。
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?ScalarFunction()
????{
????????//?在此處放置代碼
????????return?new?SqlString("Hello");
????}
};sqlserver查詢查詢分析器中運(yùn)行如下語句
SELECT?[TestProject].[dbo].[ScalarFunction]?()結(jié)果如下

SQLCLR（三）觸發(fā)器

這一節(jié)比較簡(jiǎn)單了，主要是講如何在SQLCLR下設(shè)計(jì)觸發(fā)器。在SQLServer2005里分兩種觸發(fā)器，DDL和DML兩種觸發(fā)器。DDL觸發(fā)器是響應(yīng)CREATE、ALTER 和 DROP 開頭的語句。我們常用的是DML觸發(fā)器，這一類觸發(fā)器響應(yīng)當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作包括表或視圖中修改數(shù)據(jù)的 INSERT 、UPDATE 或 DELETE 。
對(duì)于.net來講觸發(fā)器也是方法，在上方標(biāo)注[Microsoft.SqlServer.Server.SqlTrigger]標(biāo)簽(只我這樣翻譯)。
我們看一個(gè)小例子
using?System;
using?System.Data;
using?System.Data.Sql;
using?Microsoft.SqlServer.Server;
using?System.Data.SqlClient;

public?partial?class?Triggers
{
????//Name觸發(fā)器的名字
????//Target觸發(fā)器對(duì)應(yīng)表
????//Event可以是{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
????[Microsoft.SqlServer.Server.SqlTrigger(Name?=?"NameInfoTrigger",?Target?=?"NameInfo",?Event?=?"FOR?UPDATE")]
????public?static?void?GetChange()
????{
????????using?(SqlConnection?cn?=?new?SqlConnection())
????????{
????????????cn.ConnectionString?=?"context?connection=true";
????????????cn.Open();
????????????using?(SqlCommand?cmd?=?cn.CreateCommand())
????????????{
????????????????cmd.CommandText?=?"insert?into?ChangeHistory?select?b.name?+?'->'?+?a.name,getdate()?from?INSERTED?a?JOIN?DELETED?b?ON?a.id?=?b.id";
????????????????SqlContext.Pipe.ExecuteAndSend(cmd);
????????????}
????????}
????}
}

右鍵部署

數(shù)據(jù)庫里的兩張表

我執(zhí)行兩條UPDATE語句
update?[NameInfo]?set?[name]='David'?where?id=1
update?[nameinfo]?set?[name]='Fan'?where?id=2結(jié)果

再執(zhí)行兩條 update?[NameInfo]?set?[name]='*David*'?where?id=1
update?[nameinfo]?set?[name]='*Fan*'?where?id=2再看結(jié)果
?

SQLCLR（四）用戶定義類型UDT

用戶自定義類型是SQL Server 2005的新特性。和前幾篇文章介紹的SQLCLR相比，UDT相對(duì)有此復(fù)雜。UDT也有許多限制和必須遵守UDT規(guī)范。UDT的二進(jìn)制不能超過8000個(gè)字節(jié)，必須包含一個(gè)null值表示，因?yàn)镾QLServer的數(shù)據(jù)類型是允許null值的。
UDT可以是結(jié)構(gòu)或類。如果是類的話需加[StructLayout(LayoutKind.Sequential)]
標(biāo)簽（屬性），這是保證序列化時(shí)不改變屬性的次序。
現(xiàn)在看一段代碼

using?System;
using?System.IO;
using?System.Data;
using?System.Data.SqlClient;
using?System.Data.SqlTypes;
using?Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,?MaxByteSize?=?1024)]
public?struct?Person?:?INullable,?IBinarySerialize
{
????public?override?string?ToString()
????{
????????//?用您的代碼替換下列代碼
????????return?FormatU();
????}

????public?bool?IsNull
????{
????????get
????????{
????????????//?在此處放置代碼
????????????return?m_Null;
????????}
????}

????public?static?Person?Null
????{
????????get
????????{
????????????Person?h?=?new?Person();
????????????h.m_Null?=?true;
????????????return?h;
????????}
????}

????public?static?Person?Parse(SqlString?s)
????{
????????if?(s.IsNull)
????????????return?Null;

????????Person?u?=?new?Person();
????????string?value?=?s.Value;
????????if?(value?==?"null")?return?Null;

????????string[]?parts?=?value.Split(',');
????????u.name?=?parts[0];
????????u.age?=?ParseAge(parts[1]);
????????u.sex?=?parts[2];
????????return?u;
????}

????//?這是占位符方法
????public?string?FormatU()
????{
????????//在此處插入方法代碼
????????return?string.Format("名稱:{0},年齡:{1},性別:{2}",?name,?age,?sex);
????}

????//?這是占位符靜態(tài)方法
????public?static?int?ParseAge(string?str)
????{
????????//在此處插入方法代碼
????????return?int.Parse(str.Substring(0,?str.LastIndexOf("歲")));
????}

????//?這是占位符字段成員
????private?int?age;
????public?int?Age
????{
????????get?{?return?age;?}
????????set?{?age?=?value;?}
????}

????private?string?name;
????public?string?Name
????{
????????get?{?return?name;?}
????????set?{?name?=?value;?}
????}

????private?string?sex;
????public?string?Sex
????{
????????get?{?return?sex;?}
????????set?{?sex?=?value;?}
????}

????//?私有成員
????private?bool?m_Null;
????public?byte[]?b;

????public?void?Read(BinaryReader?r)
????{
????????name?=?r.ReadString();
????????sex?=?r.ReadString();
????????age?=?r.ReadInt32();
????????m_Null?=?r.ReadBoolean();
????}
????public?void?Write(BinaryWriter?w)
????{
????????w.Write(name);
????????w.Write(sex);
????????w.Write(age);
????????w.Write(m_Null);
????}
}



部署后在SQL Server 2005中執(zhí)行下面的語句
create?table?UdtTest?(Id?int?not?null,?p?Person?not?null)
insert?into?UdtTest?values(1,?'David,24歲,男')
select?id,?convert(nvarchar(25),p)?from?UdtTest
drop?table?UdtTest

結(jié)果如下

想看清楚SQLCLR在對(duì)UDT處理機(jī)制可以將項(xiàng)目附加到SQL Server 2005進(jìn)程，在相應(yīng)的方法設(shè)置斷點(diǎn)。
?

SQLCLR（五）聚合

SQL Server中的聚合，常用的比如max,count之類。 我們現(xiàn)在也可以在SQLCLR里創(chuàng)建自定義的聚合。Visual Studio 2005中提供的聚合模板是一個(gè)結(jié)構(gòu)，標(biāo)注了[Serializable]，[SqlUserDefinedAggregate]標(biāo)簽，這將讓SQLCLR知道這是一個(gè)聚合函數(shù)。
看一段代碼，這段代碼來自SQL Server 2005聯(lián)機(jī)叢書，本來自己想寫一段，但突然公司有些事要做，沒時(shí)間了。示例代碼作用是合并同一部書(ID相同)的作者。
using?System;
using?System.Data;
using?Microsoft.SqlServer.Server;
using?System.Data.SqlTypes;
using?System.IO;
using?System.Text;

[Serializable]
[SqlUserDefinedAggregate(
????Format.UserDefined,?//use?clr?serialization?to?serialize?the?intermediate?result
????IsInvariantToNulls?=?true,?//optimizer?property
????IsInvariantToDuplicates?=?false,?//optimizer?property
????IsInvariantToOrder?=?false,?//optimizer?property
????MaxByteSize?=?8000)?//maximum?size?in?bytes?of?persisted?value
]
public?class?Concatenate?:?IBinarySerialize
{
????/**////?<summary>
????///?The?variable?that?holds?the?intermediate?result?of?the?concatenation
????///?</summary>
????private?StringBuilder?intermediateResult;

????/**////?<summary>
????///?Initialize?the?internal?data?structures
????///?</summary>
????public?void?Init()
????{
????????this.intermediateResult?=?new?StringBuilder();
????}

????/**////?<summary>
????///?Accumulate?the?next?value,?not?if?the?value?is?null
????///?</summary>
????///?<param?name="value"></param>
????public?void?Accumulate(SqlString?value)
????{
????????if?(value.IsNull)
????????{
????????????return;
????????}

????????this.intermediateResult.Append(value.Value).Append(',');
????}

????/**////?<summary>
????///?Merge?the?partially?computed?aggregate?with?this?aggregate.
????///?</summary>
????///?<param?name="other"></param>
????public?void?Merge(Concatenate?other)
????{
????????this.intermediateResult.Append(other.intermediateResult);
????}

????/**////?<summary>
????///?Called?at?the?end?of?aggregation,?to?return?the?results?of?the?aggregation.
????///?</summary>
????///?<returns></returns>
????public?SqlString?Terminate()
????{
????????string?output?=?string.Empty;
????????//delete?the?trailing?comma,?if?any
????????if?(this.intermediateResult?!=?null
????????????&&?this.intermediateResult.Length?>?0)
????????{
????????????output?=?this.intermediateResult.ToString(0,?this.intermediateResult.Length?-?1);
????????}

????????return?new?SqlString(output);
????}

????public?void?Read(BinaryReader?r)
????{
????????intermediateResult?=?new?StringBuilder(r.ReadString());
????}

????public?void?Write(BinaryWriter?w)
????{
????????w.Write(this.intermediateResult.ToString());
????}
}這里有幾個(gè)比較重要的方法：Terminate，這個(gè)方法是聚合最后調(diào)用的方法，它返回最后的值。可以是SQL Server的任何標(biāo)量。；Accumulate，聚合每處理一行數(shù)據(jù)的時(shí)候都會(huì)調(diào)用一次，并將要處理的數(shù)據(jù)傳給方法。可以在函數(shù)內(nèi)部進(jìn)行比如比較，合并之類的處理。；
CREATE?TABLE?BookAuthors
(
???BookID???int???????NOT?NULL,
???AuthorName????nvarchar(200)?NOT?NULL
)

INSERT?BookAuthors?VALUES(1,?'Johnson')
INSERT?BookAuthors?VALUES(2,?'Taylor')
INSERT?BookAuthors?VALUES(3,?'Steven')
INSERT?BookAuthors?VALUES(2,?'Mayler')
INSERT?BookAuthors?VALUES(3,?'Roberts')
INSERT?BookAuthors?VALUES(3,?'Michaels')

SELECT?BookID,?dbo.MyAgg(AuthorName)
FROM?BookAuthors
GROUP?BY?BookID
結(jié)果如下
BookID Author Names

1	Johnson
2	Taylor, Mayler
3	Roberts, Michaels, Steven

Microsoft SQL Server Management Studio為我們提供了數(shù)據(jù)庫內(nèi)對(duì)象的集中管理功能，前面幾篇?jiǎng)?chuàng)建的SQLCLR對(duì)象，都可以在數(shù)據(jù)庫的可編程性下相應(yīng)模塊里找到。

轉(zhuǎn)載于:https://www.cnblogs.com/doc/archive/2009/02/11/1388513.html

總結(jié)

以上是生活随笔為你收集整理的SQLCLR系列文章的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。