SQL Server 2017 CLR 使用紀錄
為工作紀錄以未來參考。應用於附件上傳以CLR取檔案系統實體清單。因FileTable有一些權限上限制使得應用整合上出現困難，固有了以SQL 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 Server CLR的應用，用來整合檔案系統資訊讀取。原本使用 FileTable 模擬檔案系統，但是 FileTable 仍是 SQL Server 的一部份固有所限制。
改用SQL CRL 開發 Table-Valued Function 讀取檔案資訊，以替代 FileTable 不足的部份。
Last updated