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