原文: .NET編程和SQL Server ——Sql Server 與CLR集成 （學(xué)習(xí)筆記整理-1）

一、SQL Server 為什么要與CLR集成

1、 SQL Server 提供的存儲過程、函數(shù)等十分有限，經(jīng)常需要外部的代碼來執(zhí)行一些繁重的移植；

2、與CLR集成可將原本需要獨立的程序來實現(xiàn)的功能遷移到SQL Server 內(nèi)部進(jìn)行數(shù)據(jù)操作；

3、T-SQL數(shù)據(jù)查詢語言在返回數(shù)據(jù)集方面很好，但是除此之外表現(xiàn)不佳。與CLR的集成可解決這一問題；

4、.NET的操作代碼和執(zhí)行的速度比T-SQL快的很多。.NET程序是已經(jīng)編譯好的二進(jìn)制代碼，而不是作為存儲過程來構(gòu)建，不再編譯就直接可運行。

二、SQL Server 中的程序集（編譯、添加、修改、刪除）

只有在添加了程序集后才能在該程序集的基礎(chǔ)上建立CLR存儲過程、CLR函數(shù)等。

1、CLR代碼（編譯）→DLL文件（注冊）→SQL Server （作為數(shù)據(jù)庫對象）→執(zhí)行數(shù)據(jù)庫操作 過程如下：

（1）將托管程序編寫為一組類定義。編寫好代碼后編譯成一個DLL文件；

存儲過程、用戶自定義函數(shù)、觸發(fā)器的編寫為類的靜態(tài)方法；

用戶自定義類型、聚合函數(shù)編寫為一個結(jié)構(gòu)體。

（2）DLL 文件上傳 SQL Server 磁盤上，并使用create assembly 將DLL程序集存儲到系統(tǒng)目錄；

（3）創(chuàng)建SQL對象（函數(shù)、存儲過程、觸發(fā)器等）并將其綁定到程序集的入口點；

存儲過程：create procedure

用戶自定義函數(shù)：create function

觸發(fā)器：create trigger

用戶自定義類型：create type

聚合函數(shù)：create aggregate

(4)像使用T-SQL例程一樣使用。

2、SQL Server 中的程序集（創(chuàng)建程序集并上載到SQL Server 實例然后創(chuàng)建數(shù)據(jù)庫對象）

（1）SQL Server 2008默認(rèn)情況下禁用了CLR集成的功能，必需先啟用CLR集成后才能在SQL Server 訪問.NET對象。

啟用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ò)位置以及與程序集對應(yīng)的清單文件名。

<assembly_bits>：表示組成程序集和依賴程序集的二進(jìn)制值的列表。

permission_set={safe|external_access|unsafe ：表示指定SQL Server 訪問程序集時相程序集授予的一組訪問權(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ù)、存儲過程、觸發(fā)器、用戶定義的類型以及用戶自定義聚合函數(shù)時，該程序集是否可見。如果設(shè)置為OFF則程序集只能由其他程序集調(diào)用。unchecked data ：默認(rèn)情況下，如果alter assembly 必須驗證各個表行的一致性，則他將失敗。該選項使得用戶可以通過使用DBCC CHECKTABLE將檢查推遲到以后的某個時間進(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)試文件等。但如果該程序集被其他對象引用則返回錯誤。

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)

要創(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)建引用注冊程序集的函數(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接收到的任何一個參數(shù)為null時，它可以返回null，而無須實際調(diào)用函數(shù)體。 優(yōu)先采用create function語句指示的屬性。不能為表值函數(shù)指定Onnullcall屬性。

2、創(chuàng)建CLR表值函數(shù)
（1）使用C#編寫CLR表值函數(shù)
CLR表值函數(shù)只返回一個表，在.NET中中創(chuàng)建對應(yīng)的函數(shù)，返回的結(jié)果是一個IEnumerable接口，用于表示一個集合。集合中是對象的實例并不是SQLServer中所識別的表，因此需要在函數(shù)的屬性中指定FillRowMethodName,這個參數(shù)的值是用于將.NET中的對象轉(zhuǎn)換為表列的函數(shù)名。即將特性[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillSplitTable")]放置與于表值函數(shù)的頭部，以指定該特性下的函數(shù)為CLR表值函數(shù)。其中，F(xiàn)illSplitTable是將.NET 中的對象轉(zhuǎn)換為表列的函數(shù)名。還有用于將.NET中的對象轉(zhuǎn)換為表列的方法必須為靜態(tài)方法。第一個參數(shù)必須為System.Object類型，接下來的參數(shù)的個數(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存儲過程(Procedure)

1、使用C#編寫CLR存儲過程所需的函數(shù)：
在C#中編寫可用于CLR存儲過程引用的函數(shù)必須使用SqlProcedure屬性標(biāo)識。存儲過程不需要返回值，所以在C#中建立void函數(shù)即可。存儲過程一般用于查詢并生成一個查詢的表，在c#中需要使用SqlPipe對象將表格結(jié)果與信息傳回給客戶端。一般，通過SqlContext類的Pipe屬性獲得SqlPipe對象，后調(diào)用Pipe對象的Send()方法將表格結(jié)果或信息傳送給客戶端，或者使用SqlPipe對象的ExecuteAndSend()方法將查詢結(jié)果傳送給客戶端。ExecuteAndSend()方法提供了一種高效率的方式將查詢結(jié)果傳送給客戶端。使用特性[Microsoft.SqlServer.Server.SqlProcedure]放置在存儲過程調(diào)用的函數(shù)的頭部，用以標(biāo)示該函數(shù)是作為CLR存儲過程被調(diào)用的，CLR存儲過程對應(yīng)的函數(shù)。將C#編寫的代碼編譯成DLL文件，并添加到數(shù)據(jù)庫中。

2、在SQL Server中使用CLR存儲過程

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存儲過程

存儲過程中也可以使用output參數(shù)，帶有output的參數(shù)的值在存儲過程內(nèi)部被修改后也會將修改應(yīng)用到存儲過程外部相當(dāng)于指針和ref參數(shù)。output參數(shù)對應(yīng)于C#中的ref參數(shù)。

4、在T-SQL中使用CLR存儲過程

?

?

五、創(chuàng)建CLR觸發(fā)器(Trigger)

觸發(fā)器是數(shù)據(jù)庫服務(wù)器中發(fā)生時間事自動執(zhí)行的特殊存儲過程。

DML觸發(fā)器：如果用戶通過DML事件數(shù)據(jù)，則執(zhí)行DML觸發(fā)器。DML事件是針對表或視圖的insert、update 、或delete語句。

DDL觸發(fā)器：用于響應(yīng)各種DDL事件，主要是create、alter、drop語句。

??????? 1、使用C#編寫CLR觸發(fā)器

????????? 為了能夠在C#中處理觸發(fā)器觸發(fā)時的情況，Microsoft.SqlServer.Server命名空間提供了SqlTriggerContext 類。SqlTriggerContext 類提供所激發(fā)的觸發(fā)器的上下文信息，通過SqlContext.TriggerContext來獲得。通過TriggerAction來獲得觸發(fā)的類型，SqlTriggerContext.TriggerAction 屬性指示激發(fā)觸發(fā)器的操作。在使用C#編寫CLR觸發(fā)器是有可能用到觸發(fā)器中的倆張?zhí)厥獾谋恚篿nsert和deleted的時候需要使用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)常需要對數(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ù)。此外創(chuàng)建的聚合函數(shù)還必須是可序列化的，使用特性[Serializable]標(biāo)識。

聚合函數(shù)實際上是一個結(jié)構(gòu)類型或者說聚合函數(shù)對應(yīng)的是一個struct類型而不是一個方法，在其中必須實現(xiàn)4個方法：

（1)Init()初始化函數(shù): 為要處理的每組行調(diào)用Init()方法。在這個方法中，為要計算的每組行進(jìn)行初始化；

（2）Accumulate()定義具體聚合操作的函數(shù): 為所有組中的每個值調(diào)用這個方法。這個方法的參數(shù)必須是正確的累加類型，還可以上用戶定義的類型。該函數(shù)定義聚合函數(shù)的具體聚合操作；

（3）Merge()合并函數(shù): 聚合的結(jié)果必須和另一個聚合結(jié)果合并起來，調(diào)用Merge()方法。

（4）Terminate()結(jié)束函數(shù): 在處理每一組的最后一行后，調(diào)用該方法。這里，聚合的結(jié)果必須用正確的數(shù)據(jù)類型返回。

編寫好聚合函數(shù)后重新編譯整個項目將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ù)值或返回值的任意一個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)名稱和該程序集中實現(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中的變量或例程（存儲過程、觸發(fā)器等）參數(shù)的類型。用戶定義類型實例可以是表中的列，比處理、函數(shù)或存儲過程中的變量，或者函數(shù)或者存儲過程的參數(shù)。

1、使用C#定義類型

用戶定義類型必須實現(xiàn)接口INullable,申明IsNull屬性表示該類型是否為空值，而且用戶定義類型在C#中用一個可序列化的結(jié)構(gòu)體表示，這點和CLR用戶自定義聚合函數(shù)相同。編寫好C#代碼后進(jìn)行編譯生成DLL文件并更新到數(shù)據(jù)庫中。

2、在SQL Server中使用CLR用戶定義類型

要創(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

?

.NET編程和SQL Server ——Sql Server 與CLR集成 （學(xué)習(xí)筆記整理-1）