using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Storage; using Ropin.Inspection.Model.Entities; using Ropin.Inspection.Repository.Interface; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Ropin.Inspection.Repository { public class UnitOfWork : IUnitOfWork { private InspectionDbContext _dbContext; private IDbContextTransaction _dbTransaction; public UnitOfWork(InspectionDbContext dbContext) { _dbContext = dbContext; } public void BeginTransaction() { _dbTransaction = _dbContext.Database.BeginTransaction(); } public async Task ExecuteSqlCommandAsync(string sql, DbParameter[] sqlParams ) { int result = -1; var db = _dbContext.Database; var connection = db.GetDbConnection(); var cmd = connection.CreateCommand(); if (connection.State == ConnectionState.Closed) connection.Open(); //using (var transaction = connection.BeginTransaction()) //{ using (var command = connection.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } // *** ADD THIS LINE *** //command.Transaction = transaction; // otherwise, this will throw System.InvalidOperationException: The transaction associated with this command is not the connection's active transaction. result = command.ExecuteNonQuery(); } //} connection.Close(); return await Task.FromResult(result) ; //int i = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(_dbContext.Database, sql, sqlParams); //return await Task.FromResult(i); //return await _dbContext.Database.ExecuteSqlRawAsync(sql, parameters);//.ExecuteSqlCommandAsync(sql, parameters); } public async Task RegisterNew(TEntity entity) where TEntity : class { _dbContext.Set().Add(entity); if (_dbTransaction != null) return await _dbContext.SaveChangesAsync() > 0; return true; } public async Task RegisterRangeNew(IEnumerable entities) where TEntity : class { _dbContext.Set().AddRange(entities); if (_dbTransaction != null) return await _dbContext.SaveChangesAsync()>0; return true; } public async Task RegisterDirty(TEntity entity) where TEntity : class { _dbContext.Entry(entity).State = EntityState.Modified; if (_dbTransaction != null) return await _dbContext.SaveChangesAsync() > 0; return true; } public async Task RegisterClean(TEntity entity) where TEntity : class { _dbContext.Entry(entity).State = EntityState.Unchanged; if (_dbTransaction != null) return await _dbContext.SaveChangesAsync() > 0; return true; } public async Task RegisterDeleted(TEntity entity) where TEntity : class { _dbContext.Set().Remove(entity); if (_dbTransaction != null) return await _dbContext.SaveChangesAsync() > 0; return true; } public async Task CommitAsync() { if (_dbTransaction == null) return await _dbContext.SaveChangesAsync() > 0; else _dbTransaction.Commit(); return true; } public void Rollback() { if (_dbTransaction != null) _dbTransaction.Rollback(); } } }