MS SQL Server CLR 開發SQL CLR

最後更新：2018-12-05 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

SQL CLR一、配置 SQL Server，使之允許 CLR 整合:

　　1.單擊[開始] 按鈕，依次指向“所有程式”、Microsoft SQL Server 2005 和“組態工具”，然後單擊“介面區配置器”。

　　2.在 SQL Server 2005 介面區配置器工具中，單擊“功能的介面區配置器”。

　　3.選擇您的伺服器執行個體，展開“資料庫引擎”選項，然後單擊“CLR 整合”。

　　4.選擇“啟用 CLR 整合”。

此外，您可以在 SQL Server 中運行以下查詢（此查詢需要 ALTER SETTINGS 許可權）:

USE [database] sp_configure 'clr enabled', 1; GO RECONFIGURE; GO

二、建立 SQL Server Project，設定資料庫串連資訊。右鍵開啟該項目屬性，選擇“database ”，將Permission Level設為“unsafe”

三、

項目建立後添加一個.cs 檔案，如下示範如何進行資料表值函式擴充

using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Server; using System.Diagnostics; using System.Data.SqlTypes; using System.Collections;

public partial class FuncTest
{
    //資料表值函式定義.第一個方法 (InitMethod) 賦予 SqlFunction 屬性，用於將它指定為該資料表值函式的進入點
    //此方法必須返回 IEnumerable 或 IEnumerator 對象。該對象包含將用於填充返回表的資料。
    //執行該函數時，SQL Server 將逐一查看 IEnumerator 對象中的每個對象，並使用它來填充資料行。
    //為此，它要將該對象傳遞到該類中的第二個方法 FillRow。此方法會將該對象轉換成返回表中的某一行。
    //此方法在 SqlFunction 屬性的 FillRowMethodName 參數中指定
    //部署項目後在資料庫中會產生相應的資料表值函式dbo.ReadEventLog
    [SqlFunction(TableDefinition = "logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint",
                Name = "ReadEventLog", FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname, Environment.MachineName).Entries;
    }
    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

項目建立後添加一個Stored Procedure 檔案，如下示範如何進行Stored Procedure擴充

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    /// <summary>
    ///
    /// </summary>
    /// <param name="greeting">return value</param>
    public static void SayHello(ref string greeting)
    {
        SqlMetaData columnInfo = new SqlMetaData("ColumnName", SqlDbType.NVarChar, 12);
        SqlDataRecord greetingRecord = new SqlDataRecord(new SqlMetaData[] { columnInfo });
        greetingRecord.SetString(0, "HelloWorld");
        // 呼叫 Pipe 對象的 Send 方法將SqlDataRecord 對象直接傳送給用戶端
        SqlContext.Pipe.Send(greetingRecord);
        //輸出參數
        greeting = "Nice to see you!";
    }
}

四、編譯產生dll檔案，並部署到資料庫中。(Build---->Deploy projectname)

這樣就可以將project部署到資料庫中對應的程式集，函數，預存程序了

SQL Script產生程式集，函數如下

CREATE ASSEMBLY HelloWorld
from 'd:\HelloWorld.dll'
WITH PERMISSION_SET = unsafe
Go

CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE
(
logTime datetime,
[Message] nvarchar(4000),
Category nvarchar(4000),
InstanceId bigint
)
AS
EXTERNAL NAME HelloWorld.FuncTest.InitMethod
GO

CREATE PROCEDURE [dbo].[Hello]
@greeting [nvarchar](4000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SSP].[StoredProcedures].[Hello]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'Hello'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Hello.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'Hello'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'Hello'

一、配置 SQL Server，使之允許 CLR 整合: