HYTOS

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";

                                break;

                        }

                        query += $@"

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

values

(

    lower(newid())

   ,@RefID

   ,@Category

   ,@FileType

   ,@FileName

   ,@FilePath

   ,@FileExtension

   ,@FileData

   ,@Creator

)";

                        base.Execute(query, attFile, transaction);

                    }

                    transaction.Commit();

                    isSuccess = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return isSuccess;

        }

    }

}