AipGateway/AipDatabase.API/Repositories/LinkedSystemRepository.cs

using AipDatabase.API.Infrastructures;
using AipDatabase.API.Interfaces;
using AipDatabase.API.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;

namespace AipDatabase.API.Repositories
{
    public class LinkedSystemRepository : ILinkedSystemRepository
    {
        private readonly ILogger<LinkedSystemRepository> _log;
        private readonly IDatabaseFactory _databaseFactory;
        private readonly SqlHelper<LinkedSystemRepository> _sqlHelper;

        public LinkedSystemRepository(ILogger<LinkedSystemRepository> log, IDatabaseFactory databaseFactory)
        {
            _log = log;
            _databaseFactory = databaseFactory;
            _sqlHelper = new SqlHelper<LinkedSystemRepository>();
        }

        public async Task<DataCount> FindByName(string systemName)
        {
            var parameters = new
            {
                systemName,
            };

            try
            {
                using var connection = _databaseFactory.GetDbConnection();
    
                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedSystem.FindByName");
                DataCount result = await connection.QueryFirstAsync<DataCount>(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<LinkedSystem> FindById(int systemId)
        {
            var parameters = new
            {
                systemId,
            };

            try
            {
                using var connection = _databaseFactory.GetDbConnection();

                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedSystem.FindById");
                LinkedSystem result = await connection.QueryFirstAsync<LinkedSystem>(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<DataCount> Add(string systemName, string systemDesc)
        {
            var parameters = new
            {
                systemName,
                systemDesc
            };

            try
            {
                using var connection = _databaseFactory.GetDbConnection();

                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedSystem.Add");
                int effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedSystem.Add: {effectedRows} EA.");
                DataCount result = new DataCount(effectedRows);
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<DataCount> Delete(int systemId)
        {
            var parameters = new
            {
                systemId
            };

            SqlTransaction? transaction = null;
            try
            {
                using var connection = _databaseFactory.GetDbConnection();
                transaction = (SqlTransaction)connection.BeginTransaction();

                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedSystem.Delete");
                int effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedSystem.Delete: {effectedRows} EA.");

                DataCount result = new DataCount(effectedRows);

                sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedServer.Delete");
                effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedServer.Delete: {effectedRows} EA.");

                sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedApiKey.Delete");
                effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedApiKey.Delete: {effectedRows} EA.");

                sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedDecryptKey.Delete");
                effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedDecryptKey.Delete: {effectedRows} EA.");

                transaction.Commit();

                return result;
            }
            catch (Exception)
            {
                if (transaction != null )
                {
                    transaction.Rollback();
                }
                throw;
            }
        }
        
        public async Task<DataCount> Update(int systemId, string systemName, string systemDesc)
        {
            var parameters = new
            {
                systemId,
                systemName,
                systemDesc
            };

            try
            {
                using var connection = _databaseFactory.GetDbConnection();

                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedSystem.Update");
                int effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedSystem.Update: {effectedRows} EA.");

                DataCount result = new DataCount(effectedRows);

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Pagination<LinkedSystem>> GetLists(int pagePerCount, int pageSize, int page)
        {
            try
            {
                using var connection = _databaseFactory.GetDbConnection();

                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedSystem.GetListsTotal");
                Int64 totalCount = await connection.QueryFirstAsync<Int64>(
                    sql,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedSystem.GetListsTotal: {totalCount} EA.");

                Pagination<LinkedSystem> result = new Pagination<LinkedSystem>(page, pageSize, pagePerCount, (int)totalCount);
                var parameters = new
                {
                    startRow = result.startRow,
                    endRow = result.endRow
                };
                sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedSystem.GetLists");
                IEnumerable<LinkedSystem> Results = await connection.QueryAsync<LinkedSystem>(
                                    sql,
                                    parameters,
                                    commandType: CommandType.Text
                                    );

                result.lists = Results.ToList();
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}