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 _log; private readonly IDatabaseFactory _databaseFactory; private readonly SqlHelper _sqlHelper; public LinkedServerRepository(ILogger log, IDatabaseFactory databaseFactory) { _log = log; _databaseFactory = databaseFactory; _sqlHelper = new SqlHelper(); } public async Task 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( sql, parameters, commandType: CommandType.Text ); return result; } catch (Exception) { throw; } } public async Task 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( 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 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 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> 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( sql, parameters1, commandType: CommandType.Text ); _log.LogInformation($"LinkedServer.GetListsTotal: {totalCount} EA."); Pagination result = new Pagination(page, pageSize, pagePerCount, (int)totalCount); var parameters2 = new { systemId, startRow = result.startRow, endRow = result.endRow }; sql = _sqlHelper.GetSqlFromEmbeddedResource("LinkedServer.GetLists"); IEnumerable Results = await connection.QueryAsync( sql, parameters2, commandType: CommandType.Text ); result.lists = Results.ToList(); return result; } catch (Exception) { throw; } } } }