How to dispose/rollback a BeginTransaction() with several SaveChanges()

Question

I have a 3 layer structure in my solution, where the two lower ones are the logic layer and the database layer. The database layer consists of all CRUD methods for each entity in my database. And in the logic layer we have CRUD services for each entity database CRUD method (EntityService.Add/Get/Update/Delete -> EntityRepository.Add/Get/Update/Delete).

So here I am trying to do a big save method in services that saves several entities. The method calls several Add methods in the logic layer for each entity, which in their turn calls the database Add method. I might add that all database CRUD methods ends with SaveChanges(). Of course I want the save method to be Rollbacked if anything goes wrong so our database don't get unconnected rows, so I have added BeginTransaction() to the save method.

Maybe worth mentioning is that after every CRUD we check so that nothing has gone wrong, and if it has we do a return with the returncode and whatnot. These returns happens before transaction.Commit()

The save method is structured like this: (Logic Layer)

public void SaveMethod(...)
{
 using (var context = _unitOfWork.EntityRepository.GetContext()) //Gets the dbContext
        {
            using (var transaction = context.Database.BeginTransaction())
            {
                try
                {
                    Service.Entity1.AddEntity1();
                    
                    Service.Entity2.AddEntity2();

                    //And so on...

                    transaction.Commit();
                 }
                 catch{
                    //Exception handling...
                 }
              }
           }
        }

The method that gets the context to the Save method transaction: (database layer)

     public AniPlanContext GetContext()
    {
        var dbBuilder = new DbContextOptionsBuilder();
        var dbConn = _configuration.GetConnectionString("dbContextConnectionstring"); //Gets the db connection string
        var options = dbBuilder.UseSqlServer(dbConn).Options;

        return new AniPlanContext(options);
    }

How an AddEntity method looks like: (Logic layer)

public Entity AddClinic(...)
    {
        try
        {
            //Validation....

           Entity entity = _unitOfWork.EntityRepository.Add(
                new Entity
            {
                //Set the attributes...
            });

            return entity;
        }
        catch (Exception ex)
        {
            //Exception
        }
    }

How a database Add looks like: (Database layer)

public TEntity Add(TEntity entity)
    {
        if (entity == null)
        {
            throw new ArgumentNullException($"{nameof(Add)} entity must not be null");
        }

        try
        {
            _context.Add(entity);
            _context.SaveChanges();

            return entity;
        }
        catch (Exception)
        {
            throw new Exception($"{nameof(entity)} could not be saved");
        }
    }

Anyhow, my issue is that BeginTransaction Commits the things that has done their AddEntity() even if the save method crashes or fails in any way. Can you help me understand why this is and how I can fix this?

I have tried with TransactionScope and it works but when reading several blogposts it sounds like BeginTransaction() is the save and more reliant way to go. Of what I have understood, both of these should be disposed and rollbacked if it does not go through transaction.Commit or scope.Complete, is that correct?

So to summarize or clarify: I'd like to use BeginTransaction() to save several entities to the database but that also rollback / dispose the transaction if anything has gone wrong.

How to dispose/rollback a BeginTransaction() with several SaveChanges()

Answers (1)

Related Questions