[LINQ via C# series] 

Database data Changing cannot be talked about without transactions.

Implementing TRANSACTION (BEGIN / COMMIT / ROLLBACK)

The previous post has shown that, when invoking SubmitChanges(), the translated SQL (INSERT / UPDATE / DELETE) are always executed within a TRANSACTION.

Internally, DataContext.SubmitChanges() invokes DataContext.SubmitChanges(ConflictMode.FailOnFirstConflict). The latter is implemented like this:

public class DataContext : IDisposable
{
    public virtual void SubmitChanges(ConflictMode failureMode)
    {
        if (this._isInSubmitChanges) // Concurrency is not allowed.
        {
            throw new InvalidOperationException(
                "The operation cannot be performed during a call to SubmitChanges.");
        }

        if (!this.ObjectTrackingEnabled) // Tracking must be enabled.
        {
            throw new InvalidOperationException(
                "Object tracking is not enabled for the current data context instance.");
        }

        this._isInSubmitChanges = true;

        try
        {
            if (Transaction.Current != null ||
                this.Transaction != null) // Custom transaction is specified.
            {
                // Process changes...
                return;
            }

            try
            {
                try
                {
                    this.Transaction = this.Connection.BeginTransaction(
                        IsolationLevel.ReadCommitted); // BEGIN TRANSACTION
                    // Process changes...
                    this.Transaction.Commit(); // COMMIT TRANSACTION
                }
                catch
                {
                    this.Transaction.Rollback(); // ROLLBACK TRANSACTION
                    throw; // Failure is notified to the caller.
                }

                return; // Successes.
            }
            finally
            {
                this.Transaction = null; // Finally block ensures clearing transaction.
            }
        }
        finally
        {
            this._isInSubmitChanges = false; // Finally block ensures resetting the flag.
        }
    }
}

It ensures all changes (INSERT / UPDATE / DELETE) are submitted within a TRANSACTION.

Conflict will be explained in the next post.

Default transaction

If the DataContext.Transaction has never been set, it is null. In such scenarios LINQ to SQL will create a DbTransaction object to implement the TRANSACTION:

try
{
    using (NorthwindDataContext database = new NorthwindDataContext())
    {
        Category[] categories = database.Categories.Take(2).ToArray();
        Console.WriteLine("Category[0]: {0}", categories[0].CategoryName); // Beverages
        categories[0].CategoryName = "Updated";
        // Updating should success.

        Console.WriteLine("Category[1]: {0}", categories[1].CategoryName); // Condiments
        categories[1].CategoryName = "Aotobots of Transformers";
        // Updating should fail in database, because CategoryName is NVARCHAR(15).

        database.SubmitChanges();
    }
}
catch (Exception exception)
{
    Console.WriteLine("{0}: {1}", exception.GetType(), exception.Message);

    // Checks whether any change has been submitted.
    using (NorthwindDataContext database = new NorthwindDataContext())
    {
        Category[] categories = database.Categories.Take(2).ToArray();
        // All records are not updated.
        Console.WriteLine("Category[0]: {0}", categories[0].CategoryName); // Beverages
        Console.WriteLine("Category[1]: {0}", categories[1].CategoryName); // Condiments
    }

    throw;
}

The above code tried to submit two changes, which are translated to two UPDATE statements:

BEGIN TRANSACTION 

exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2
WHERE ([CategoryID] = @p0) AND ([CategoryName] = @p1)',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=1,@p1=N'Beverages',@p2=N'Updated'
-- Successes.

exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2
WHERE ([CategoryID] = @p0) AND ([CategoryName] = @p1)',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=2,@p1=N'Condiments',@p2=N'Aotobots of Transformers'
-- Falis. SubmitChanges() catches a SqlException.

ROLLBACK TRANSACTION -- this.Transaction.Rollback();

-- SubmitChanges() re-throws the SqlException to caller.

Because the second UPDATE fails, Submit() catches a SqlException, then it invoke DbTransaction.Rollback() and re-throw the SqlException to the code in the upper call stack.

Custom transactions

If DataContext.Transaction is set with a custom DbTransaction:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    database.Transaction = database.Connection.BeginTransaction();
    // Now DataContext.Transaction is not null.
}

or current submitting code is bracketed inside a TransactionScope:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    using (TransactionScope transactionScope = new TransactionScope())
    {
        // Transaction.Current is not null here.
    }
}

Then it is not LINQ to SQL’s responsibility to implement the logic of transactions.

Because this is a LINQ / functional programming series, not a SQL / ADO.NET series, the further details of transaction will not be explained. Please check MSDN and Wikipedia for more information.