Is there an automatic way to generate a rollback script when inserting data with LINQ2SQL?

Question

Let's assume we have a bunch of LINQ2SQL InsertOnSubmit statements against a given DataContext. If the SubmitChanges call is successful, is there any way to automatically generate a list of SQL commands (or even LINQ2SQL statements) that could undo everything that was submitted at a later time? It's like executing a rollback even though everything worked as expected.

Note: The destination database will either be Oracle or SQL Server, so if there is specific functionality for both databases that will achieve this, I'm happy to use that as well.

Clarification: I do not want the "rollback" to happen automatically as soon as the inserts have succesfully completed. I want to have the ability to "undo" the INSERT statements via DELETE (or some other means) up to 24 hours (for example) after the original program finished inserting data. We can ignore any possible referential integrity issues that may come up.

Assume a Table A with two columns: Id (autogenerated unique id) and Value (string)

If the LINQ2SQL code performs two inserts

 INSERT INTO Table A VALUES('a') // Creates new row with Id = 1
 INSERT INTO Table A VALUES('z') // Creates new row with Id = 2

<< time passes>>

At some point later I would want to be able "undo" this by executing

 DELETE FROM A Where Id = 1
 DELETE FROM A Where Id = 2

or something similar. I want to be able to generate the DELETE statements to match the INSERT ones. Or use some functionality that would let me capture a transaction and perform a rollback later.

We cannot just 'reset the database' to a certain point in time either as other changes not initiated by our program could have taken place since.

Answer 1

It is actually quite easy to do this, because you can pass in a SqlConnection into the LINQ to SQL DataContext on construction. Just run this connection in a transaction and roll that transaction back as soon as you're done.

Here's an example:

string output;

using (var connection = new SqlConnection("your conn.string"))
{
    connection.Open();
    using (var transaction = connection.StartTransaction())
    {
        using (var context = new YourDataContext(connection))
        {
            // This next line is needed in .NET 3.5.
            context.Transaction = transaction;

            var writer = new StringWriter();
            context.Log = writer;

            // *** Do your stuff here ***

            context.SubmitChanges();

            output = writer.ToString();
        }

        transaction.Rollback();
    }
}

Answer 2

Why do you need this?

Maybe you should explore the flashback possibilities of Oracle. It makes it possible to travel back in time.

It makes it possible to reset the content of a table or a database to how it once was at a specific moment in time (or at a specific system change number).

See: http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm