Sql Server 與CLR整合

標籤：

.NET編程和SQL Server ——Sql Server 與CLR整合  一、SQL Server 為什麼要與CLR整合

1、 SQL Server 提供的預存程序、函數等十分有限，經常需要外部的代碼來執行一些繁重的移植；

2、與CLR整合可將原本需要獨立的程式來實現的功能遷移到SQL Server 內部進行資料操作；

3、T-SQL資料查詢語言在返回資料集方面很好，但是除此之外表現不佳。與CLR的整合可解決這一問題；

4、.NET的作業碼和執行的速度比T-SQL快的很多。.NET程式是已經編譯好的二進位代碼，而不是作為預存程序來構建，不再編譯就直接可運行。

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

只有在添加了程式集後才能在該程式集的基礎上建立CLR預存程序、CLR函數等。

1、CLR代碼（編譯）→DLL檔案（註冊）→SQL Server （作為資料庫物件）→執行資料庫操作 過程如下：

（1）將託管程式編寫為一組類定義。編寫好代碼後編譯成一個DLL檔案；

預存程序、使用者自訂函數、觸發器的編寫為類的靜態方法；

使用者自訂類型、彙總函式編寫為一個結構體。

（2）DLL檔案上傳SQL Server 磁碟上，並使用create assembly 將DLL程式集儲存到系統目錄；

（3）建立SQL對象（函數、預存程序、觸發器等）並將其綁定到程式集的進入點；

預存程序：create procedure

使用者自訂函數：create function

觸發器：create trigger

使用者自訂類型：create type

彙總函式：create aggregate

(4)像使用T-SQL常式一樣使用。

2、SQL Server 中的程式集（建立程式集並上傳到SQL Server 執行個體然後建立資料庫物件）

（1）SQL Server 2008預設情況下禁用了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>：表示程式集所在的本地位置或網路位置以及與程式集對應的資訊清單檔名。

<assembly_bits>：表示組成程式集和依賴程式集的二進位值的列表。

permission_set={safe|external_access|unsafe ：表示指定SQL Server 訪問程式集時相程式集授予的一組存取權限，預設值為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}：指示在建立CLR函數、預存程序、觸發器、使用者定義的類型以及使用者自訂彙總函式時，該程式集是否可見。如果設定為OFF則程式集只能由其他程式集調用。unchecked data ：預設情況下，如果alter assembly 必須驗證各個表行的一致性，則他將失敗。該選項使得使用者可以通過使用DBCC CHECKTABLE將檢查延遲到以後的某個時間進行。

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）刪除程式集

刪除程式集是，將從資料庫中刪除程式集和它的所有關聯檔案，如，原始碼和調試檔案等。但如果該程式集被其他對象引用則返回錯誤。

drop assembly assembly_name[,....n] 
[with no dependents]

其中， with no dependents ：表示只刪除assembly_name而不刪除該程式集引用的相關程式集。如果不指定它，則drop assembly 將刪除assembly_name和所有相關程式集。

三、建立CLR函數(Function)

　　要建立被SQL Server 引用的CLR程式則需要引用Microsoft.SqlServer.Server命名空間，建立CLR函數還需要使用該命名空間下的SqlFunctionAttribute屬性類別即將[Microsoft.SqlServer.ServerSqlFunction.]放置CLR函數的頭部。

1、建立CLR純量值函式

（1）使用C#編寫CLR純量值函式在VS2010中建立CLR函數後，編譯成DLL檔案，並將該檔案添加到資料庫中。

（2）在SQL Server中使用CLR純量值函式 使用create function建立引用註冊程式集的函數。

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

　　其中external name assembly_name.class_name.method_name：指定將程式集與函數綁定的方法。<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 ]:指定純量值函式的onNULLCall屬性。如果未指定，則預設值為 called on null input。這意味著即使傳遞的參數為null，也將執行函數體。如果在CLR函數中指定了returns null on null input ,它指示當SQL Server接收到的任何一個參數為null時，它可以返回null，而無須實際調用函數體。 優先採用create function語句指示的屬性。不能為資料表值函式指定Onnullcall屬性。

2、建立CLR資料表值函式 
（1）使用C#編寫CLR資料表值函式 
CLR資料表值函式只返回一個表，在.NET中中建立對應的函數，返回的結果是一個IEnumerable介面，用於表示一個集合。集合中是對象的執行個體並不是SQLServer中所識別的表，因此需要在函數的屬性中指定FillRowMethodName,這個參數的值是用於將.NET中的對象轉換為表列的函數名。即將特性[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillSplitTable")]放置與於資料表值函式的頭部，以指定該特性下的函數為CLR資料表值函式。其中，FillSplitTable是將.NET 中的對象轉換為表列的函數名。還有用於將.NET中的對象轉換為表列的方法必須為靜態方法。第一個參數必須為System.Object類型，接下來的參數的個數就是列的個數。同時接下來的參數都必須聲明為ref參數。SQLServer中返回的列的資料類型和順序必須與該函數中ref參數的資料類型和順序相 同。編寫完後編譯成DLL檔案並添加到資料庫中。 
（2)在SQLServer中使用CLR資料表值函式 
A、更新程式集 
要在SQLServer中使用C#編寫的CLR資料表值函式，必須先更新程式集。 
如： 
alter assembly assembly_name 
from ‘程式集地址‘ 
with permission_set=safe 
B、建立CLR資料表值函式 
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函數的表資料類型。表聲明僅包含列名稱和資料類型。表始終放在主檔案組中。 order(<order_clause>)指定從資料表值函式中返回結果的順序。

3、在T-SQL中使用CLR函數 

四、建立CLR預存程序(Procedure)

1、使用C#編寫CLR預存程序所需的函數： 
　　在C#中編寫可用於CLR預存程序引用的函數必須使用SqlProcedure屬性標識。預存程序不需要傳回值，所以在C#中建立void函數即可。預存程序一般用於查詢並產生一個查詢的表，在c#中需要使用SqlPipe對象將表格結果與資訊傳回給用戶端。一般，通過SqlContext類的Pipe屬性獲得SqlPipe對象，後調用Pipe對象的Send()方法將表格結果或資訊傳送給用戶端，或者使用SqlPipe對象的ExecuteAndSend()方法將查詢結果傳送給用戶端。ExecuteAndSend()方法提供了一種高效率的方式將查詢結果傳送給用戶端。使用特性[Microsoft.SqlServer.Server.SqlProcedure]放置在預存程序調用的函數的頭部，用以標示該函數是作為CLR預存程序被調用的，CLR預存程序對應的函數。將C#編寫的代碼編譯成DLL檔案，並添加到資料庫中。

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必須存在與該程式集中，而且指定的方法必須為該類的靜態方法。

<procedure_option>::=[encryption] [recompile]

3、建立有output參數的CLR預存程序

預存程序中也可以使用output參數，帶有output的參數的值在預存程序內部被修改後也會將修改應用到預存程序外部相當於指標和ref參數。output參數對應於C#中的ref參數。

4、在T-SQL中使用CLR預存程序

 

 

五、建立CLR觸發器(Trigger)

觸發器是資料庫伺服器中發生時間事自動執行的特殊預存程序。

DML觸發器：如果使用者通過DML事件數目據，則執行DML觸發器。DML事件是針對錶或視圖的insert、update 、或delete語句。

DDL觸發器：用於響應各種DDL事件，主要是create、alter、drop語句。

        1、使用C#編寫CLR觸發器

          為了能夠在C#中處理觸發器觸發時的情況，Microsoft.SqlServer.Server命名空間提供了SqlTriggerContext 類。SqlTriggerContext 類提供所激發的觸發器的上下文資訊，通過SqlContext.TriggerContext來獲得。通過TriggerAction來獲得觸發的類型，SqlTriggerContext.TriggerAction 屬性指示激發觸發器的操作。在使用C#編寫CLR觸發器是有可能用到觸發器中的倆張特殊的表：insert和deleted的時候需要使用SqlCommand.如:

SqlConnection connection = new SqlConnection("context connection=true");connection.Open();//開啟連結SqlCommand sqlcom=new SqlCommand();sqlcom.CommandText="Select * from "+"inserted"; //使用到inserted表reader=sqlcom.ExecuteReader();//執行SQL語句reader.Read();//讀取資料for(int columnNumber=0;columnNumber<triggerContext.ColumnCount; columnNumber++){ //將每一列的列名通過pipe.Send方法發送到用戶端Pipe.Send("Update Column"+reader.GetName(columnNumber)+"?"+triggerContext.IsUpdateColumn(columnNumber).Tostring());}reader.Close();//關閉連結 將C#編寫的代碼編譯成DLL檔案後添加到資料庫並更新SQL Server中的程式集。2、在SQL Server中使用CLR觸發器將程式集中的觸發器函數添加到SQL Server中，需要用到create trigger命令。

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用於指定程式集與觸發器綁定的方法。該方法不帶任何參數而且必需返回空值。

       3、在T-SQL中使用CLR觸發器

 

 

 

六、建立使用者定義彙總函式(Aggregate)

在SQL Server中，經常需要對資料按組進行自訂的彙總操作，預設的彙總函式只有SUM()，MAX()，MIN(),AVG()等，因此就需要定義使用者自訂彙總函式。

1、使用C#編寫彙總函式

建立使用者自訂彙總函式必須使用特性[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]放置彙總函式的頭部，以標識該函數是使用者自訂彙總函式。此外建立的彙總函式還必須是可序列化的，使用特性[Serializable]標識。

彙總函式實際上是一個結構類型或者說彙總函式對應的是一個struct類型而不是一個方法，在其中必須實現4個方法：

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

（2）Accumulate()定義具體彙總操作的函數: 為所有組中的每個值調用這個方法。這個方法的參數必須是正確的累加類型，還可以上使用者定義的類型。該函數定義彙總函式的具體彙總操作；

（3）Merge()合并函數: 彙總的結果必須和另一個彙總結果合并起來，調用Merge()方法。

（4）Terminate()結束函數: 在處理每一組的最後一行後，調用該方法。這裡，彙總的結果必須用正確的資料類型返回。

編寫好彙總函式後重新編譯整個項目將DLL檔案添加的資料庫中。後使用alter assembly命令將彙總到SQL Server的程式集中。

2、在SQL Server中建立使用者自訂彙總函式

在SQL Server中建立使用者自訂彙總函式以引用CLR中的彙總函式。建立使用者自訂彙總函式使用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：表示要存放輸入參數值或傳回值的任意一個SQL Server系統純量資料型別。除了text、ntext和image之外的所有純量資料型別，都可以用作自訂彙總函式的參數。不能指定非標量類型（如cursor和table）。 
udt_schema_name：表示CLR使用者定義型別所屬的架構的名稱。如果未指定則資料庫按以下順序引用udt_schema_name：本機SQL類型命名空間、當前資料庫中目前使用者的預設架構、當前資料庫中的dbo架構。

udt_type_name：表示當前資料庫中以建立的CLR使用者自訂類型的名稱。如果未指定udt_schema_name，則SQL Server假定該類型屬於目前使用者的架構。

assembly_name [.class_name] ：表示指定與使用者定義的彙總函式綁定在一起的程式集以及（可選）該程式集所屬的架構名稱和該程式集中實現該使用者定義彙總函式的類名稱。

3、在T-SQL中使用使用者自訂彙總函式

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 

七、建立CLR使用者定義型別（UDT）

建立CLR使用者自訂類型來擴充SQL的類型系統，UDT可用於定義表中的列的類型或T-SQL中的變數或常式（預存程序、觸發器等）參數的類型。使用者定義型別執行個體可以是表中的列，比處理、函數或預存程序中的變數，或者函數或者預存程序的參數。

1、使用C#定義類型

使用者定義型別必須實現介面INullable,申明IsNull屬性工作表示該類型是否為空白值，而且使用者定義型別在C#中用一個可序列化的結構體表示，這點和CLR使用者自訂彙總函式相同。編寫好C#代碼後進行編譯產生DLL檔案並更新到資料庫中。

2、在SQL Server中使用CLR使用者定義型別

要建立CLR使用者定義型別需使用create type命令，不僅可以建立基於SQL資料類型的使用者自訂類型，也可以建立基於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

Sql Server 與CLR整合