Transactions with LINQ to SQL

This post shows how to implement Transaction with LINQ to SQL


LINQ to SQL supports three distinct transaction models. The following lists describes them all.

1) Explicit Local Transaction

When SubmitChanges is called, if the Transaction property is set to a (IDbTransaction) transaction, the SubmitChanges call is executed in the context of the same transaction.

It is your responsibility to commit or rollback the transaction after successful execution of the transaction. The connection corresponding to the transaction must match the connection used for constructing the DataContext. An exception is thrown if a different connection is used.

2) Explicit Distributable Transaction

You can call LINQ to SQL APIs (including but not limited to SubmitChanges) in the scope of an active Transaction. LINQ to SQL detects that the call is in the scope of a transaction and does not create a new transaction. LINQ to SQL also avoids closing the connection in this case. You can perform query and SubmitChanges executions in the context of such a transaction.

3) Implicit Transaction

When you call SubmitChanges, LINQ to SQL checks to see whether the call is in the scope of a Transaction or if the Transaction property (IDbTransaction) is set to a user-started local transaction. If it finds neither transaction, LINQ to SQL starts a local transaction (IDbTransaction) and uses it to execute the generated SQL commands. When all SQL commands have been successfully completed, LINQ to SQL commits the local transaction and returns.

->Example of simple transaction:-

This is a vary simple example for implementing transaction which implements one insert and one update statement within one transaction. If any Error comes the last statement of Try block will not be executed and data of any statement will not be reflected to database.

try

{
//OBJECT OF YOUR DATA CONTEXT CLASSS (.DBML FILE)

LINQ_to_SQL_ClassDataContext DataContexClassObject = new LINQ_to_SQL_ClassDataContext();

//OBJECT OF TABLE IN DATABASE (DRAGED IN DATA CONTEXT CLASS)

Product_Master Prod = new Product_Master();

//ASSIGN VALUE TO FIELDS OF YOUR TABLE

Prod.Field1 = 1001;
Prod.Field2 = "My new Product";
Prod.Field3 = "This is new product";
Prod.Field4 = 10.00;
Prod.Field5 = 500;

//INSERT DATA TO YOUR DATA CONTEXT CLASS TABLE

DataContexClassObject.Product_Master.InsertOnSubmit(Prod);
DataContexClassObject.SubmitChanges();

//ANOTHER OBJECT OF TABLE IN DATABASE (DRAGED IN DATA CONTEXT CLASS)

Order_Book OrderUpdate = new Order_Book();
OrderUpdate = //LINQ Query which whil return one record which you want to update

//ASSIGN VALUE TO FIELDS OF YOUR TABLE

OrderUpdate.Field3 = "New data for updating previous";
OrderUpdate.Field5 = 550;

//UPDATE DATA TO YOUR DATA CONTEXT CLASS TABLE

DataContexClassObject.Order_Book.InsertOnSubmit(OrderUpdate);
DataContexClassObject.SubmitChanges();

//FINALLY TO UPDATE ENTRIES TO DATABASE TABLES

dcCSE.SubmitChanges();

//TRANSACTION EXECUTED SUCCESSFULLY
}

catch (Exception ex)
{
//TRANSACTION NOT EXECUTED NO EFFECT HAS BEEN MADE TO DATABASE
}

For any query regarding this issues write me on
anil.pandya84@gmail.com
Anil Pandya

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