Transaction with Strongly Typed Dataset in ASP .NET 2.0

if you want to implement transaction with strongly typed dataset it is some what trikey,

Table adapters which are created in typed datasets are implemented in partial classes by visual studio 2005,

if you want to use multiple table adapters in one transaction than you have to follow this sceanario.....

implement partial classes foR all your table adapters like this use onky one connection object for all, (assign new connection to first adapter and to other pass just reference of that connection)

Partial classes are used to extend the functionality provided by TableAdapters. They are to be written in “App_Code” folder. In partial classes, the physical files are scattered any where in the project with the same name like “public partial class ” but when project is compiled all the physical files are combined like one physical file and make the whole class

Example of a partial class

using System;
using System.Data;
using System.Data.SqlClient;
namespace dsGroundSelectTableAdapters
{
public partial class ADAPTERNAME_TableAdapter
{
private SqlTransaction _transaction;
private SqlTransaction Transaction {
get
{ return this._transaction; }
set {
this._transaction = value; }
}
public SqlConnection GetConnection()
{
return this.Connection;
}
public void SetConnection(ref SqlConnection Conn)
{
this.Connection = Conn;
}
public SqlTransaction GetTransaction()
{
return this.Transaction;
}
public void SetTransaction(ref SqlTransaction Trans)
{
this.Transaction = Trans;
foreach (SqlCommand command in this.CommandCollection)
{
command.Transaction = this.Transaction;
}
this.Adapter.InsertCommand.Transaction = this.Transaction;
}
public void BeginTransaction()
{
// Open the connection, if needed
if (this.Connection.State != ConnectionState.Open)
{
this.Connection.Open();
}
// Create the transaction and assign it to the Transaction property
this.Transaction = this.Connection.BeginTransaction();
// Attach the transaction to the Adapters
foreach (SqlCommand command in this.CommandCollection)
{
command.Transaction = this.Transaction;
}
this.Adapter.InsertCommand.Transaction = this.Transaction;
}
public void CommitTransaction()
{
// Commit the transaction
this.Transaction.Commit();
// Close the connection
this.Connection.Close();
}
public void RollbackTransaction()
{
// Rollback the transaction
this.Transaction.Rollback();
// Close the connection
this.Connection.Close();
}
}}


************************
and in code behind file

//////create object of all your adapters

(1) dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter daOBJECT1 = new dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter();

(2) dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter daOBJECT2 = new dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter();

(3) dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter daOBJECTn = new dsDATASETNAMETableAdapters.ADAPTERNAMETableAdapter();

/////////create connection
///////// assign it to first adapter object and give others a refernce ////////to the same

/////////all operation must be performed using first adapter object

//even they all are refernced by a same connectin they all are in one ///////transaction...

SqlConnection Conn= new SqlConnection();
Conn=daOBJECT1.GetConnection();
daOBJECT2.SetConnection(ref Conn);
daOBJECTn.SetConnection(ref Conn);

daOBJECT1.BeginTransaction();

SqlTransaction trnTrans = daOBJECT1.GetTransaction();
daOBJECT2.SetTransaction(ref trnTmpTrans);
daOBJECTn.SetTransaction(ref trnTmpTrans);
try
{
//EXECUTE YOUR QUERIES HERE
// YOU CAN CALL FUNCTIONS TO UPDATE INSERT DATA
//insertDATAInTable();
//updateDATATable();

daOBJECT1.CommitTransaction();
}
catch (Exception ex)
{
daOBJECT1.RollbackTransaction();
}

***********************************
Anil Pandya
Senior Software Programmer
Mumbai
+anil.pandya84@gmail.com