SQL Server 2005中的CLR(1)

簡介

        Microsoft 通過宿主 Microsoft .NET Framework 2.0 公共語言運行庫 (CLR)，SQL Server 2005顯著地增強了數據庫編程模型的功能。它支持用任何 CLR 語言（特別是 Microsoft Visual C# .NET、Microsoft Visual Basic .Net 和 Microsoft Visual C++）編寫過程、觸發器和函數。同時，它還提供了擴展數據庫系統中的類型系統和聚合函數的功能，方法是允許應用程序創建它們自己的數據類型和聚合函數。

CLR 集成概述

Visual Studio 2005  支持在 SQL Server 2005 中開發、部署和調試托管代碼。有一種新的項目類型（稱為 SQL Server 項目），它允許開發人員在 SQL Server 中開發、部署和調試例程（函數、過程和觸發器）、類型和聚合。

構建和部署

SQL Server 項目提供了代碼模板，這使得開發人員能夠輕松地開始為基於 CLR 的數據庫例程、類型和聚合編寫代碼。該項目還允許添加對數據庫中其他的程序集的引用。在構建項目時，可以將其編譯成一個程序集。部署此程序集可以將程序集的二進制文件上載到與該項目相關聯的 SQL Server 數據庫中。部署操作還自動創建在數據庫的程序集中定義的例程、類型和聚合，方法是使用在代碼中定義的自定義屬性（SqlProcedure、SqlFunction 和 SqlTrigger 等等）。它還上載與該程序集相關聯的源代碼和 .pdb 文件（調試符號）。

調試

對於任何平台來說，調試都是開發人員體驗的基本部分。SQL Server 2005 和 Visual Studio 2005 為數據庫編程人員提供了這些功能。調試 SQL Server 2005 對象的關鍵部分在於其易於安裝和使用。調試到運行 SQL Server 的計算機的連接在很大程度上同調試傳統操作系統中運行的進程的方式一樣。調試器的功能不會受到客戶端到服務器的連接類型的影響。這樣就可以調試表格數據流 (TDS) 和 HTTP 連接。而且，還可以跨語言進行無縫調試。因此，如果有一個調用 CLR 存儲過程的 T-SQL 存儲過程，調試會允許您從 T-SQL 過程進入到 CLR 過程。

     在這裡我只是簡單的介紹一下吧，其實還有很多這方面的知識，大家可以到微軟的官方網站上去查看一下我給個鏈接吧方便查找

http://www.microsoft.com/china/msdn/library/data/sqlserver/sqlclrguidance.mspx?mfr=true 這上面說的很細，包括Clr和Xp的特性也有介紹

     

     在這裡我分享一下我經常使用的一些功能 吧，CLR 集成到低是做什麼的呢？大家平時在數據庫裡寫的存儲過程 ，函數，還有觸發器等這些東西都 是存在於數據庫裡的，不能跟前台的程序代碼發生關系，Clr大家都 知道是支持語言的基本環境，有了他，我們就可以在Sql裡部署我們用c#或是VB編寫的代碼了，也就是說我們在c#裡寫一個函數可以在Sql裡直接被執行，大家是不是感覺這樣很神奇呢？如果真的是這樣的話，那以後數據庫裡再有什麼復咋的邏輯直接用c#代碼寫一定會很方便，下面我就一個一個的很大家分享一下吧，我們先從函數入手吧

　　CLR中的函數

第一步 建立環境

   首先我們要打開Sql2005的IDE，新建 一個數據庫，這個過程 就不多說了，數據庫名稱 是 Text

   下在我們打開VS2005 或是2008（選擇.Net2.0因為這裡只支持2.0的CLR）新建項目

  

  在這裡跟新建其它項目就有很大不同的，我們要選擇一個Office下的數據庫，選擇SqlServer項目  名稱為 SqlClrProject 位置您可以自己選擇電腦上的任意位置就可以了，單擊確定

在這裡我們要選擇一下自己的數據，如果你不是第一次的話那麼會默認的列出所有選擇過的數據庫，而這裡我的選擇過了所以出現一個 已有的選擇項，當然如果 你是第一次的話 是沒有選擇項目的，這時我們單擊  添加新引用（A）... 

相信這裡大家都 很熟悉了吧，選擇一下我們剛剛建好的數據庫吧，

然後單擊確定

在這裡我們單擊是就可以了，因為我們得啟用它來調試我們下面的操作

新建好的項目結構如上圖所顯

   因為我們這裡要使用函數，所以我們右擊項目---添加

我們可以看的到裡面已經出了很多數據庫中常用的對象了，

我們單擊新建項也就是第一個

我們給函數名稱改為StrCount意思就是統計一下輸入的字符個數

我們選擇的是用戶定義的函數

好了單擊添加就可以了，

系統會自動生成一段代碼

 [Microsoft.SqlServer.Server.SqlFunction]
這句是表示在Sql中的對象類型

代碼 using System;
using System.Data;
using System.Data.SqlClIEnt;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{

    //表示注冊為Sql中的函數
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString StrCount()
    {
        // 在此處放置代碼
        return new SqlString("Hello");
    }
};

 

到這裡我們的前期准備工作就完了，我們只要修改這個類就行了，

 

SqlFunction  就是函數

SqlProcedure  存儲過程

SqlTrigger  觸發器

還有其它的大家自己查看一下吧

第二步 部署自己定義函數

      我們這個函數是用來計算字符長度的我把完成的代碼放在下在面

 

代碼 using System;
using System.Data;
using System.Data.SqlClIEnt;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{

    //表示注冊為Sql中的函數
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string StrCount(string str)
    {
        // 返回字符串的長度
        return str.Length.ToString();
    }
};

 

現在函數寫好了，這個函數就不做過多的解釋了， 因這這個只是得到字符串的長度這東西太基礎了，呵呵

我們怎麼樣才能在Sql2005裡使用我們這個函數呢？

其實很簡單我們生成一下項目，生成成功之後，我們右擊項目

單擊部署項目，等部署成功就可以了。那我們部署的程序在那裡呢，現在我們打開Sql2005IDE

 找到如下圖所示

我們會發現在Sql2005裡的標題值函數裡出現了一個我們自己定義的函數，那怎麼使用他呢，很簡單其實是跟我們平時使用的是一樣的

我們新建查詢

因為我們Sql默認的是關閉CLR功能的，我們需要用命令開啟一下

看到如上圖所顯示的就表示 你的功能開啟成功了，下面我們就可以自由的使用函數了，跟使用系統自己定義的函數是一樣的，下面我統計幾個大家可以看下圖

 

 

 

  這樣一這樣

 

 這樣就是利用sql的ClR執行c#程序了，是不是很方便，當然我們可以根據自己的需要把這個函數改的復咋一下，不過方法都是一樣的像存儲過程和觸發器的實現原理都 也是這樣的。

    我加上一段，調試功能 是.Net裡用來解決問題最多的功能了，那CLR是否支持呢？答案是肯定的，只要我們在這個Text.Sql文件裡打個段點就行了，因為在這個文件裡執行的結果和數據庫裡的是一樣的

 

代碼 -- 運用此程序集實現的不同 SQL 對象的查詢示例

-----------------------------------------------------------------------------------------
-- 存儲過程
-----------------------------------------------------------------------------------------
-- exec StoredProcedureName


-----------------------------------------------------------------------------------------
-- 用戶定義的函數
-----------------------------------------------------------------------------------------
-- select dbo.FunctionName()


-----------------------------------------------------------------------------------------
-- 用戶定義的類型
-----------------------------------------------------------------------------------------
-- CREATE TABLE test_table (col1 UserType)
-- go
--
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 1'))
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 2'))
-- INSERT INTO test_table VALUES (convert(uri, 'Instantiation String 3'))
--
-- select col1::method1() from test_table

-----------------------------------------------------------------------------------------
-- 用戶定義的類型
-----------------------------------------------------------------------------------------
-- select dbo.AggregateName(Column1) from Table1

select '要運行項目，請編輯項目中的 Test.sql 文件。此文件位於解決方案資源管理器的 Test Scripts 文件夾中。'

    我們可以跟自己的Sql語句結合起來使用，就是用到我們自己定義的存儲過程裡這樣可以把復咋的邏輯用c#代碼來實現是不是感覺很方便，Clr是個好東西，他不僅僅只有這些，還有什麼高級的功能 呢，我們下次博文接著說吧!