JG Vimalan's Blog

It is all about coding!

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

September 29, 2012 Posted by | C#.NET, Enterprise Library 5.0 | , | Leave a comment