HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Dapper;

using ID2.Manager.Data.Models;

namespace ID2.Manager.Dapper.Repository

{

    public class ExcelDataRepository : BaseRepository

    {

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

        public IEnumerable<ExcelData> GetExcelData(string FileName,string WorksheetName)

        {

            try

            {

                string query = $@"select * from dbo.ImportExcel where [FILE_NAME] = @FileName and WORKSHEET_NAME = @WorksheetName order by ROW_INDEX, COUMMN_INDEX";

                var dynamicParameters = new DynamicParameters();

                dynamicParameters.Add("@FileName", FileName);

                dynamicParameters.Add("@WorksheetName", WorksheetName);

                return Query<ExcelData>(query, dynamicParameters);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public IEnumerable<ExcelDataInfo> GetExcelDataInfo()

        {

            try

            {

                string query = $@"select [FILE_NAME],WORKSHEET_NAME from dbo.ImportExcel group by [FILE_NAME],WORKSHEET_NAME";

                return Query<ExcelDataInfo>(query);

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public bool DeleteExcelData(string FileName, string WorksheetName)

        {

            bool isSuccess = false;

            try

            {

                using (var transaction = base.BeginTransaction())

                {

                    string query = $@"delete from dbo.ImportExcel where [FILE_NAME] = @FileName and WORKSHEET_NAME = @WorksheetName";

                    var dynamicParameters = new DynamicParameters();

                    dynamicParameters.Add("@FileName", FileName);

                    dynamicParameters.Add("@WorksheetName", WorksheetName);

                    Execute(query, dynamicParameters, transaction);

                    transaction.Commit();

                    isSuccess = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return isSuccess;

        }

    }

}