原文: .NET編程和SQL Server ——Sql Server 與CLR集成 (學(xué)習(xí)筆記整理-1)
一、SQL Server 為什么要與CLR集成
1、 SQL Server 提供的存儲(chǔ)過程、函數(shù)等十分有限,經(jīng)常需要外部的代碼來執(zhí)行一些繁重的移植;
2、與CLR集成可將原本需要獨(dú)立的程序來實(shí)現(xiàn)的功能遷移到SQL Server 內(nèi)部進(jìn)行數(shù)據(jù)操作;
3、T-SQL數(shù)據(jù)查詢語言在返回?cái)?shù)據(jù)集方面很好,但是除此之外表現(xiàn)不佳。與CLR的集成可解決這一問題;
4、.NET的操作代碼和執(zhí)行的速度比T-SQL快的很多。.NET程序是已經(jīng)編譯好的二進(jìn)制代碼,而不是作為存儲(chǔ)過程來構(gòu)建,不再編譯就直接可運(yùn)行。
二、SQL Server 中的程序集(編譯、添加、修改、刪除)
只有在添加了程序集后才能在該程序集的基礎(chǔ)上建立CLR存儲(chǔ)過程、CLR函數(shù)等。
1、CLR代碼(編譯)→DLL文件(注冊(cè))→SQL Server (作為數(shù)據(jù)庫對(duì)象)→執(zhí)行數(shù)據(jù)庫操作 過程如下:
(1)將托管程序編寫為一組類定義。編寫好代碼后編譯成一個(gè)DLL文件;
存儲(chǔ)過程、用戶自定義函數(shù)、觸發(fā)器的編寫為類的靜態(tài)方法;
用戶自定義類型、聚合函數(shù)編寫為一個(gè)結(jié)構(gòu)體。
(2)DLL 文件上傳 SQL Server 磁盤上,并使用create assembly 將DLL程序集存儲(chǔ)到系統(tǒng)目錄;
(3)創(chuàng)建SQL對(duì)象(函數(shù)、存儲(chǔ)過程、觸發(fā)器等)并將其綁定到程序集的入口點(diǎn);
存儲(chǔ)過程:create procedure
用戶自定義函數(shù):create function
觸發(fā)器:create trigger
用戶自定義類型:create type
聚合函數(shù):create aggregate
(4)像使用T-SQL例程一樣使用。
2、SQL Server 中的程序集(創(chuàng)建程序集并上載到SQL Server 實(shí)例然后創(chuàng)建數(shù)據(jù)庫對(duì)象)
(1)SQL Server 2008默認(rèn)情況下禁用了CLR集成的功能,必需先啟用CLR集成后才能在SQL Server 訪問.NET對(duì)象。
啟用CLR集成
exec sp_configure 'show advanced options','1';
go
reconfigure;
go
exec sp_configure 'clr enabled','1';//開啟CLR集成
go
reconfigure;
go
解釋
(2)將DLL程序集添加到SQL Server 中。在SQL Server 中添加程序集使用create assembly命令。
create assembly assembly_name(程序集名)
[authorization owner_name]
from {<client_assembly_specifier>|<assembly_bits>}
[with permission_set={safe|external_access|unsafe}]
其中,<client_assembly_specifier>:表示程序集所在的本地位置或網(wǎng)絡(luò)位置以及與程序集對(duì)應(yīng)的清單文件名。
<assembly_bits>:表示組成程序集和依賴程序集的二進(jìn)制值的列表。
permission_set={safe|external_access|unsafe :表示指定SQL Server 訪問程序集時(shí)相程序集授予的一組訪問權(quán)限,默認(rèn)值為safe。
(3)修改程序集
alter assembly assembly_name
[from <client_assembly_specifier>|<assembly_bits>]
[with <assembly_option>[,....n]]
[drop file{file_name[,....n]|all}]
[add file from client_file_specifier [as file_name]|file_bits as file_name}[,....n]][;]
其中,<assembly_option>::=permission_set=[{safe|external_access|unsafe} | visibility={on|off} | unchecked data],其中 visibility={on|off}:指示在創(chuàng)建CLR函數(shù)、存儲(chǔ)過程、觸發(fā)器、用戶定義的類型以及用戶自定義聚合函數(shù)時(shí),該程序集是否可見。如果設(shè)置為OFF則程序集只能由其他程序集調(diào)用。unchecked data :默認(rèn)情況下,如果alter assembly 必須驗(yàn)證各個(gè)表行的一致性,則他將失敗。該選項(xiàng)使得用戶可以通過使用DBCC CHECKTABLE將檢查推遲到以后的某個(gè)時(shí)間進(jìn)行。
A、為程序集添加文件:
alter assembly assembly_name
add file from client_file_specifier [as file_name]|file_bits as file_name}[,....n]][;]
B、更新程序集:
use database_name
go
alter assembly assembly_name
drop file all
go
alter assembly assembly_name
from <client_assembly_specifier>|<assembly_bits>]
add file from client_file_specifier [as file_name]|file_bits as file_name}[,....n]][;]
(4)刪除程序集
刪除程序集是,將從數(shù)據(jù)庫中刪除程序集和它的所有關(guān)聯(lián)文件,如,源代碼和調(diào)試文件等。但如果該程序集被其他對(duì)象引用則返回錯(cuò)誤。
drop assembly assembly_name[,....n]
[with no dependents]
其中, with no dependents :表示只刪除assembly_name而不刪除該程序集引用的相關(guān)程序集。如果不指定它,則drop assembly 將刪除assembly_name和所有相關(guān)程序集。
三、創(chuàng)建CLR函數(shù)(Function)
要?jiǎng)?chuàng)建被SQL Server 引用的CLR程序則需要引用Microsoft.SqlServer.Server命名空間,創(chuàng)建CLR函數(shù)還需要使用該命名空間下的SqlFunctionAttribute特性類即將[Microsoft.SqlServer.ServerSqlFunction.]放置CLR函數(shù)的頭部。
1、創(chuàng)建CLR標(biāo)量值函數(shù)
(1)使用C#編寫CLR標(biāo)量值函數(shù)在VS2010中創(chuàng)建CLR函數(shù)后,編譯成DLL文件,并將該文件添加到數(shù)據(jù)庫中。
(2)在SQL Server中使用CLR標(biāo)量值函數(shù) 使用create function創(chuàng)建引用注冊(cè)程序集的函數(shù)。
create function --[schema_name.]function_name //[schema_name.]如:[dbo.]
(
{@parameter_name
[as] [type_schema_name.]parameter_data_type [=default]}[,....n]
)
return {return_date_type}
[with <clr_function_option> [,...n]]
[as]external name assembly_name.
class_name
.method_name
(
class_name需要加上命名空間哦
)
其中external name assembly_name. class_name .method_name:指定將程序集與函數(shù)綁定的方法。<clr_function_option>::={[returns null on null input | called no null input] | [execute_as_clause] } 其中returns null on null input | called no null input] | [execute_as_clause ]:指定標(biāo)量值函數(shù)的onNULLCall屬性。如果未指定,則默認(rèn)值為 called on null input。這意味著即使傳遞的參數(shù)為null,也將執(zhí)行函數(shù)體。如果在CLR函數(shù)中指定了returns null on null input ,它指示當(dāng)SQL Server接收到的任何一個(gè)參數(shù)為null時(shí),它可以返回null,而無須實(shí)際調(diào)用函數(shù)體。 優(yōu)先采用create function語句指示的屬性。不能為表值函數(shù)指定Onnullcall屬性。
2、創(chuàng)建CLR表值函數(shù)
(1)使用C#編寫CLR表值函數(shù)
CLR表值函數(shù)只返回一個(gè)表,在.NET中中創(chuàng)建對(duì)應(yīng)的函數(shù),返回的結(jié)果是一個(gè)IEnumerable接口,用于表示一個(gè)集合。集合中是對(duì)象的實(shí)例并不是SQLServer中所識(shí)別的表,因此需要在函數(shù)的屬性中指定FillRowMethodName,這個(gè)參數(shù)的值是用于將.NET中的對(duì)象轉(zhuǎn)換為表列的函數(shù)名。即將特性[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillSplitTable")]放置與于表值函數(shù)的頭部,以指定該特性下的函數(shù)為CLR表值函數(shù)。其中,F(xiàn)illSplitTable是將.NET 中的對(duì)象轉(zhuǎn)換為表列的函數(shù)名。還有用于將.NET中的對(duì)象轉(zhuǎn)換為表列的方法必須為靜態(tài)方法。第一個(gè)參數(shù)必須為System.Object類型,接下來的參數(shù)的個(gè)數(shù)就是列的個(gè)數(shù)。同時(shí)接下來的參數(shù)都必須聲明為ref參數(shù)。SQLServer中返回的列的數(shù)據(jù)類型和順序必須與該函數(shù)中ref參數(shù)的數(shù)據(jù)類型和順序相 同。編寫完后編譯成DLL文件并添加到數(shù)據(jù)庫中。
(2)在SQLServer中使用CLR表值函數(shù)
A、更新程序集
要在SQLServer中使用C#編寫的CLR表值函數(shù),必須先更新程序集。
如:
alter assembly assembly_name
from '程序集地址'
with permission_set=safe
B、創(chuàng)建CLR表值函數(shù)
create function [schema_name.]function_name
(
{@parameter_name [as][type.schema_name.]
parameter_data_type [=default]}[,...n]
)
return table<clr_table_type_definition>
[with <clr_function_option>[,...n] ]
[order(<order_clause>)]
[as]external name assembly_name.class_name.method_name[;]
其中,<clr_table_type_definition>::=({column_name data_type}[,...n])定義CLR函數(shù)的表數(shù)據(jù)類型。表聲明僅包含列名稱和數(shù)據(jù)類型。表始終放在主文件組中。 order(<order_clause>)指定從表值函數(shù)中返回結(jié)果的順序。
3、在T-SQL中使用CLR函數(shù)
四、創(chuàng)建CLR存儲(chǔ)過程(Procedure)
1、使用C#編寫CLR存儲(chǔ)過程所需的函數(shù):
在C#中編寫可用于CLR存儲(chǔ)過程引用的函數(shù)必須使用SqlProcedure屬性標(biāo)識(shí)。存儲(chǔ)過程不需要返回值,所以在C#中建立void函數(shù)即可。存儲(chǔ)過程一般用于查詢并生成一個(gè)查詢的表,在c#中需要使用SqlPipe對(duì)象將表格結(jié)果與信息傳回給客戶端。一般,通過SqlContext類的Pipe屬性獲得SqlPipe對(duì)象,后調(diào)用Pipe對(duì)象的Send()方法將表格結(jié)果或信息傳送給客戶端,或者使用SqlPipe對(duì)象的ExecuteAndSend()方法將查詢結(jié)果傳送給客戶端。ExecuteAndSend()方法提供了一種高效率的方式將查詢結(jié)果傳送給客戶端。使用特性[Microsoft.SqlServer.Server.SqlProcedure]放置在存儲(chǔ)過程調(diào)用的函數(shù)的頭部,用以標(biāo)示該函數(shù)是作為CLR存儲(chǔ)過程被調(diào)用的,CLR存儲(chǔ)過程對(duì)應(yīng)的函數(shù)。將C#編寫的代碼編譯成DLL文件,并添加到數(shù)據(jù)庫中。
2、在SQL Server中使用CLR存儲(chǔ)過程
create {proc|procedure}[schema_name.]procedure_name [;number]
[
{ @parameter [type_schema_name.] data_type }
[varying] [=default] [out|output] [readonly]
][,...n]
[with <procedure_option> [,...n]]
[for replication]
as external name assembly_name.class_name.method_name [;]
其中,external name assembly_name.class_name.method_name指定.net framework程序集的方法,以便程序集引用。class_name必須存在與該程序集中,而且指定的方法必須為該類的靜態(tài)方法。
<procedure_option>::=[encryption] [recompile]
3、創(chuàng)建有output參數(shù)的CLR存儲(chǔ)過程
存儲(chǔ)過程中也可以使用output參數(shù),帶有output的參數(shù)的值在存儲(chǔ)過程內(nèi)部被修改后也會(huì)將修改應(yīng)用到存儲(chǔ)過程外部相當(dāng)于指針和ref參數(shù)。output參數(shù)對(duì)應(yīng)于C#中的ref參數(shù)。
4、在T-SQL中使用CLR存儲(chǔ)過程
?
?
五、創(chuàng)建CLR觸發(fā)器(Trigger)
觸發(fā)器是數(shù)據(jù)庫服務(wù)器中發(fā)生時(shí)間事自動(dòng)執(zhí)行的特殊存儲(chǔ)過程。
DML觸發(fā)器:如果用戶通過DML事件數(shù)據(jù),則執(zhí)行DML觸發(fā)器。DML事件是針對(duì)表或視圖的insert、update 、或delete語句。
DDL觸發(fā)器:用于響應(yīng)各種DDL事件,主要是create、alter、drop語句。
??????? 1、使用C#編寫CLR觸發(fā)器
????????? 為了能夠在C#中處理觸發(fā)器觸發(fā)時(shí)的情況,Microsoft.SqlServer.Server命名空間提供了SqlTriggerContext 類。SqlTriggerContext 類提供所激發(fā)的觸發(fā)器的上下文信息,通過SqlContext.TriggerContext來獲得。通過TriggerAction來獲得觸發(fā)的類型,SqlTriggerContext.TriggerAction 屬性指示激發(fā)觸發(fā)器的操作。在使用C#編寫CLR觸發(fā)器是有可能用到觸發(fā)器中的倆張?zhí)厥獾谋恚篿nsert和deleted的時(shí)候需要使用SqlCommand.如:
create trigger [schema_name.] trigger_name
on {table | view}
[with <dml_trigger_option>[,...n]]
{for | after | instead of}
{ [insert] [,] [update] [,] [delete] }
[with append]
[not for replication]
as external name assembly_name.class_name.method_name
其中,external name assembly_name.class_name.method_name用于指定程序集與觸發(fā)器綁定的方法。該方法不帶任何參數(shù)而且必需返回空值。
?????? 3、在T-SQL中使用CLR觸發(fā)器
?
?
?
六、創(chuàng)建用戶定義聚合函數(shù)(Aggregate)
在SQL Server中,經(jīng)常需要對(duì)數(shù)據(jù)按組進(jìn)行自定義的聚合操作,默認(rèn)的聚合函數(shù)只有SUM(),MAX(),MIN(),AVG()等,因此就需要定義用戶自定義聚合函數(shù)。
1、使用C#編寫聚合函數(shù)
創(chuàng)建用戶自定義聚合函數(shù)必須使用特性[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]放置聚合函數(shù)的頭部,以標(biāo)識(shí)該函數(shù)是用戶自定義聚合函數(shù)。此外創(chuàng)建的聚合函數(shù)還必須是可序列化的,使用特性[Serializable]標(biāo)識(shí)。
聚合函數(shù)實(shí)際上是一個(gè)結(jié)構(gòu)類型或者說聚合函數(shù)對(duì)應(yīng)的是一個(gè)struct類型而不是一個(gè)方法,在其中必須實(shí)現(xiàn)4個(gè)方法:
(1)Init()初始化函數(shù): 為要處理的每組行調(diào)用Init()方法。在這個(gè)方法中,為要計(jì)算的每組行進(jìn)行初始化;
(2)Accumulate()定義具體聚合操作的函數(shù): 為所有組中的每個(gè)值調(diào)用這個(gè)方法。這個(gè)方法的參數(shù)必須是正確的累加類型,還可以上用戶定義的類型。該函數(shù)定義聚合函數(shù)的具體聚合操作;
(3)Merge()合并函數(shù): 聚合的結(jié)果必須和另一個(gè)聚合結(jié)果合并起來,調(diào)用Merge()方法。
(4)Terminate()結(jié)束函數(shù): 在處理每一組的最后一行后,調(diào)用該方法。這里,聚合的結(jié)果必須用正確的數(shù)據(jù)類型返回。
編寫好聚合函數(shù)后重新編譯整個(gè)項(xiàng)目將DLL文件添加的數(shù)據(jù)庫中。后使用alter assembly命令將聚合到SQL Server的程序集中。
2、在SQL Server中創(chuàng)建用戶自定義聚合函數(shù)
在SQL Server中創(chuàng)建用戶自定義聚合函數(shù)以引用CLR中的聚合函數(shù)。創(chuàng)建用戶自定義聚合函數(shù)使用create aggregate命令。如下:
create aggregate [schema_name.] aggregate_name
(
@param_name <input_sqltype>[,...n]
)
returns <return_type>
external name assembly_name [.class_name]
<input_sqltype>::=
system_scalar_type | {[udt_schema_name.] udt_type_name}
<return_type>::=
system_scalar_type | {[udt_schema_name.] udt_type_name}
其中,system_scalar_type:表示要存放輸入?yún)?shù)值或返回值的任意一個(gè)SQL Server系統(tǒng)標(biāo)量數(shù)據(jù)類型。除了text、ntext和image之外的所有標(biāo)量數(shù)據(jù)類型,都可以用作自定義聚合函數(shù)的參數(shù)。不能指定非標(biāo)量類型(如cursor和table)。
udt_schema_name:表示CLR用戶定義類型所屬的架構(gòu)的名稱。如果未指定則數(shù)據(jù)庫按以下順序引用udt_schema_name:本機(jī)SQL類型命名空間、當(dāng)前數(shù)據(jù)庫中當(dāng)前用戶的默認(rèn)架構(gòu)、當(dāng)前數(shù)據(jù)庫中的dbo架構(gòu)。
udt_type_name:表示當(dāng)前數(shù)據(jù)庫中以創(chuàng)建的CLR用戶自定義類型的名稱。如果未指定udt_schema_name,則SQL Server假定該類型屬于當(dāng)前用戶的架構(gòu)。
assembly_name [.class_name] :表示指定與用戶定義的聚合函數(shù)綁定在一起的程序集以及(可選)該程序集所屬的架構(gòu)名稱和該程序集中實(shí)現(xiàn)該用戶定義聚合函數(shù)的類名稱。
3、在T-SQL中使用用戶自定義聚合函數(shù)
create aggregate CountVowels
(
@input nvarchar(4000)
)
returns int
external name TestAssembly.CountVowels
go
select City ,COUNT(City) as PersonCount,dbo.CountVowels(City) as CityVowelsCount
from Person.Address
group by City
七、創(chuàng)建CLR用戶定義類型(UDT)
創(chuàng)建CLR用戶自定義類型來擴(kuò)展SQL的類型系統(tǒng),UDT可用于定義表中的列的類型或T-SQL中的變量或例程(存儲(chǔ)過程、觸發(fā)器等)參數(shù)的類型。用戶定義類型實(shí)例可以是表中的列,比處理、函數(shù)或存儲(chǔ)過程中的變量,或者函數(shù)或者存儲(chǔ)過程的參數(shù)。
1、使用C#定義類型
用戶定義類型必須實(shí)現(xiàn)接口INullable,申明IsNull屬性表示該類型是否為空值,而且用戶定義類型在C#中用一個(gè)可序列化的結(jié)構(gòu)體表示,這點(diǎn)和CLR用戶自定義聚合函數(shù)相同。編寫好C#代碼后進(jìn)行編譯生成DLL文件并更新到數(shù)據(jù)庫中。
2、在SQL Server中使用CLR用戶定義類型
要?jiǎng)?chuàng)建CLR用戶定義類型需使用create type命令,不僅可以創(chuàng)建基于SQL數(shù)據(jù)類型的用戶自定義類型,也可以創(chuàng)建基于CLR的用戶自定義類型。
create type [schema_name] type_name
external name assembly_name.[class_name]
3、使用CLR用戶自定義類型
create type myFirstType
external name myTypeAssembly.myFirstType
go
select table testMyFirstType
(
T myFirstType;
)
go
insert into testMyFirstType
values(‘1,7’);
insert into testMyFirstType
values(‘6,0’);
go
select T
from testMyFirstType
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
