隨著技術(shù)的不斷升級，微軟的王牌數(shù)據(jù)庫SQL Server 2000正在逐漸淡出人們的視線，而新版的SQL Server 2005正成為企業(yè)和開發(fā)人員的新寵。本文重點(diǎn)關(guān)注SQL Server 2005的功能，尤其是CLR集成，這是開發(fā)人員經(jīng)常要使用的功能之一。

1、SQL Server 2005簡介

SQL Server 2005較SQL Server 2000有了長足進(jìn)步。SQL Server 2005帶來了大量新功能，新圖形化用戶界面（GUI）和新管理工具。下面列舉了一些簡單的功能介紹：

能夠在數(shù)據(jù)庫中托管.NET Framework中的通用語言運(yùn)行時(shí)（CLR），那么開發(fā)人員就可以使用Visual Basic 2005和C#編寫程序集。這對于SQL Server數(shù)據(jù)庫編程人員可能是件有趣的事情，以前的編程人員受限于SQL和T-SQL，而如今卻有力的暗示了應(yīng)用程序的構(gòu)建方法。

通過完全成熟的，能夠保存多個(gè)相關(guān)數(shù)據(jù)類型的XML數(shù)據(jù)類型對XML進(jìn)行深入支持。開發(fā)人員可以將XML文檔存儲到數(shù)據(jù)庫中，同時(shí)驗(yàn)證其有效性，并且抽取文檔的部分內(nèi)容。這意味著可以將半結(jié)構(gòu)化數(shù)據(jù)與關(guān)系型數(shù)據(jù)相結(jié)合，同時(shí)將它們存儲到同一地方，并采取相同方式處理它們。另外，服務(wù)器端還提供了XML查詢（XQuery）和XML架構(gòu)定義語言（XSD）標(biāo)準(zhǔn)。

完全更新的GUI管理工具SQL Server Management Studio（SSMS），該工具提供的單個(gè)集成環(huán)境可滿足配配置/管理需求。

將報(bào)表框架（SQL Server Reporting Services，縮寫為SSRS）作為數(shù)據(jù)庫整體的一部分。

為實(shí)現(xiàn)異步消息傳遞創(chuàng)建新的應(yīng)用程序框架Service Broker。

經(jīng)過大量改進(jìn)和擴(kuò)展的SQL Server Integration Services（SSIS，以前是Data Transformation Services），該工具可用于提取，轉(zhuǎn)換和加載數(shù)據(jù)（另外，關(guān)系型數(shù)據(jù)庫管理系統(tǒng)使用這個(gè)功能代價(jià)昂貴）。

以上只是介紹了SQL Server 2005的一些重點(diǎn)功能，每個(gè)功能都可以讓開發(fā)人員細(xì)細(xì)品味，限于篇幅，本文重點(diǎn)介紹其中的CLR集成功能。

在SQL Server 2005中可使用通用語言運(yùn)行時(shí)（CLR）集成，這意味著在SQL Server實(shí)例中提供對于.NET Framework編程模型的訪問是很有必要的。為此，在SQL Server 2005中引入了程序集概念。程序集是.NET編譯和托管的DLL文件。SQL Server使用程序集部署對象，例如存儲過程、用戶定義類型、觸發(fā)器和用戶定義函數(shù)。這些對象通常使用T-SQL編寫，但是而今使用多種受管語言代碼（例如VB.NET或者C#）也能夠創(chuàng)建和編寫這些對象。SQL Server 2005的這種新功能還提供了在數(shù)據(jù)庫對象（例如存儲過程，函數(shù)和類型）中，訪問經(jīng)過改進(jìn)的.NET Framework編程模型的能力。.NET Framework 2.0版本具有多方面增強(qiáng)功能，這些經(jīng)過改進(jìn)的功能有很多已經(jīng)可以為CLR集成使用。

2、受管程序集的概念

在SQL Server 2005之前，程序集也稱為受管代碼，它是一組編譯為.dll或者.exe的文件。在.NET Framework中仍然存在這個(gè)術(shù)語和特性。然而，在SQL Server 2005中，程序集術(shù)語則變得有點(diǎn)模糊。

在SQL Server范圍內(nèi)，程序集是一個(gè)引用物理程序集.dll文件的對象。受管代碼是.dll文件，該文件使用.NET Framework CLR和可訪問其他受管代碼來創(chuàng)建。更確切的說，是在SQL Server內(nèi)部的其他受管代碼。每段受管代碼都包括兩個(gè)重要的片段信息。一個(gè)是描述程序集的元數(shù)據(jù)，例如程序集方法和屬性，程序集版本號。第二個(gè)片段信息是實(shí)際的受管代碼，組成程序集的方法和屬性。通常，使用一些高級編程語言（例如C#或者Visual Basic.NET）編寫受管代碼，這些代碼共享類庫，同時(shí)被編譯為中間語言（Intermediate Language，縮寫為IL）。

程序集中的受管代碼實(shí)現(xiàn)SQL Server對象的功能，例如存儲過程、UDT、CLR函數(shù)和CLR觸發(fā)器。更為重要的是，程序集自身控制受管代碼訪問內(nèi)部和外部資源的權(quán)限級別。當(dāng)在SQL Server中利用CREATE ASSEMBLY語句創(chuàng)建程序集時(shí)，.dll文件會物理的加載到SQL Server中，這樣SQL Server引擎就能夠引用和使用程序集。SQL Server 2005中有兩個(gè)說明所創(chuàng)建程序集的表，它們是sys.assemblies和sys.assembly_files。

3、啟用CLR集成的方法

當(dāng)開發(fā)人員開始在SQL Server 2005中使用程序集之前，需要告知SQL Server已經(jīng)準(zhǔn)備好在SQL Server中與CLR交互。默認(rèn)情況下，禁用CLR集成功能，必須啟用CLR集成才能SQL Server中訪問.NET對象。

為了啟用CLR集成，在SQL Server Management Studio的查詢窗口中執(zhí)行以下代碼：

EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO

要啟用CLR集成，必須具有ALTER SETTINGS服務(wù)器權(quán)限。該權(quán)限由sysadmin和serveradmin固定服務(wù)器角色的成員顯式持有。

啟用CLR集成的另一種方法是利用SQL Server外圍應(yīng)用配置器工具。為打開這個(gè)工具，可在Microsoft SQL Server 2005配置工具菜單中選擇“SQL Server外圍應(yīng)用配置器”。要配置CLR集成，可單擊對話框底部的“功能的外部應(yīng)用配置器”。在彈出的對話框中，選擇左邊Database Engine之下的CLR集成，然后選中右邊的CLR集成復(fù)選框。單擊確定按鈕，從而啟用該選項(xiàng)，同時(shí)關(guān)閉窗口。接下來就可以使用受管代碼創(chuàng)建SQL Server中的對象了，例如創(chuàng)建存儲過程。

4、使用受管代碼創(chuàng)建存儲過程

第一個(gè)示例很簡單，其用于幫助讀者初步理解在程序集中使用SQL Server 2005的方法。首先，在C:\Projects目錄下創(chuàng)建名為sample1的文件夾。打開編輯器，輸入以下代碼，接著將其保存為HelloWorldStoredProcedures.cs：

using System; using System.Data; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; public class HelloWorldStoredProcedures { public static void HelloWorld() { SqlContext.Pipe.Send("Hello World"); } }

在HellpWorld()方法中，通過調(diào)用SqlContext類的Pipe屬性能夠獲取指向SqlPipe對象的引用。當(dāng)獲取指向SqlPipe對象的引用后，接著可將表格式結(jié)果和消息發(fā)送到客戶端。這個(gè)工作可由SqlPipe類的Send()方法完成。SqlPipe對象類似于ASP.NET中的Response對象。通過調(diào)用Send()的多種重載方法，開發(fā)人員可通過管道將數(shù)據(jù)發(fā)送給調(diào)用方應(yīng)用程序。Send()的各種重載方法如下所示：

Send(SqlDataReader)：允許以SqlDataReader對象格式發(fā)送表格式結(jié)果。

Send(SqlDataRecord)：允許以SqlDataRecord對象格式發(fā)送結(jié)果。

Send(String)：使用該方法可將消息發(fā)送給調(diào)用方應(yīng)用程序。

一旦創(chuàng)建了類，下一步是創(chuàng)建受管代碼。為此，通過開始→所有程序→Microsoft .NET Framework SDK v2.0→SDK命令提示符，可打開命令提示符窗口。

在命令提示符窗口下，訪問C:\Projects\sample1目錄，接著執(zhí)行以下命令：

CSC /target:library C:\Projects\sample1\HelloWorldStoredProcedures.cs

此時(shí)，在sample1目錄會看到一個(gè)新文件HelloWorldStoredProcedures.dll。

下一步是在SQL Server中注冊程序集。在SQL Server Management Studio中打開查詢窗口，確認(rèn)選中AdventureWorks數(shù)據(jù)庫，接著執(zhí)行以下T-SQL語句：

CREATE ASSEMBLY HelloWorld FROM 'C:\Projects\sample1\HelloWorldStoredProcedures.dll' WITH PERMISSION_SET = SAFE

CREATE ASSEMBLY語句將程序集加載到SQL Server中，此時(shí)可以在SQL Server內(nèi)部引用該程序集。在SQL Server中，可以以相同文件名稱存儲程序集的多個(gè)版本，只要每個(gè).dll文件具有不同的版本號。

需要注意的一件事情是CREATE ASSEMBLY語句是以WITH PERMISSION_SET子句結(jié)尾。該子句設(shè)置了當(dāng)SQL Server使用和訪問程序集時(shí)，對程序集的訪問權(quán)限。該子句的可用值是：

SAFE：這是默認(rèn)級別，具有最大限制性。這意味著，代碼不需要使用任何外部資源，在SQL Server內(nèi)部可以完全控制操作。SAFE代碼能夠訪問本地SQL Server數(shù)據(jù)庫中的數(shù)據(jù)，或者執(zhí)行計(jì)算，以及處理不包括訪問本地?cái)?shù)據(jù)庫之外資源的業(yè)務(wù)邏輯。階乘計(jì)算是一個(gè)好例子。階乘計(jì)算只需要整型輸入，然后返回另外一個(gè)整數(shù)。計(jì)算階乘無需打開磁盤上的文件。

EXTERNAL_ACCESS：該級別表示可以使用某些外部資源，例如文件、網(wǎng)絡(luò)、Web服務(wù)、環(huán)境變量和注冊表。因此，如果代碼計(jì)劃將某些結(jié)果寫入磁盤文件中，那么需要在SQL Server的EXTERNAL_ACCESS安全類別下注冊這些代碼。

UNSAFE：這個(gè)級別設(shè)置代碼能夠完成任何事情，因此應(yīng)該盡量避免使用。換言之，開發(fā)人員要求不做任何級別的控制，因此代碼與外部存儲過程具有相同權(quán)限。即使獲得了與外部存儲過程相同的權(quán)限，CLR仍然會為開發(fā)人員代碼一些方便。然而，這樣在邏輯上可能存在漏洞，那么黑客就能夠隨意訪問系統(tǒng)的關(guān)鍵部分。因此，應(yīng)該避免在SQL Server中使用UNSAFE代碼。

本示例代碼通過使用HelloWorldStoredProcedures.dll，同時(shí)將權(quán)限設(shè)置為SAFE創(chuàng)建了名為HelloWorld的程序集。

在創(chuàng)建程序集之后，下一步是創(chuàng)建一個(gè)使用該程序集的簡單T-SQL存儲過程。以下DDL語句為程序集創(chuàng)建了入口點(diǎn)：

CREATE PROCEDURE HelloWorld AS EXTERNAL NAME HelloWorld.HelloWorldStoredProcedures.HelloWorld 在執(zhí)行存儲過程之前，查看一下CREATE PROCEDURE語句中的EXTERNAL NAME語法，其設(shè)置了.NET程序集的方法，其語法格式如下：

Assembly_name.Class_name.Method_name

當(dāng)使用前面的示例時(shí)，程序集名稱來自CREATE ASSEMBLY，在此處是HelloWorld。第二部分是類名稱，其來自HelloWorldStoredProcedures.cs文件代碼。第三部分是方法名稱，它也來自HelloWorldStoredProcedures.cs，在本示例中是HelloWorld。

將這些片段信息一起置于EXTERNAL NAME子句，其告知存儲過程所執(zhí)行的內(nèi)容。

此時(shí)，可以準(zhǔn)備測試程序集和獲取返回?cái)?shù)據(jù)。為了測試示例，通過運(yùn)行以下語句來執(zhí)行存儲過程：

EXEC HelloWorld

雖然本示例很簡單，但是它說明了創(chuàng)建和部署程序集的基本步驟。下一個(gè)示例將在此代碼之上，說明涉及訪問SQL Server數(shù)據(jù)的更為復(fù)雜的功能。

讀者肯定在想：“必須有一種更為簡單的方法來生成和部署這些程序集”。實(shí)際上是存在這種方法的。然而，通過這些步驟手動生成和部署程序集的原因是，幫助讀者理解程序集生成過程發(fā)生的內(nèi)幕。下面將說明如何使用Visual Studio 2005專業(yè)版創(chuàng)建CLR存儲過程。

4.1 創(chuàng)建復(fù)雜CLR存儲過程

在這個(gè)示例中，將創(chuàng)建從AdventureWorks數(shù)據(jù)庫的Production.Product表返回?cái)?shù)據(jù)的存儲過程。首先，選擇“文件”→“新建項(xiàng)目”，然后在彈出窗口的左邊導(dǎo)航菜單中選擇“Visual C#”→“數(shù)據(jù)庫”，接著將項(xiàng)目模板選擇為“SQL Server項(xiàng)目”，這樣可在Visual Studio 2005中創(chuàng)建新項(xiàng)目SqlServerDataAccess。如果單擊新建項(xiàng)目對話框中的OK按鈕，那么會要求添加數(shù)據(jù)庫引用。如果AdventureWorks引用還不可用，那么可使用“添加新引用”選項(xiàng)來添加指向AdventureWorks數(shù)據(jù)庫的引用。

當(dāng)選擇啟用CLR存儲過程調(diào)試和項(xiàng)目建立后，在菜單中選擇“項(xiàng)目”→“添加存儲過程”項(xiàng)，接著設(shè)置類名稱為GetProducts.cs。一旦創(chuàng)建了類，可根據(jù)示例1修改代碼。

示例1：由CLR存儲過程返回表格式結(jié)果集