LINQ to SQL Transactions

The thing about Transactions in LINQ is that they are rubbish.
It’s all designed around submitting changes to a transaction, great, but if you rollback the transaction those changes can not be submitted a second time, you’d have to manually undo the changes or recreate the DataContext and apply the changes once more.

Below are some examples I put together whilst trying out LINQ to SQL, as they are copied from a Wiki I used to have, please let me know if you spot any errors.

Here’s an example of this with an insert.

VB.NET

Dim testerString As String = "HELLO"
Dim meeee As LINQtoSQLDataClassesDataContext = Nothing

Try
    ' New DataContext
    meeee = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out}

    ' Open connection
    meeee.Connection.Open()

    ' New transaction
    meeee.Transaction = meeee.Connection.BeginTransaction()

    ' Insert command
    meeee.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "East"})

    ' Submit changes (to the transaction)
    meeee.SubmitChanges()

    ' Rollback
    meeee.Transaction.Rollback()

    ' This would fail on submit because the in memory would have a duplicate - which is annoying but kind of makes sense

    'meeee.Transaction = meeee.Connection.BeginTransaction()
    'meeee.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "West"})
    'meeee.SubmitChanges()
    'meeee.Transaction.Commit()

    ' This would have no effect - changes already submitted, so there are none

    'meeee.Transaction = meeee.Connection.BeginTransaction()
    'meeee.SubmitChanges()
    'meeee.Transaction.Commit()

    ' This would have no effect - once submitted there doesn't appear to be any way back

    'meeee.Transaction = meeee.Connection.BeginTransaction()
    'meeee.Refresh(--of any kind--)
    'meeee.SubmitChanges()
    'meeee.Transaction.Commit()
Finally
    If (Not meeee Is Nothing) Then
        If (Not meeee.Connection Is Nothing) Then
            meeee.Connection.Close()
        End If

        meeee.Dispose()
    End If
End Try

C#

String testerString = "HELLO";
LINQtoSQLDataClassesDataContext meeee = null;

try
{
    // New DataContext
    meeee = new LINQtoSQLDataClassesDataContext() { Log = Console.Out };

    // Open connection
    meeee.Connection.Open();

    // New transaction
    meeee.Transaction = meeee.Connection.BeginTransaction();

    // Insert command
    meeee.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "East" });

    // Submit changes (to the transaction)
    meeee.SubmitChanges();

    // Rollback
    meeee.Transaction.Rollback();

    // This would fail on submit because the in memory would have a duplicate - which is annoying but kind of makes sense

    //meeee.Transaction = meeee.Connection.BeginTransaction();
    //meeee.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "West" });
    //meeee.SubmitChanges();
    //meeee.Transaction.Commit();

    // This would have no effect - changes already submitted, so there are none

    //meeee.Transaction = meeee.Connection.BeginTransaction();
    //meeee.SubmitChanges();
    //meeee.Transaction.Commit();

    // This would have no effect - once submitted there doesn't appear to be any way back

    //meeee.Transaction = meeee.Connection.BeginTransaction();
    //meeee.Refresh(--of any kind--);
    //meeee.SubmitChanges();
    //meeee.Transaction.Commit();
}
finally
{
    if (null != meeee)
    {
        if (null != meeee.Connection)
        {
            meeee.Connection.Close();
        }

        meeee.Dispose();
    }
}

This next part is the retry i.e. ditch it and redo

VB.NET

' Start a clean DataContext if you want to retry / redo the updates

Dim meee2 As LINQtoSQLDataClassesDataContext = Nothing

Try
    meee2 = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out}
    meee2.Connection.Open()
    meee2.Transaction = meee2.Connection.BeginTransaction()
    meee2.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "South"})
    meee2.SubmitChanges()
    meee2.Transaction.Commit()
Finally
    If (Not meee2 Is Nothing) Then
        If (Not meee2.Connection Is Nothing) Then
            meee2.Connection.Close()
        End If

        meee2.Dispose()
    End If
End Try

C#

// Start a clean DataContext if you want to retry  redo the updates

LINQtoSQLDataClassesDataContext meee2 = null;

try
{
    meee2 = new LINQtoSQLDataClassesDataContext() { Log = Console.Out };
    meee2.Connection.Open();
    meee2.Transaction = meee2.Connection.BeginTransaction();
    meee2.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "South" });
    meee2.SubmitChanges();
    meee2.Transaction.Commit();
}
finally
{
    if (null != meee2)
    {
        if (null != meee2.Connection)
        {
            meee2.Connection.Close();
        }

        meee2.Dispose();
    }
}

Now is an example of a delete:

VB.NET

' Delete it

Dim meee3 As LINQtoSQLDataClassesDataContext = Nothing

Try
    meee3 = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out}
    meee3.Connection.Open()
    meee3.Transaction = meee3.Connection.BeginTransaction()
    meee3.TableAs.DeleteOnSubmit( _
                meee3.TableAs.Where(Function(x) x.Name = testerString).Select(Function(x) x).FirstOrDefault())
    meee3.SubmitChanges()
    meee3.Transaction.Commit()
Finally
    If (Not meee3 Is Nothing) Then
        If (Not meee3.Connection Is Nothing) Then
            meee3.Connection.Close()
        End If

        meee3.Dispose()
    End If
End Try

C#

// Delete it

LINQtoSQLDataClassesDataContext meee3 = null;

try
{
    meee3 = new LINQtoSQLDataClassesDataContext() { Log = Console.Out };
    meee3.Connection.Open();
    meee3.Transaction = meee3.Connection.BeginTransaction();
    meee3.TableAs.DeleteOnSubmit(
        meee3.TableAs.Where(x => x.Name == testerString).Select(x => x).FirstOrDefault());
    meee3.SubmitChanges();
    meee3.Transaction.Commit();
}
finally
{
    if (null != meee3)
    {
        if (null != meee3.Connection)
        {
            meee3.Connection.Close();
        }

        meee3.Dispose();
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s