SQL Server 2017 CLR 使用紀錄

為工作紀錄以未來參考。應用於附件上傳以CLR取檔案系統實體清單。因FileTable有一些權限上限制使得應用整合上出現困難，固有了以SQL CLR取檔案清單的想法。註：非入門級文章。

參考

SQL Server CLR 整合簡介

SqlPipe.SendResultsRow(SqlDataRecord) 方法 與 SendResultsStart, SendResultsEnd 搭配做用。

CLR 資料表值函式

原始碼留參

開發環境

IDE: Visual Studio 2019 目標平台: SQL Server 2016 目標 Framework: .NET Framework 4.5

SQL Server CLR 開發

自 Visual Studio 2017 後又改用【SQL Server 資料庫專案】來開發 SQL Server 相關的程式。

程式碼如下：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Globalization;
using System.IO;
using System.Runtime.Remoting.Messaging;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static SqlInt32 HelloWorld(SqlString name)
    {
        string message = $"Hello, {name}";
        SqlContext.Pipe.Send(message + Environment.NewLine);
        return new SqlInt32(0);
    }

    [SqlProcedure]
    public static void ListFolderFile(SqlString folderPath)
    {
        // trace
        SqlContext.Pipe.Send(@"ListFolderFile:BEGIN v1.1" + Environment.NewLine);
        SqlContext.Pipe.Send($"folderPath: {folderPath}" + Environment.NewLine);

        DirectoryInfo folder = new DirectoryInfo(folderPath.ToString());
        if (!folder.Exists)
        {
            SqlContext.Pipe.Send($"Not exists: {folder.Name}" + Environment.NewLine);
            return;
        }

        // Create a new record with the column metadata. 
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
            new SqlMetaData("FileName", SqlDbType.NVarChar, 128),
            new SqlMetaData("FullFileName", SqlDbType.NVarChar, 1024),
            new SqlMetaData("FileSize", SqlDbType.BigInt),
            new SqlMetaData("LastWriteTime", SqlDbType.DateTime),
            new SqlMetaData("LastAccessTime", SqlDbType.DateTime)
        });

        SqlContext.Pipe.SendResultsStart(record);
        foreach (FileInfo fi in folder.EnumerateFiles())
        {
            //record.SetValues(fi.Name, fi.FullName, fi.Length, fi.LastWriteTime, fi.LastAccessTime);
            record.SetString(0, fi.Name);
            record.SetString(1, fi.FullName);
            record.SetInt64(2, fi.Length);
            record.SetDateTime(3, fi.LastWriteTime);
            record.SetDateTime(4, fi.LastAccessTime);
            SqlContext.Pipe.SendResultsRow(record);
        }
        SqlContext.Pipe.SendResultsEnd();

        // DONE
        SqlContext.Pipe.Send(@"ListFolderFile:END" + Environment.NewLine);
    }
}

CLR 資料表值函式

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    /// <summary>
    /// 資料表值函式，※注意其宣告的呼應部份要完全一致。
    /// </summary>
    [SqlFunction(DataAccess = DataAccessKind.Read, 
        FillRowMethodName = "vwfFileServerFileList_FillRow",
        TableDefinition = "[fileName] NVARCHAR(128), parentName NVARCHAR(128), fullFileName NVARCHAR(1024), fileSize BIGINT, lastWriteTime DATETIME, lastAccessTime DATETIME")]
    public static IEnumerable vwfFileServerFileList(SqlString fileServerRootPath, SqlString folderName)
    {
        // GO
        DirectoryInfo fileServerRoot = new DirectoryInfo(fileServerRootPath.ToString());
        if (!fileServerRoot.Exists)
        {
            return null;
        }

        List<FileInfo> fileInfoList = new List<FileInfo>();
        foreach (DirectoryInfo folder in fileServerRoot.EnumerateDirectories(folderName.ToString()))
        {
            fileInfoList.AddRange(folder.EnumerateFiles());
        }

        // trace
        return fileInfoList;
    }

    public static void vwfFileServerFileList_FillRow(
        object obj,
        out SqlString fileName,
        out SqlString parentName,
        out SqlString fullFileName,
        out SqlInt64 fileSize,
        out SqlDateTime lastWriteTime,
        out SqlDateTime lastAccessTime)
    {
        FileInfo fi = (FileInfo)obj;

        // to yield return
        fileName = fi.Name;
        parentName = fi.Directory.Name;
        fullFileName = fi.FullName;
        fileSize = fi.Length;
        lastWriteTime = fi.LastWriteTime;
        lastAccessTime = fi.LastAccessTime;
    }
}

SQL Server CRL 註冊指令紀錄

-- 啟動CLR
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  

-- 設定為『值得信賴』的資料庫。
ALTER DATABASE AsvtToturDB SET TRUSTWORTHY ON
GO

-- 註冊CLR.dll模組
CREATE ASSEMBLY FileView 
  AUTHORIZATION dbo
  FROM 'C:\YourSQLServerProject\bin\FileView.dll' 
  WITH PERMISSION_SET = EXTERNAL_ACCESS; 
  -- 注意：要開啟 TRUSTWORTHY 才能設定 EXTERNAL_ACCESS。
GO

-- 註冊 Procedure
CREATE PROCEDURE uspHelloWorld  
@name NVARCHAR(512)  
AS  
EXTERNAL NAME FileView.StoredProcedures.HelloWorld 
--- 注意：命名空間的名稱取用順序為[ModuleName].[NameSpace].[ClassName].[MethodName] 
GO

-- 註冊 Procedure
CREATE PROCEDURE uspListFolderFile  
@folderPath NVARCHAR(512)  
AS  
EXTERNAL NAME FileView.StoredProcedures.ListFolderFile 
--- 注意：命名空間的名稱取用順序為[ModuleName].[NameSpace].[ClassName].[MethodName] 
GO

-- 註冊 Table-Valued Function ※注意其宣告的呼應部份要完全一致。
CREATE FUNCTION vwfFileServerFileList( 
@fileServerRootPath NVARCHAR(1024),
@folderName NVARCHAR(128)
) RETURNS TABLE (  

   [fileName] NVARCHAR(128),  

   parentName NVARCHAR(128),

   fullFileName NVARCHAR(1024),

   fileSize BIGINT,

   lastWriteTime DATETIME,

   lastAccessTime DATETIME

) AS  
EXTERNAL NAME FileView.UserDefinedFunctions.vwfFileServerFileList 
--- 注意：命名空間的名稱取用順序為[ModuleName].[NameSpace].[ClassName].[MethodName] 
GO

沒圖沒真像

成功的話，可以在(SSMS) SQL Server 物件總管看到註冊好的 SQL CLR。

以此應用來說，SQL CLR 可讀取一般檔案清單，也可以讀取網路芳鄰的檔案清單。，=

SQL CLR 也可以讀取網路芳鄰的檔案清單

CLR 資料表值函式成果展示

小結

終於有了一個SQL Server CLR的應用，用來整合檔案系統資訊讀取。原本使用 FileTable 模擬檔案系統，但是 FileTable 仍是 SQL Server 的一部份固有所限制。

改用SQL CRL 開發 Table-Valued Function 讀取檔案資訊，以替代 FileTable 不足的部份。