Repository by File

DadosRepository.cs

 public class DadosRepository : RepositoryBase<Dados>, IDadosRepository
    {
        private readonly RepositoryConfigurationVO _configdbconn;

        public PlanilhaDadosRepository(RepositoryConfigurationVO configdbconn) : base(configdbconn.ConnectionString)
        {
            _configdbconn = configdbconn;
        }

        public async Task<IEnumerable<Dados>> GetAllEnvioNull()
        {
            var query = await GetType().GetContentAsync(SqlFileConstants.GET_ALL_ENVIO_NULL);

            return await QueryAsync<Dados>(query);
        }


        protected override string GetGetAllSQLContent()
        {
            throw new System.NotImplementedException();
        }

        protected override string GetDeleteSQLContent()
        {
            throw new System.NotImplementedException();
        }

        protected override string GetGetByIdSQLContent()
        {
            throw new System.NotImplementedException();
        }

        protected override string GetInsertSQLContent()
        {
            return SqlFileConstants.INSERT;
        }

        protected override string GetUpdateSQLContent()
        {
            return SqlFileConstants.UPDATE;
        }
    }

FileExtensions.cs

public static class FileExtensions
    {
        public static async Task<string> GetContentAsync(this Type type, string path)
        {
            var fileContent = type.Assembly.GetManifestResourceStream($"{type.Assembly.GetName().Name}.{path}");
            if (fileContent == null) return null;
            
            using var reader = new StreamReader(fileContent);
            return await reader.ReadToEndAsync();
        }
    }

IRepositoryBase.cs

public interface IRepositoryBase<T> : IDisposable, IUnitOfWork
    {
        public Task<int> Execute(string query, DynamicParameters parameters = null);
        public Task<T> QuerySingleAsync(string query, DynamicParameters parameters = null);
        public Task<IEnumerable<T>> QueryAsync(string query, DynamicParameters parameters = null);
        public Task CallStoredProcedureAsync(string name, DynamicParameters parameters = null);
        Task<int> InsertAsync(T entity);
        Task<int> UpdateAsync(T entity);
        Task<int> DeleteAsync(long id);
        Task<T> GetByIdAsync(long id);
        Task<IEnumerable<T>> GetAllAsync();

    }

IUnitOfWork.cs

    public interface IUnitOfWork
    {
        public Task OpenConnectionAsync();
        public Task DisposeConnectionAsync();
        public void CommitTransaction();
        public Task BeginTransaction();
        public void DisposeTransaction();
        public void RoolbackTransaction(string transactionName);
        void OpenConnectionWithBeginTransaction(string transactionName);
    }

RepositoryBase.cs

  public abstract class RepositoryBase<T> : IRepositoryBase<T> where T : class
    {
        protected IDbConnection _sqlConn;

        private IDbTransaction _sqlTrans;


        protected RepositoryBase(IDbConnection sqlConn)
        {
            _sqlConn = sqlConn;
        }

        public virtual void OpenConnection(IDbConnection sqlConn = null)
        {
           if(sqlConn != null) _sqlConn = sqlConn;
            if (_sqlConn.State == ConnectionState.Closed)
            {
                await _sqlConn.Open();
            }

        }

        public virtual void DisposeConnectionAsync()
        {
            await _sqlConn.Dispose();
        }

        public void Dispose()
        {
            if (_sqlConn.State == ConnectionState.Open)
                _sqlConn.Close();

            _sqlConn?.Dispose();
            _sqlTrans?.Dispose();
        }

        public virtual async Task<T> QuerySingleAsync(string query, DynamicParameters parameters = null)
        {
            OpenConnection();
            return await _sqlConn.QuerySingleAsync<T>(query, parameters);
        }

        public virtual async Task<TKey> QuerySingleAsync<TKey>(string query, DynamicParameters parameters = null)
        {
            OpenConnection();
            return await _sqlConn.QuerySingleAsync<TKey>(query, parameters);
        }

        public virtual async Task<IEnumerable<T>> QueryAsync(string query, DynamicParameters parameters = null)
        {
            OpenConnection();
            return await _sqlConn.QueryAsync<T>(query, parameters, _sqlTrans);
        }

        public virtual async Task<IEnumerable<TEntity>> QueryAsync<TEntity>(string query, DynamicParameters parameters = null)
        {
           OpenConnection();
            return await _sqlConn.QueryAsync<TEntity>(query, parameters, _sqlTrans);
        }

        public virtual async Task CallStoredProcedureAsync(string name, DynamicParameters parameters = null)
        {
           OpenConnection();
            await _sqlConn.ExecuteAsync(name, parameters, commandType: CommandType.StoredProcedure);
        }


        public virtual async Task<int> Execute(string query, DynamicParameters parameters = null)
        {
            OpenConnection();
            return await _sqlConn.ExecuteAsync(query, parameters, _sqlTrans);
        }

      
        public async Task<int> ExecuteQuery(string query, DynamicParameters parameters = null)
        {
            OpenConnection();
            return await _sqlConn.QuerySingleAsync<int>(query, parameters);
        }

        protected virtual async Task<int> Transaction(T entity, string arquivo)
        {
            var query = await GetType().GetContentAsync(arquivo);
            var param = new DynamicParameters(entity);
            var result = await Execute(query, param);

            return result;
        }


        protected virtual async Task Transaction<TEnity>(TEnity entity, string arquivo)
        {
            var query = await GetType().GetContentAsync(arquivo);
            var param = new DynamicParameters(entity);
            var result = await Execute(query, param);
        }

        protected virtual async Task Transaction(IEnumerable<T> entities, string arquivo)
        {

            var query = await GetType().GetContentAsync(arquivo);
            var param = new DynamicParameters(entities);

            await Execute(query, param);

        }

        public virtual async Task<int> InsertAsync(T entity)
        {
            return await Transaction(entity, GetInsertSQLContent());
        }

        public virtual async Task<int> UpdateAsync(T entity)
        {
            return await  Transaction(entity, GetUpdateSQLContent());
        }


        public virtual async Task<int> DeleteAsync(long id)
        {
            var query = await GetType().GetContentAsync(GetDeleteSQLContent());
            var param = new DynamicParameters(new { Id = id });

            return await Execute(query, param);
        }

        public virtual async Task<T> GetByIdAsync(long id)
        {
            var query = await GetType().GetContentAsync(GetGetByIdSQLContent());
            var param = new DynamicParameters(new { Id = id });

            var result = (await QueryAsync(query, param)).FirstOrDefault();
            return result;
        }


        public virtual async Task<IEnumerable<T>> GetAllAsync()
        {   
            var query = await GetType().GetContentAsync(GetGetAllSQLContent());
            var result = await QueryAsync(query);

            return result;
        }

        /// <summary>
        /// Implementacao deve ser carregar o arquivo SQL especifico que seleciona todos
        /// por exemplo:  await GetType().GetContentAsync(<SQLFileName>)
        /// </summary>
        /// <returns></returns>
        protected abstract string GetGetAllSQLContent();

        /// <summary>
        /// Implementacao deve ser carregar o arquivo SQL especifico que executa a delecao
        /// </summary>
        /// <returns></returns>
        protected abstract string GetDeleteSQLContent();

        /// <summary>
        /// Implementacao deve ser carregar o arquivo SQL especifico que seleciona por id
        /// </summary>
        /// <returns></returns>
        protected abstract string GetGetByIdSQLContent();

        /// <summary>
        /// Implementacao deve ser carregar o arquivo SQL especifico que insere
        /// </summary>
        /// <returns></returns>
        protected abstract string GetInsertSQLContent();

        /// <summary>
        /// Implementacao deve ser carregar o arquivo SQL especifico que faz o update
        /// </summary>
        /// <returns></returns>
        protected abstract string GetUpdateSQLContent();

        public void SetConnectionAndTransaction(IDbConnection dbConnection, IDbTransaction transaction)
        {
            if (_sqlConn.State == ConnectionState.Open)
                _sqlConn.Close();

            _sqlConn = dbConnection;
            _sqlTrans = transaction;
        }
    }

SqlFileConstants.cs

public class SqlFileConstants
    {
        public const string GET_ALL_BY_QTD = "Scripts.GetAllByQtd.sql";
        public const string UPDATE = "Scripts.Update.sql";
    }

UnitOfWork.cs

   public class UnitOfWork : IUnitOfWork
    {
        private IDbConnection _dbConnection;
        private IDbTransaction _sqlTrans;


        public UnitOfWork()
        {
        }

        public void BeginTransaction(string transactionName, params ICrossTransactionalRepository[] repositorios)
        {
            if (_dbConnection.State == ConnectionState.Closed)
            {
                _dbConnection = new SqlConnection(_config.ConnectionString);
                _dbConnection.Open();
            }

            _sqlTrans = _dbConnection.BeginTransaction(transactionName);
            SetConnectionTransaction(repositorios);
        }

        public void CommitTransaction()
        {
            if (_sqlTrans != null)
                _sqlTrans.Commit();
        }

        public void RollbackTransaction(string transactionName)
        {
            if (_sqlTrans != null)
                _sqlTrans.Rollback(transactionName);
        }

        private void SetConnectionTransaction(params ICrossTransactionalRepository[] repositorios)
        {
            foreach (var rep in repositorios)
            {
                rep.SetConnectionAndTransaction(_dbConnection, _sqlTrans);
            }
        }
    }