DI_Backup/DI/Agent/Services/FileTableService.cs

using System.Data.SqlClient;
using System.Linq;
using Dapper;
using System.IO;
using System;
using log4net;
using Agent.Models;
using System.Configuration;

namespace Agent.Services
{
    class FileTableService
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(FileTableService));

        /// <summary>
        /// 디렉토리 조회
        /// </summary>
        public string GetDirectory(SqlConnection con, string name)
        {
            try
            {
                var sql = $@"
                    SELECT TOP 1 path_locator.ToString()
                    FROM DI_FILE.[dbo].[di_backup_file]
                    WHERE 1=1
                    AND name = @DirectoryName
                    AND is_directory = 1
                ";

                return con.Query<string>(sql, new { DirectoryName = name }).SingleOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 디렉토리 생성
        /// </summary>
        public string CreateDirectory(SqlConnection con, SqlTransaction transaction, string name, string parentId)
        {
            try
            {
                var sql = @"
                    DECLARE @parent_node hierarchyid, @node hierarchyid
                    SET @parent_node = CAST(@ParentId AS hierarchyid)
                    SELECT @node = MAX(path_locator) FROM DI_FILE.[dbo].[di_backup_file] WHERE path_locator.GetAncestor(1) = @parent_node

                    INSERT INTO DI_FILE.[dbo].[di_backup_file] ([name], is_directory, path_locator)
                    OUTPUT
                        INSERTED.path_locator.ToString()
                    VALUES (@Name, 1, @parent_node.GetDescendant(@node, NULL))
                ";

                return con.Query<string>(sql, new { @Name = name, @ParentId = parentId }, transaction).SingleOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
        }
        public string CreateDirectory(string name, string parentId)
        {
            try
            {
                var sql = @"
                    DECLARE @parent_node hierarchyid, @node hierarchyid
                    SET @parent_node = CAST(@ParentId AS hierarchyid)
                    SELECT @node = MAX(path_locator) FROM DI_FILE.[dbo].[di_backup_file] WHERE path_locator.GetAncestor(1) = @parent_node

                    INSERT INTO DI_FILE.[dbo].[di_backup_file] ([name], is_directory, path_locator)
                    OUTPUT
                        INSERTED.path_locator.ToString()
                    VALUES (@Name, 1, @parent_node.GetDescendant(@node, NULL))
                ";

                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    return con.Query<string>(sql, new { @Name = name, @ParentId = parentId }).SingleOrDefault();
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 디렉토리 생성(시간으로 이름지정)
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public string CreateDirectory(SqlConnection con, SqlTransaction transaction, string parentId)
        {
            try
            {
                var sql = @"
                    DECLARE @name nvarchar(100), @parent_node hierarchyid, @node hierarchyid
                    SET @parent_node = CAST(@ParentId AS hierarchyid)
                    SELECT @node = MAX(path_locator) FROM DI_FILE.[dbo].[di_backup_file] WHERE path_locator.GetAncestor(1) = @parent_node
                    SELECT @name = Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')

                    INSERT INTO DI_FILE.[dbo].[di_backup_file] ([name], is_directory, path_locator)
                    OUTPUT
                        INSERTED.path_locator.ToString()
                    VALUES (@name, 1, @parent_node.GetDescendant(@node, NULL))
                ";

                return con.Query<string>(sql, new { @ParentId = parentId }, transaction).SingleOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
        }
        public string CreateDirectory(string parentId)
        {
            try
            {
                var sql = @"
                    DECLARE @name nvarchar(100), @parent_node hierarchyid, @node hierarchyid
                    SET @parent_node = CAST(@ParentId AS hierarchyid)
                    SELECT @node = MAX(path_locator) FROM DI_FILE.[dbo].[di_backup_file] WHERE path_locator.GetAncestor(1) = @parent_node
                    SELECT @name = Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')

                    INSERT INTO DI_FILE.[dbo].[di_backup_file] ([name], is_directory, path_locator)
                    OUTPUT
                        INSERTED.path_locator.ToString()
                    VALUES (@name, 1, @parent_node.GetDescendant(@node, NULL))
                ";

                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    return con.Query<string>(sql, new { @ParentId = parentId }).SingleOrDefault();
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 디렉토리 생성(루트)
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public string CreateDirectoryToRoot(SqlConnection con, SqlTransaction transaction, string name)
        {
            try
            {
                var sql = @"
                    DECLARE @parent_node hierarchyid, @node hierarchyid
                    SET @parent_node = hierarchyid::GetRoot()
                    SELECT @node = MAX(path_locator) FROM DI_FILE.[dbo].[di_backup_file] WHERE path_locator.GetAncestor(1) = @parent_node

                    INSERT INTO DI_FILE.[dbo].[di_backup_file] ([name], is_directory, path_locator)
                    OUTPUT
                        INSERTED.path_locator.ToString()
                    VALUES (@Name, 1, @parent_node.GetDescendant(@node, NULL))
                ";

                return con.Query<string>(sql, new { @Name = name }, transaction).SingleOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 파일 업로드
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="srcPath"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public Guid UploadFile(SqlConnection con, SqlTransaction transaction, string srcPath, string parentId)
        {
            try
            {
                var sql = @"
                    DECLARE @parent_node hierarchyid, @node hierarchyid
                    SET @parent_node = CAST(@ParentId AS hierarchyid)
                    SELECT @node = MAX(path_locator) FROM DI_FILE.[dbo].[di_backup_file] WHERE path_locator.GetAncestor(1) = @parent_node

                    INSERT INTO DI_FILE.[dbo].[di_backup_file] ([name], file_stream, path_locator)
                    OUTPUT 
                        INSERTED.file_stream.PathName() AS Path,
                        INSERTED.stream_id AS StreamId
                    VALUES (@Name, @Binary, @parent_node.GetDescendant(@node, NULL))
                ";

                var fileBinary = File.ReadAllBytes(srcPath);

                var insertedFile = con.Query<FileTable>(sql, new { @Name = Path.GetFileName(srcPath), Binary = fileBinary, @ParentId = parentId }, transaction).SingleOrDefault();

                return insertedFile.StreamId;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 파일 다운로드
        /// </summary>
        /// <param name="con"></param>
        /// <param name="transaction"></param>
        /// <param name="dstPath"></param>
        /// <param name="streamId"></param>
        /// <returns></returns>
        public string DownloadFile(SqlConnection con, SqlTransaction transaction, string dstPath, Guid streamId)
        {
            try
            {
                if (!Directory.Exists(Path.GetDirectoryName(dstPath)))
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(dstPath));
                }

                var sql = @"
                    SELECT TOP 1
                        file_stream AS binary,
                        file_stream.PathName() AS Path,
                        name
                    FROM DI_FILE.[dbo].[di_backup_file]
                    WHERE stream_id = @StreamId
                ";

                var file = con.Query<FileTable>(sql, new { @StreamId = streamId }, transaction).SingleOrDefault();

                var tmpDstPath = $@"{Path.GetDirectoryName(dstPath)}\tmp_{Path.GetFileName(dstPath)}";

                File.WriteAllBytes(tmpDstPath, file.Binary);

                return tmpDstPath;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 디렉터리 삭제 (자식노드 포함)
        /// </summary>
        /// <param name="name"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public bool RemoveDirectory(string parentId)
        {
            try
            {
                var sql = @"
                    DECLARE @parent_node hierarchyid
                    SET @parent_node = CAST(@ParentId AS hierarchyid)
                    DELETE FROM DI_FILE.[dbo].[di_backup_file] WHERE path_locator.IsDescendantOf(@parent_node) = 1
                ";

                using (var con = new SqlConnection(App._myConnection))
                {
                    con.Open();

                    return 0 < con.Execute(sql, new { ParentId = parentId });
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}