Maintain Transaction in LINQ to SQL
Hello Folks,
Today I will tell you how to maintain transaction in LINQ to SQL.
Before going on to discuss the detail of maintaining Transaction, it should be made
clear that
LINQ to SQL implicitly maintain transaction
So if there is single call to SubmitChanges() after any number of calls to
InsertOnSubmit, DeleteOnSubmit...
But if you have multiple call to SubmitChanges(), LINQ will not maintain
Transaction and one has to explicitly
maintain Transaction. And that is what we are going to discuss today.
In the example I am going to use, I have taken two Table2 and TestTable.
First Create object of Type
System.Data.Common.DbTransaction
System.Data.Common.DbTransaction transaction;
Now Open the connection. Make sure to use the same DataContext to open connection
which you are going to use
In your LINQ operation.
DataClasses1DataContext dataContext =
new DataClasses1DataContext();
dataContext.Connection.Open();
Now begin the transaction and assign it to transaction object created in first step.
transaction = dataContext.Connection.BeginTransaction();
Go on and set the dataContext Transaction property to transaction.
dataContext.Transaction =
transaction;
At this point 50% of job
is done.
In the try block add the logic to insert the data into the and just after SubmitChanges()
add
transaction.Commit();
If transaction.Commit() line is missing database operation performed
will not be reflected in database
In the Catch block add the code
transaction.Rollback();
and final step
Don’t forget to close the connection you opened at the top
Find below the complete code
static void
Main(string[] args)
{
DataClasses1DataContext dataContext = new DataClasses1DataContext();
System.Data.Common.DbTransaction transaction;
dataContext.Connection.Open();
transaction = dataContext.Connection.BeginTransaction();
dataContext.Transaction = transaction;
try
{
Table2 tbl2 = new
Table2()
{
ID = 1,
Value = "Test Data"
};
dataContext.Table2s.InsertOnSubmit(tbl2);
TestTable tstTbl = new
TestTable()
{
ID = 1,
Name = "Alpha",
lastname = "Beta"
};
dataContext.TestTables.InsertOnSubmit(tstTbl);
dataContext.SubmitChanges();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
Console.WriteLine("Rolback Done");
}
finally
{
if (null !=
dataContext.Connection)
{
dataContext.Connection.Close();
}
}
}
This is all that needs to be done to maintain Transaction in LINQ.
Happy Coding J