C# using a transaction with ODBC

Let's say your writing some data to the database and you need to make it transactional.  You know where you need to rollback multiple statements or use multiple statements in one go.  Normally you would do this in the database itself.  But I have found a few times where you have to do this in .net.  My latest was using mySQL to get the new index after I did an insert.  I found the SQL to do so would work fine in a mySQL command prompt or in the phpMyAdmin SQL pane but their .net ODBC driver simply wouldn't allow it.  Epic .net fail mySQL I guess your love for PHP has clouded your vision.  So you end up needing to wrap up multiple SQL calls in a transaction to accomplish this.  I have also had to do this it SQLSERVER but not on a scale like this.  But regardless this is how you can use a .net transaction with multiple SQL statements.

You need to create an OdbcConnection.  Then an OdbcCommand and OdbcTransaction.  Set the command and transactions connection to the connection opened earlier.  Then you call BeginTransaction on the transaction object and make all your SQL command calls.  When you're done you try and finalize the transaction or if any errors occurred rollback the transaction.  You can look at the code below for a good example.




// open a new connection using a default connection string I have defined elsewhere
using( OdbcConnection connection = new OdbcConnection( s_connectionString ) )
{
      // ODBC command and transaction objects
      OdbcCommand command = new OdbcCommand();
      OdbcTransaction transaction = null;

      // tell the command to use our connection
      command.Connection = connection;

      try
      {
           // open the connection
           connection.Open();

           // start the transaction
           transaction = connection.BeginTransaction();

           // Assign transaction object for a pending local transaction.
           command.Connection = connection;
           command.Transaction = transaction;

           // TODO: Build a SQL INSERT statement
           StringBuilder SQL = new StringBuilder();

           // run the insert using a non query call
           command.CommandText = SQL.ToString();
           command.ExecuteNonQuery();

           /* now we want to make a second call to MYSQL to get the new index 
              value it created for the primary key.  This is called using scalar so it will
               return the value of the SQL  statement.  We convert that to an int for later use.*/
           command.CommandText = "select last_insert_id();";
           id = Convert.ToInt32( command.ExecuteScalar() );

           // Commit the transaction.
           transaction.Commit();
     }
     catch( Exception ex )
     {
          Debug.WriteLine( ex.Message );

          try
          {
               // Attempt to roll back the transaction.
               transaction.Rollback();
            }
            catch
            {
                 // Do nothing here; transaction is not active.
              }
         }
}

Comments

Fernando Cubias said…
I don't know if I'm wrong but you never close de Connection.

Popular Posts