這不是FileTable入門級的文章，也不討論如何啟用FileTable，建議完成FileTable入門練習再來閱讀參考本文章。本篇是本人應用FileTable 的紀錄，主要用作未來參
Last updated
\\共享根目錄\表單序號\附件檔案
\\FileServerRoot\FormUUID\attachFile2
<--- 共享根目錄到附件檔案中的管理目錄只有一層表單序號\\共享根目錄\類別\年月\表單序號\附件檔案
\\FileServerRoot\FooGroup\201908\aaaaaaaa-bbbb-cccc-dddd-cccccccccccc\Foo.pdf
<--- 傳統上，一般中間層管理目錄還會有類別、年月等等。-- 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);
}