AipGateway/AipDatabase.API/Repositories/LinkedServerRepository.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 LinkedServerRepository : ILinkedServerRepository
    {
        private readonly ILogger<LinkedServerRepository> _log;
        private readonly IDatabaseFactory _databaseFactory;
        private readonly SqlHelper<LinkedServerRepository> _sqlHelper;

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

        public async Task<DataCount> FindByIp(int systemId, string serverIpAddr)
        {
            var parameters = new
            {
                systemId,
                serverIpAddr
            };

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

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

        public async Task<DataCount> Add(int systemId, string serverIpAddr, string serverDesc,
                                         string apiKey, DateTime expiredAt,
                                         bool policyLookupYn,
                                         bool fileInfoLookupYn,
                                         bool applyLabelYn,
                                         bool releaseLabelYn,
                                         bool encryptionFileYn,
                                         bool decryptionFileYn)
        {
            var parameters1 = new
            {
                systemId,
                serverIpAddr,
                serverDesc
            };

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

                transaction = (SqlTransaction)connection.BeginTransaction();

                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedServer.Add");
                int effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters1,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedServer.Add: {effectedRows} EA.");
                if (effectedRows <= 0)
                {
                    return new DataCount(0);
                }
                DataCount result = new DataCount(effectedRows);

                var parameters2 = new
                {
                    systemId,
                    serverIpAddr,
                };

                sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedServer.GetIdentify");
                DataIdentify identify = await connection.QueryFirstAsync<DataIdentify>(
                    sql,
                    parameters2,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedServer.GetIdentify: {identify.Id}.");

                var parameters3 = new
                {
                    identify.Id,
                    apiKey,
                    expiredAt,
                    policyLookupYn,
                    fileInfoLookupYn,
                    applyLabelYn,
                    releaseLabelYn,
                    encryptionFileYn,
                    decryptionFileYn
                };

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

                transaction.Commit();
                return result;
            }
            catch (Exception)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
        }

        public async Task<DataCount> Update(int serverId, string serverIpAddr, string serverDesc,
                                         int apiId, DateTime expiredAt,
                                         bool policyLookupYn,
                                         bool fileInfoLookupYn,
                                         bool applyLabelYn,
                                         bool releaseLabelYn,
                                         bool encryptionFileYn,
                                         bool decryptionFileYn)
        {
            var parameters1 = new
            {
                serverId,
                serverIpAddr,
                serverDesc
            };

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

                string sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedServer.Update");
                int effectedRows = await connection.ExecuteAsync(
                    sql,
                    parameters1,
                    commandType: CommandType.Text
                    );
                _log.LogInformation($"LinkedServer.Add: {effectedRows} EA.");
                if (effectedRows <= 0)
                {
                    return new DataCount(0);
                }
                DataCount result = new DataCount(effectedRows);

                var parameters2 = new
                {
                    apiId,
                    expiredAt,
                    policyLookupYn,
                    fileInfoLookupYn,
                    applyLabelYn,
                    releaseLabelYn,
                    encryptionFileYn,
                    decryptionFileYn
                };

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

                transaction.Commit();
                return result;
            }
            catch (Exception)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
        }

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

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

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

                DataCount result = new DataCount(effectedRows);

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

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

                transaction.Commit();
                return result;
            }
            catch (Exception)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw;
            }
        }


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

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

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

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