OleDBCommand, multiple commands

Question

I have an access table that I am using to keep track of a number that increments whenever an export of other data occurs. In the

using (OleDbConnection con = new OleDbConnection(DatabaseProvider + DatabaseFilePath))
{
      con.Open();
      using (OleDbCommand cmd = con.CreateCommand())
      {
           cmd.CommandText = "DELETE FROM IncrementingNumberTable WHERE [Num]=" + curr + ";";
           cmd.Connection = con;
           cmd.ExecuteNonQuery();
      }
      curr++;
      using (OleDbCommand cmd = con.CreateCommand())
      {
           cmd.CommandText = "INSERT INTO IncrementingNumberTable ([Num])VALUES(" + curr + ");";
           cmd.Connection = con;
           cmd.ExecuteNonQuery();
      }
}

The code works as intended but it is possible, though unlikely, that a connection error occurs in the second command, meaning that my incremented number is lost. This occurred while debugging and wouldn't be good while if it happened in a live system.

Answer 1

The way this would normally be handled in a database environment is with a transaction. This would cause a series of commands to succeed or fail as a block, and if an error occurs in the middle it rolls back everything to the initial state.

I've never done this personally with Access (done it many times with SQL Server, MySql, Postgres, etc), but there is an OleDbConnection.BeginTransaction. There is a very nice example on this page showing how to use it.

Based on a comment in the MSDN forums, it appears this works with Access' Jet database engine:

Hi,

Indeed Microsoft Jet doesn't support System.Transactions and TransactionScope.

If you still want to use transactions, you can use native OleDB transactions: http://msdn2.microsoft.com/en-us/library/93ehy0z8.aspx

Cheers,

Florin

(credit Florin Lazar - MSFT)

An alternative solution...

would be to do this operation in a single update command, instead of a delete followed by an insert. Something like this:

cmd.CommandText = "UPDATE IncrementingNumberTable set [Num] = [Num] + 1 WHERE [Num]=" + curr + ";";