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',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.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.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é)果如下
| 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)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: ASP.NET应用程序客户端执行的优化小
- 下一篇: 【转】cron