SQL Server數(shù)據(jù)庫(kù)托管代碼的使用是本文我們主要要介紹的內(nèi)容，我們知道，SQL Server是一個(gè)非托管的應(yīng)用程序，但是開發(fā)人員可以用托管代碼創(chuàng)建數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程、觸發(fā)器或者函數(shù)。這樣開發(fā)人員就可以利用其他語(yǔ)言來(lái)編寫數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程、觸發(fā)器或者函數(shù)，不僅可以使用FCL或其他程序集中的類型，還可以享受.net framerwork中強(qiáng)大類庫(kù)的功能。

當(dāng)***請(qǐng)求一個(gè)用托管代碼寫的存儲(chǔ)過(guò)程時(shí)， MSSQL會(huì)加載CLR，存儲(chǔ)過(guò)程會(huì)在獨(dú)立的應(yīng)用程序域運(yùn)行，這樣不會(huì)對(duì)數(shù)據(jù)庫(kù)本身產(chǎn)生影響。托管代碼會(huì)被JIT編譯成本地代碼執(zhí)行，而不是解釋執(zhí)行，這保證了托管代碼的執(zhí)行效率。

編寫托管代碼

為了使用托管代碼創(chuàng)建數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程、觸發(fā)器或者函數(shù)，只需要對(duì)托管代碼中具體方法使用Microsoft.SqlServer.Server命名空間下的特性：[SqlProcedure]、[SqlFunction]或者[SqlTrigger]。當(dāng)然方法的參數(shù)和返回值相應(yīng)會(huì)有一定的限制。

比如寫一個(gè)隨機(jī)散列算法的方法，聲明成MSSQL的存儲(chǔ)過(guò)程：

GetHashText方法   
 
namespace Wuhong.ClrSp  
{  
public class StoredProcedures  
{  
[Microsoft.SqlServer.Server.SqlProcedure]  
public static void GetHashText(string plainText, out string encryptedText, ref int hashProvider, ref string saltText)  
{  
//方法體略……  
 }  
 }  
} 

至此，可以利用托管代碼完成一切數(shù)據(jù)庫(kù)腳本所無(wú)法完成的功能。

特別的，托管代碼中也可以訪問(wèn)當(dāng)前的數(shù)據(jù)庫(kù)，但與普通方法訪問(wèn)數(shù)據(jù)庫(kù)有兩處不同：

一是連接字符串要使用上下文鏈接(Context Connection)，"context connection=true"。這表示代碼中的數(shù)據(jù)庫(kù)上下文跟托管代碼調(diào)用方的數(shù)據(jù)庫(kù)上下文一致。

二是將結(jié)果集、消息或命令執(zhí)行結(jié)果發(fā)送回客戶端時(shí)需要通過(guò)數(shù)據(jù)庫(kù)上下文專門的管道對(duì)象SqlContext.Pipe來(lái)發(fā)送。

開啟CLR集成

默認(rèn)情況下， CLR集成功能在MSSQL中處于禁用狀態(tài)。若要使用 SQL CLR 項(xiàng)目項(xiàng)，必須啟用 CLR 集成。

sp_configure  
 
sp_configure 'clr enabled',1  
GO  
 RECONFIGURE  
GO 

創(chuàng)建程序集

生成托管代碼程序集后，需要在MSSQL中創(chuàng)建相應(yīng)的托管應(yīng)用程序模塊，將其作為MSSQL實(shí)例中的對(duì)象。通過(guò)引用此模塊，可在數(shù)據(jù)庫(kù)中創(chuàng)建CLR函數(shù)、存儲(chǔ)過(guò)程、觸發(fā)器。

創(chuàng)建ASSEMBLY

CREATE ASSEMBLY [Wuhong.ClrSp]   
FROM 'F:\website\Wuhong.ClrSp.dll'  
WITH PERMISSION_SET = SAFE; 

WITH PERMISSION_SET指定MSSQL向程序集授予的一組代碼訪問(wèn)權(quán)限。SAFE是默認(rèn)值。

SAFE 是***限制性的權(quán)限集。由具有 SAFE 權(quán)限的程序集所執(zhí)行的代碼將無(wú)法訪問(wèn)外部系統(tǒng)資源，例如文件、網(wǎng)絡(luò)、環(huán)境變量或注冊(cè)表。

EXTERNAL_ACCESS 使程序集可以訪問(wèn)某些外部系統(tǒng)資源，例如文件、網(wǎng)絡(luò)、環(huán)境變量以及注冊(cè)表。UNSAFE 可使程序集不受限制地訪問(wèn)資源，無(wú)論是MSSQL實(shí)例內(nèi)部還是外部的資源都可以訪問(wèn)。從 UNSAFE 程序集內(nèi)運(yùn)行的代碼可調(diào)用未托管代碼。

創(chuàng)建托管應(yīng)用程序集的操作也可以在MSSQL Management Studio中手工完成。

創(chuàng)建存儲(chǔ)過(guò)程

下面是根據(jù)創(chuàng)建的托管模塊來(lái)創(chuàng)建存儲(chǔ)過(guò)程。

創(chuàng)建PROCEDURE

CREATE PROCEDURE [dbo].[GetHashText]  
@plainText [nvarchar](4000),  
@encryptedText [nvarchar](4000) OUTPUT,  
@hashProvider [int] OUTPUT,  
@saltText [nvarchar](4000) OUTPUT  
WITH EXECUTE AS CALLER  
AS  
EXTERNAL NAME [Wuhong.ClrSp].[StoredProcedures].[GetHashText]  
GO 

WITH EXECUTE AS指定執(zhí)行模塊的上下文，可以控制 數(shù)據(jù)庫(kù)引擎使用哪一個(gè)用戶帳戶來(lái)驗(yàn)證對(duì)模塊引用的對(duì)象的權(quán)限。CALLER是默認(rèn)值。

CALLER指定模塊內(nèi)的語(yǔ)句在模塊調(diào)用方的上下文中執(zhí)行。這表示執(zhí)行模塊的用戶不僅必須對(duì)模塊本身?yè)碛羞m當(dāng)?shù)臋?quán)限，還要對(duì)模塊引用的任何數(shù)據(jù)庫(kù)對(duì)象擁有適當(dāng)權(quán)限。

OWNER指定模塊內(nèi)的語(yǔ)句在模塊的當(dāng)前所有者上下文中執(zhí)行。

SELF指定模塊內(nèi)的語(yǔ)句在創(chuàng)建或更改模塊的用戶的上下文中執(zhí)行。

'user_name'指定模塊內(nèi)的語(yǔ)句在user_name指定的用戶的上下文中執(zhí)行。

關(guān)于SQL Server數(shù)據(jù)庫(kù)中托管代碼的使用方法就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

【編輯推薦】

1. SQL Server數(shù)據(jù)庫(kù)中FOR XML AUTO的使用詳解續(xù)
2. SQL Server使用UNION代替OR提升查詢性能的實(shí)例
3. SQL Server表中數(shù)據(jù)按條件批量導(dǎo)出為多個(gè)Excel文件
4. Oracle數(shù)據(jù)庫(kù)分析函數(shù)應(yīng)用實(shí)例之查找狀態(tài)全為1的ID
5. Oracle handbook系列之虛擬專用數(shù)據(jù)庫(kù)VPD的使用詳解