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

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

編寫托管代碼

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

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

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ù)庫腳本所無法完成的功能。

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

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

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

開啟CLR集成

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

sp_configure  
 
sp_configure 'clr enabled',1  
GO  
 RECONFIGURE  
GO 

創(chuàng)建程序集

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

創(chuàng)建ASSEMBLY

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

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

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

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

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

創(chuàng)建存儲過程

下面是根據(jù)創(chuàng)建的托管模塊來創(chuàng)建存儲過程。

創(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ù)庫引擎使用哪一個用戶帳戶來驗證對模塊引用的對象的權(quán)限。CALLER是默認(rèn)值。

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

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

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

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

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

【編輯推薦】

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