HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ID2.Manager.Data.Models;

using ID2.Manager.Dapper.Repository;

namespace ID2.Manager.Controller.Controllers

{

    public class AttFileController : BaseController

    {

        public IEnumerable<AttFileInfo> GetFileList(string refID, string category)

        {

            try

            {

                using (AttFileRepository rep = new AttFileRepository(this._MSSQLCONNSTR))

                {

                    return rep.GetAttFileList(refID, category);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public AttFileInfo GetAttFileInfo(string refID, string category)

        {

            try

            {

                using (AttFileRepository rep = new AttFileRepository(this._MSSQLCONNSTR))

                {

                    return rep.GetAttFileInfo(refID, category);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public bool SetAttFiles(List<AttFileInfo> attFiles, string userId)

        {

            try

            {

                using (AttFileRepository rep = new AttFileRepository(this._MSSQLCONNSTR))

                {

                    return rep.SetAttFiles(attFiles, userId);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ID2.Manager.Data.Models;

namespace ID2.Manager.Dapper.Repository

{

    public class AttFileRepository : BaseRepository

    {

        public AttFileRepository(string connectionStr) : base(connectionStr) { }

        public IEnumerable<AttFileInfo> GetAttFileList(string refID, string category)

        {

            try

            {

                string query = $@"

select * from dbo.AttachFIles where RefID=@RefID and Category=@Category order by CreatedDate";

                return Query<AttFileInfo>(query, new { RefID = refID, Category = category });

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public AttFileInfo GetAttFileInfo(string refID, string category)

        {

            try

            {

                string query = $@"

select top 1 * from dbo.AttachFIles where RefID=@RefID and Category=@Category order by CreatedDate";

                return QueryFirstOrDefault<AttFileInfo>(query, new { RefID = refID, Category = category });

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public bool SetAttFiles(List<AttFileInfo> attFiles, string userId)

        {

            bool isSuccess = false;

            try

            {

                string query = string.Empty;

                using (var transaction = base.BeginTransaction())

                {

                    foreach (AttFileInfo attFile in attFiles)

                    {

                        attFile.Creator = userId;

                        switch (attFile.Category)

                        {

                            case "notice":

                                query = $@"

delete dbo.AttachFIles where RefID=@RefID and Category=@Category

insert into dbo.AttachFIles (FileID,RefID,Category,FileType,FileName,FilePath,FileData,Creator)

values

(

lower(newid())

,@RefID

,@Category

,@FileType

,@FileName

,@FilePath

,@FileData

,@Creator

)";

                                break;

                            default:

                                query = $@"

insert into dbo.AttachFIles (FileID,RefID,Category,FileType,FileName,FilePath,FileData,Creator)

values

(

lower(newid())

,@RefID

,@Category

,@FileType

,@FileName

,@FilePath

,@FileData

,@Creator

)";

                                break;

                        }

                        base.Execute(query, attFile, transaction);

                    }

                    transaction.Commit();

                    isSuccess = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return isSuccess;

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Runtime.Serialization;

namespace ID2.Manager.Data.Models

{

    [DataContract]

    public class AttFileInfo

    {

        [DataMember]

        public string FileID { get; set; }

        [DataMember]

        public string RefID { get; set; }

        [DataMember]

        public string Category { get; set; }

        [DataMember]

        public string FileType { get; set; }

        [DataMember]

        public string FileName { get; set; }

        [DataMember]

        public string FilePath { get; set; }

        [DataMember]

        public byte[] FileData { get; set; }

        [DataMember]

        public DateTime CreatedDate { get; set; }

        [DataMember]

        public string Creator { get; set; }

    }

}