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

一、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 ( class_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.如:

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

?

.NET編程和SQL Server ——Sql Server 與CLR集成 （學習筆記整理-1）