CODEWITHSUNDEEP
c#.netcompact-databasenpoco
daniel_aren
daniel_aren

Reputation: 1924

Why doesn't AbortTransaction work?

I am using npoco, trying to work with transactions, I can´t get the AbortTransaction method to rollback anything in my db.

public class ItemRepository
{
  private Func<Database> _db;

  public ItemRepository(Func<Database> db)
  {
        _db = db;
  }

Public void Update(){
    using (_db().Transaction)
    {
        _db().BeginTransaction();
        foreach (var item in itemToUpdate.Items)
        { 
            _db().Insert(item);
            if (SomethingIsNotCorrect())
            {
                _db().AbortTransaction();
            }
        }
    _db().CompleteTransaction();
    }
}}

Call from test class:

_db = () => new Database(String.Format("DataSource={0};Persist Security Info=False;", DbPath),"System.Data.SqlServerCe.4.0");
_itemRepository = new ItemRepository(() => _db());
_itemRepository.Update();

------------- edit after suggested answer:

var db = _db();
using (db.Transaction)
{
    db.BeginTransaction();  
    foreach (var item in itemToUpdate.Items)
    {
        db.Insert(item);
        db.Transaction.Commit();
        if (GetNutrientConns(itemToUpdate).Count > 2)
        {
             db.AbortTransaction();
        }
    }  
    db.CompleteTransaction();
}

Now I must use db.Transaction.Commit() to have something inserting in db. Then when trying to run db.AbortTransaction() I get error message:

"This SqlCeTransaction has completed; it is no longer usable."

Upvotes: 0

Views: 1286

Answers (2)

daniel_aren
daniel_aren

Reputation: 1924

Final solution update method:

var db = _dbFactory();
public void Update() {
try
{
    db.BeginTransaction();
    foreach(item in itemlist)
    {
        db.Insert(item);
    }
    db.CompleteTransaction();
}
catch(Exception)
{
    db.Transaction.Rollback();
}

I am not sure if Rollback are needed in exception because when I testing with throwing exception the transaction dissapears.

Upvotes: 0

Asad Saeeduddin
Asad Saeeduddin

Reputation: 46628

From your update, it is pretty clear what the problem is. You're creating a factory function that instantiates and returns a new connection/datacontext, and passing that into your repository constructor. So far so good.

You're then proceeding to invoke every single transaction related call on a fresh new Database instance that you get from your factory function, which makes no sense. In your constructor, use your factory function to actually get a Database instance, and store that in a field. Use that as many times as necessary.

public class ItemRepository
{
    private Database _db;

    public ItemRepository(Func<Database> dbFactory)
    {
        _db = dbFactory();
    }

    ...
}

UPDATE: As pointed out by Mike C, in the interests of further limiting the scope and lifetime of a datacontext, it would be preferable to store the factory and create a Database instance that lives within the scope of the Update method, like so:

public class ItemRepository
{
    private Func<Database> _dbFactory;

    public ItemRepository(Func<Database> dbFactory)
    {
        _dbFactory = dbFactory;
    }

    public void Update() {
        var db = _dbFactory();

        // Now use db wherever you were using _db()
        ...
    }
}

Upvotes: 2

Related Questions