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

DbTransaction and EnterpriseLibrary Data Application Block 5.0

Using DbTransaction with EnterpriseLibrary Data Application Block 5.0 is very easy. In this post, i am inserting multiple rows in 5 tables in sql server database. If insert fails in one table, then all transaction will be rollback.

The complete code is given below,

using System;
using System.Collections.Generic;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Db2_EntLib5._0_Transaction
{
class Program
{
static void Main(string[] args)
{
List<string> sqlCollection = new List<string>();

sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A0’)”);
sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A1’)”);
sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A2’)”);
sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A3’)”);
sqlCollection.Add(“INSERT INTO TABLE_B (Name) VALUES (‘B’)”);
sqlCollection.Add(“INSERT INTO TABLE_C (Name) VALUES (‘C’)”);
sqlCollection.Add(“INSERT INTO TABLE_D (Name) VALUES (‘D0’)”);
sqlCollection.Add(“INSERT INTO TABLE_D (Name) VALUES (‘D1’)”);
sqlCollection.Add(“INSERT INTO TABLE_E (Name) VALUES (‘12345678901234567890123456789012345678901234567890’)”);
sqlCollection.Add(“INSERT INTO TABLE_F (Name) VALUES (null)”); //null value is not allowed. So, exception will occur here.

Database database = DatabaseFactory.CreateDatabase(“DBConn”);

using (DbConnection connection = database.CreateConnection())
{
DbTransaction transaction = null;

try
{
if (connection.State != System.Data.ConnectionState.Open)
connection.Open();

transaction = connection.BeginTransaction();

foreach (string sql in sqlCollection)
{
database.ExecuteNonQuery(transaction, System.Data.CommandType.Text, sql);
}

transaction.Commit();
}
catch (Exception error)
{
Console.WriteLine(error);
transaction.Rollback();
}
finally
{
transaction.Dispose();
connection.Close();
}
}

Console.Read();
}
}
}

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;

}

Email critical exceptions using Enterprise Library 5.0 Logging Application Block

I got a task where I need to email specific exceptions (like database connectivity issues) to an email address.  The idea is to notify the support team immediately about the critical issues. My project was developed using Visual Studio 2010, ASP.NET and C#.  In order to complete this task,  I used Microsoft Enterprise Library 5.o Logging Application Block. The steps are very simple and it is given below,

a) Install Microsoft Enterprise Library 5.0 in your system. You can download from : http://www.microsoft.com/en-us/download/details.aspx?id=15104

b) Add reference to Microsoft.Practices.EnterpriseLibrary.Logging in your project.

c) Launch ‘Edit Enterprise Library V5 Configuration’ wizard by right clicking the web.config file as shown below,

d) Under ‘Blocks’, select ‘Add Logging Settings’. This will display default logging settings in the wizard.

e) Now define the exception categories (Lets say, General, Error, Critical etc). The idea is to send email only if the exception category is Critical. Now add the email trace listener and provide necessary details as shown in the image below. Add a log message formatter. In my case, I added Text Formatter. In email trace listener set the formatter as ‘Text Formatter’. In Critical category, set the Listeners as Email Trace Listener. Finally, the settings should look as shown below,

f) Save the file and close it. The settings will be displayed in the web.config file.

g) In the code, add multiple catch blocks as shown below and set the category to ‘Critical’ for those exceptions you want to capture in email.

Now, whenever there is a null reference exception, an email will be sent.

Sample email with exception is given below,