By default, LINQ to SQL uses an implicit transaction for each SubmitChanges call. All updates between two SubmitChanges calls are wrapped within one transaction.

For example, in the following code, we are trying to update two products. The second update will fail due to a constraint, so both updates will fail. Nothing will be written to the database.

Product prod1 = (from p in db.Products
where p.ProductID == 4
select p).First();
Product prod2 = (from p in db.Products
where p.ProductID == 5
select p).First();
prod1.UnitPrice += 1;
// update will fail because UnitPrice can't be < 0
prod2.UnitPrice = -5;
// both updates will fail because they are wihtin one transaction
db.SubmitChanges();

The output will look like this :

In addition to implicit transactions, you can also define a transaction scope, to explicitly control the update behavior. All updates within a transaction scope will be within a single transaction, Thus, they will either all succeed or all fail.

For example, in the following code, we started a transaction scope first. Then, within this transaction scope, we updated one product, and submitted the change to the database. However, at this point, the update had not really been committed, because the transaction scope was still not closed. We then tried to update another product, which failed due to the same constraint as mentioned in the previous example. The final result is that neither of these two products have been updated; nor can we say that the first update has been rolled back.

using (TransactionScope ts = new TransactionScope())
{
try
{
Product prod1 = (from p in db.Products
where p.ProductID == 4
select p).First();
prod1.UnitPrice += 1;
db.SubmitChanges();
// now let's try to update another product
Product prod2 = (from p in db.Products
where p.ProductID == 5
select p).First();
// update will fail because UnitPrice can't be < 0
prod2.UnitPrice = -5;
db.SubmitChanges();
}
catch (System.Data.SqlClient.SqlException e)
{
// both updates will fail because they are wihtin one transaction
Console.WriteLine("Updates failed. Error Message: {0}", e.Message);
}
}

Note that TransactionScope is in .NET Assembly System.Transactions. So you need to add a reference to System.Transactions first, and then add the following using statement to the Program.cs file:

using System.Transactions;

The output of the program is the same as shown in the previous example, in which an implicit transaction was used.

If you start the program in debugging mode, after the first SubmitChanges is called, you can go to SQL Server Management Studio, and query product 4's price using the following statement:

select UnitPrice from products (nolock) where productID = 4

The nolock hint is equivalent to READUNCOMMITTED, and it is used to retrieve dirty data that has not been committed. With this hint, you can see its price has been increased by the first change. Then, after the second SubmitChanges is called, an exception is thrown, and the transaction scope is closed. At this point, if you run the query again, you will see that product 4's price is rolled back to its original value.

Because LINQ to SQL is a part of the ADO.NET family, it can also participate in an existing ADO.NET transaction. Regardless of whether the updates are done in the traditional ADO.NET code, or in LINQ to SQL, all of them will be committed at the same time, or all rolled back if any of them fails.

In the following code, we will first update a product using a traditional ADO.NET connection, and then update another product using LINQ to SQL. The second update will fail, making the whole transaction roll back.

string connString = "Server=your_db_name\your_db_instance;initial cataLog=Northwind;user=your_user_name;pwd=your_password";
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
// open the connection
conn = new SqlConnection(connString);
conn.Open();
// Use pre-existing ADO.NET connection to create DataContext:
NorthwindDataContext db2 = new NorthwindDataContext(conn);
SqlTransaction trans = conn.BeginTransaction();
try
{
//update first product using ADO.NET
using (cmd = new SqlCommand())
{
cmd.CommandText = "UPDATE Products SET UnitPrice = UnitPrice+1 WHERE ProductID = 4";
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
// update second product using LINQ to SQL
// Share pre-existing ADO.NET transaction:
db2.Transaction = trans;
Product prod2 = (from p in db2.Products
where p.ProductID == 5
select p).First();
// update will fail because UnitPrice can't be < 0
prod2.UnitPrice = -5;
db2.SubmitChanges();
db2.Dispose();
//commit the transaction
trans.Commit();
}
catch (Exception e)
{
// both updates will fail because they are wihtin one transaction Console.WriteLine("Updates failed. Error Message: {0}", e.Message);
}
}
catch (Exception e)
{
Console.WriteLine("Can not connect to database. Error: {0}", e.Message);
}
finally
{
if (cmd != null)
cmd.Dispose();
if (conn != null)
conn.Dispose();
}

There are two things to note in the above code.

using System.Data.SqlClient;

The output of the program is still the same as shown in the previous examples.