SQL CLR的介紹與範例說明

        傳統的SQL語法由於只有定義簡單的DDL (data definition language) and DML (data manipulation languages)等，但是使用者對SQL的要求卻是日與聚增，所以各家廠商粉粉自行擴展SQL語法，如Microsoft的T-SQL、Oracle的PL/SQL等，但是這些擴展的SQL還是與程式語言有些差距，而Microsoft為了改善這個問題，在SQL Server 2005以後推出了SQL CLR，讓開發者可以透過.NET  Framework設計出各種資料庫的物件，其中包括預存程式、使用者自訂函式、觸發程式、使用者自訂型別以及使用者自訂彙總函式等功能。

SQL CLR架構

在這我透過一個簡單的範例來介紹，希望大家可以透過這個範例獲得一些啟發。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlString QueryVersion()
{
string tmp = "";
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select @@VERSION", conn);
SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())
tmp = dr[0].ToString();

dr.Close();
}
return new SqlString(tmp);
}
};


程式碼設定完成後，請透過DOC Command視窗輸入下列的指令：
C:\Windows\Microsoft.NET\Framework\v3.5> csc.exe /t:library /out:QueryVersion.dll c:\QueryVersion.cs

完成後你就會在 [C:\Windows\Microsoft.NET\Framework\v3.5] 的目錄下得到一個dll

PS：注意事項
1、在上述的語法上，原始檔 [QueryVersion.cs] 放在C磁碟機下。
2、v3.5只要看你安裝的版本而定，再請自行修正。

指令語法：
--啟用CLR
EXEC sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO
--啟用Trustworthy
Use test_db
ALTER DATABASE master SET TRUSTWORTHY ON
Go
--建立Assembly
Use test_db
Create ASSEMBLY CARY_CLR_1
FROM 'C:\QueryVersion.dll'
WITH PERMISSION_SET = SAFE
GO
--建立SQL Function
CREATE FUNCTION QueryVersion()
RETURNS NVARCHAR(4000) WITH EXECUTE AS CALLER
AS EXTERNAL NAME CARY_CLR_1.UserDefinedFunctions.QueryVersion
--執行SQL Function
select dbo.queryversion()

執行結果：

看到上述的執行結果，就代表你已成功設計出一個SQL CLR，當然SQL CLR其實還有其他許多強大的功能，如字串切割、不同語系的字元碼轉換等，後續我再將不同的CLR功能分享上來，也希望大家多多分享，謝謝了。

參考連結：
Introduction to SQL Server CLR Integration (ADO.NET)
http://msdn.microsoft.com/en-us/library/ms254498(v=VS.90).aspx
CLR User-Defined Functions
http://msdn.microsoft.com/en-us/library/ms254508(v=vs.90).aspx
How to: Create a SQL Server Project
http://msdn.microsoft.com/en-us/library/84b1se47(v=vs.90).aspx
How to: Create and Run a CLR SQL Server User-Defined Function
http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.90).aspx
How to: Create and Run a CLR SQL Server User-Defined Type
http://msdn.microsoft.com/en-us/library/a8s4s5dz(v=vs.90).aspx
TRUSTWORTHY Database Property
http://msdn.microsoft.com/en-us/library/ms187861.aspx

關鍵字：SQL Server、SQL CLR