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