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;
}

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

Connect to DB2 database using Enterprise Library 5.0 – Data Access Application Block

In this post, I will show you how to connect to DB2 database using Enterprise Library 5.0 – Data Access Application Block. The code snippets are developed using C#, ASP.NET and Visual Studio 2010.

Step 1:

Install Enterprise Library 5.0 and IBM Db2 Client (I am using 9.7 fix 6) in your development system.

Step 2:

Add reference to Microsoft.Practices.EnterpriseLibrary.Data in your project.

Step 3:

In the web.config file, add the connection string for target DB2 database,

<connectionStrings>
<add name=”DB2Conn” connectionString=”Dsn=CustomerDb2;uid=abcdefg;pwd=tempassword”
providerName=”System.Data.Odbc” />

Step 4:

In the app settings, add the Db2 schema name as shown below,

<add key=”DB2SchemaName” value=”DevSerDb2″ />

In my case, the schema name is DevSerDb2.

Step 5:

In my Utility class, I added properties for accessing the values from Web.config file as shown below,

public class Utility
{

internal static string GetDB2SchemaName
{
get { return ConfigurationManager.AppSettings[“DB2SchemaName”]; }
}

internal static string GetDB2ConnectionName
{
get { return string.Format(“DB2Conn”); }
}

Step 6:

In your data access class, add reference as shown below,

using Microsoft.Practices.EnterpriseLibrary.Data;

Step 7:

I have a stored procedure in Db2 which accepts 2 parameters and returns multiple rows of records. The code snippet for connecting to it and retrieving the values is as follows,

public DataSet Search(string userId, string firstName)
{
DataSet resultSet = null;
StringBuilder sql = new StringBuilder(“Call “).Append(Utility.GetDB2SchemaName).Append(“.SP_CUSTOMER_LIST(?,?)”);
Database database = DatabaseFactory.CreateDatabase(Utility.GetDB2ConnectionName);
using (DbCommand dbCommand = database.GetStoredProcCommand(sql.ToString()))
{
OdbcParameter userIdParam = new OdbcParameter(“@userId”, OdbcType.VarChar, 24);
userIdParam.Direction = ParameterDirection.Input;
userIdParam.Value = userId;
dbCommand.Parameters.Add(userIdParam);

OdbcParameter firstNameParam = new OdbcParameter(“@firstName”, OdbcType.VarChar, 24);
firstNameParam.Direction = ParameterDirection.Input;
firstNameParam.Value = firstName;
dbCommand.Parameters.Add(firstNameParam);

resultSet = database.ExecuteDataSet(dbCommand).Tables[0];
}
return resultSet;

}

August 28, 2012 Posted by | C#.NET, DB2, Enterprise Library 5.0 | Leave a comment