SQL Server: FileTable應用紀錄
這不是FileTable入門級的文章，也不討論如何啟用FileTable，建議完成FileTable入門練習再來閱讀參考本文章。本篇是本人應用FileTable 的紀錄，主要用作未來參
參考
一些觀念與應用說明
在應用上，FileTable 可以省下表單的附件檔案上傳管理的一些工作，它把一些常規(routine)的工作做完了。主要省下附件檔案與該筆資料表單的關聯與同步。不過FileTable 仍只能算是個半成品，實際應用需依需求做一些些加工。
特性：
FileTable為SQL Server的一個特別的資料表，以FileStream 結構體存放實體檔案。
FileTable為資料庫內容的一部份，所以可以直接下SQL指令操作FileStream。
FileTable也支援網路共用資料夾，可以由檔案總管直接操作檔案新增、刪除、複蓋等等，並即時與DB狀態同步。
開發應用注意事項：
一、FileTable 為半成品，需為應用需求再加工才會合用。 二、讀寫檔案時，比較建議直接像檔案總管般操作檔案；也就是可以用C#的"System.IO"的檔案操作指令。三、做統計分析(data science)或備份清掃(data purge)等批量工作時，建議用SQL指令處理。
應用紀錄-需求與規劃
需求：一筆表單有多個附件檔案。
規劃：以表單序號為附件分群。
\\共享根目錄\表單序號\附件檔案
\\FileServerRoot\FormUUID\attachFile2
<--- 共享根目錄到附件檔案中的管理目錄只有一層表單序號\\共享根目錄\類別\年月\表單序號\附件檔案
\\FileServerRoot\FooGroup\201908\aaaaaaaa-bbbb-cccc-dddd-cccccccccccc\Foo.pdf
<--- 傳統上，一般中間層管理目錄還會有類別、年月等等。於共享根目錄到附件檔案中的管理目錄只有一層表單序號。 一般來說至少還會加入YYYYMM年月目錄或其它分群分類的中間目錄，然而這些都可事後用SQL交叉查詢取得故不在此加入，只用表單序號做分群就好，因為一份表單可有多個附件檔。 每筆 FileStream 都會有個GUID格式的stream_id，要好好善用它就可以省下一些常規(routine)工作。
與傳統實作比較
需求面，一筆表單有多個附件檔案。
傳統實作上，附件會放在檔案系統（或共享資料夾、FTP），中間必需有一個附件資訊檔為它們關聯起來，這只能全手動同步。
採用 FileTable 實作的話，中間仍需有一個附件資訊檔為它們關聯起來，而這部份是自動同步的，我們要作的只是把它們的關聯拉起來。
應用紀錄-FileTable Biz Viewer - FileTalbe 加工區段
實作FileTable的檢視(View) 強烈建議把FileTable的View做完滿，這個View要可以反應商業層邏輯並可方便且易與表單關聯勾稽。 建議也實作相關的資料表值函式(Table-Valued User-Defined Functions)來加速查詢表單附件。
注意：FileTalbe 加工區段就在此處，我們不直接使用FileTable。透過View以商業邏輯包裝FileTable，讓表單與附件關聯起來。
-- FileTable View 例子，取得所有表單的附件資訊
CREATE VIEW [dbo].[vwArticleAttachment] AS
SELECT streamId = M.stream_id
,attachName = M.[name]
,artGuid = P.[name]
,directoryPath = CAST(P.file_stream.GetFileNamespacePath(1) AS NVARCHAR(1024))
,[fileStream] = M.file_stream
,fileType = M.file_type
,fileFullPath = CAST(M.file_stream.GetFileNamespacePath(1) AS NVARCHAR(1024))
,fileSize = M.cached_file_size
,createTime = CAST(M.creation_time AS DATETIME2)
,lastWriteTime = CAST(M.last_write_time AS DATETIME2)
,lastAccessTime = CAST(M.last_access_time AS DATETIME2)
FROM ArticleAttachment M WITH(NOLOCK)
LEFT JOIN ArticleAttachment P WITH(NOLOCK) ON M.parent_path_locator = P.path_locator
WHERE M.is_archive = 1
-- 其中M為目錄P為附件檔，將會過濾目錄留下附件檔案清單與其所在檔案目錄路徑。-- FileTable 資料表值函式 例子，加速取得單一表單的附件資訊
CREATE FUNCTION [dbo].[vwfArticleAttachment]
(
@artGuid NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN
(
SELECT streamId = M.stream_id
,attachName = M.[name]
,artGuid = P.[name]
,directoryPath = CAST(P.file_stream.GetFileNamespacePath(1) AS NVARCHAR(1024))
,[fileStream] = M.file_stream
,fileType = M.file_type
,fileFullPath = CAST(M.file_stream.GetFileNamespacePath(1) AS NVARCHAR(1024))
,fileSize = M.cached_file_size
,createTime = CAST(M.creation_time AS DATETIME2)
,lastWriteTime = CAST(M.last_write_time AS DATETIME2)
,lastAccessTime = CAST(M.last_access_time AS DATETIME2)
FROM ArticleAttachment M WITH(NOLOCK)
LEFT JOIN ArticleAttachment P WITH(NOLOCK) ON M.parent_path_locator = P.path_locator
WHERE M.is_archive = 1
AND P.[name] = @artGuid
)應用紀錄-表單附件讀寫
讀寫存取附件檔案建議用一般檔案操作指令，就像使用網路芳鄰、共享目錄一樣。 但也不是完全自由的亂用，要符合規劃好的商業邏輯讓附件與表單正常地相呼應。
/// <summary>
/// 上傳多個附件（使用FormData封包）。
/// </summary>
[HttpPost("[action]")]
public IActionResult UploadFileList()
{
//# parse FormData
Guid artGuid = Guid.Parse(Request.Form["artGuid"].ToString());
var fileList = Request.Form.Files;
//# 建立目標目錄
DirectoryInfo targetDir = new DirectoryInfo(Path.Combine(
_config.GetValue<String>("AttachmentRootPath"),
artGuid.ToString()));
if (!targetDir.Exists) targetDir.Create();
//# 複製上傳檔案到目的地
foreach (var fi in fileList)
{
// the terget file
FileInfo target = new FileInfo(Path.Combine(
targetDir.FullName,
fi.FileName));
using (var targetStream = target.OpenWrite())
{
fi.CopyTo(targetStream);
}
}
return Ok(LastErrMsg.SUCCESS);
}/// <summary>
/// 移除附件
/// </summary>
[HttpPost("[action]")]
public IActionResult RemoveFile(RemoveFileArgs args)
{
//# parse
Guid artGuid = Guid.Parse(args.artGuid);
//## 移除目標附件(檔案)清單
// 取目標目錄
DirectoryInfo targetDir = new DirectoryInfo(Path.Combine(
_config.GetValue<String>("AttachmentRootPath"),
artGuid.ToString()));
// the terget file
FileInfo target = new FileInfo(Path.Combine(
targetDir.FullName,
args.attachName));
// remove the target file
if (target.Exists)
target.Delete();
return Ok(LastErrMsg.SUCCESS);
}實作原理總結
建立 View 把 FileTable (附件)與 DataTable (表單)關聯起來。
表單資料欄位的讀寫用SQL指令。
附件檔案的單筆維護用File.IO指令，FileTable會立即同步到DB狀態。
分析統計附件時可以用SQL指令。
EOF
Last updated