JG Vimalan's Blog

It is all about coding!

This OdbcTransaction has completed; it is no longer usable.

I got this exception when I was trying to perform multiple row insert in a DB2 database using Enterprise Library 5.0 and DbTransaction.

Cause of this exception:

I was trying to invoke ExecuteNonQuery method from Database object instead of DbCommand object. And, when one of the SQL failed (due to syntax or data problem), the catch block caught the exception and tried to rollback but DbTransaction.Rollback failed and it was throwing the above exception.

Solution:

In order to solve this issue, set the transaction object in DBCommand.Transaction property and ExecuteNonQuery in DbCommand. Plus, the connection should be in open state when trying to rollback. The working code sample is given below,

internal static bool InsertRecords(List<string> sqlCollection)
{
Database database = DatabaseFactory.CreateDatabase(Utility.GetDB2ConnectionName);

using (DbConnection dbConnection = database.CreateConnection())
{
DbCommand dbCommand = dbConnection.CreateCommand();
DbTransaction dbTransaction = null;

try
{
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;

foreach (string sql in sqlCollection)
{
dbCommand.CommandText = sql;
dbCommand.CommandType = CommandType.Text;
dbCommand.ExecuteNonQuery();
}

dbTransaction.Commit();
return true;
}
catch (Exception error)
{
Log.Error(error);

if (dbTransaction != null)
dbTransaction.Rollback();
}
}

return false;
}

Advertisements

October 2, 2012 - Posted by | ASP.NET, C#.NET, DB2, Enterprise Library 5.0, VS 2010 |

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: