qlhb/Ropin.Inspection.Repository/Base/UnitOfWork.cs

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<int> 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<bool> RegisterNew<TEntity>(TEntity entity)
            where TEntity : class
        {
            _dbContext.Set<TEntity>().Add(entity);
            if (_dbTransaction != null)
                return await _dbContext.SaveChangesAsync() > 0;
            return true;
        }

        public async Task<bool> RegisterRangeNew<TEntity>(IEnumerable<TEntity> entities)
            where TEntity : class
        {
             _dbContext.Set<TEntity>().AddRange(entities);
            if (_dbTransaction != null)
                return await _dbContext.SaveChangesAsync()>0;
            return true;
        }

        public async Task<bool> RegisterDirty<TEntity>(TEntity entity)
            where TEntity : class
        {
            _dbContext.Entry<TEntity>(entity).State = EntityState.Modified;
            if (_dbTransaction != null)
                return await _dbContext.SaveChangesAsync() > 0;
            return true;
        }

        public async Task<bool> RegisterClean<TEntity>(TEntity entity)
            where TEntity : class
        {
            _dbContext.Entry<TEntity>(entity).State = EntityState.Unchanged;
            if (_dbTransaction != null)
                return await _dbContext.SaveChangesAsync() > 0;
            return true;
        }

        public async Task<bool> RegisterDeleted<TEntity>(TEntity entity)
            where TEntity : class
        {
            _dbContext.Set<TEntity>().Remove(entity);
            if (_dbTransaction != null)
                return await _dbContext.SaveChangesAsync() > 0;
            return true;
        }

        public async Task<bool> CommitAsync()
        {
            if (_dbTransaction == null)
                return await _dbContext.SaveChangesAsync() > 0;
            else
                _dbTransaction.Commit();
            return true;
        }

        public void Rollback()
        {
            if (_dbTransaction != null)
                _dbTransaction.Rollback();
        }
    }
}