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

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 S开发者_运维技巧QL 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.

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();
    }
}

I am always required to provide a RollBack script to our QA team for testing before any change script can be executed in PROD.

Example: Files are sent externally with a bunch of mappings between us, the recipient and other third parties. One of these third parties wants to change, on an agreed date, the mappings between the three of us.

Exec script would maybe update some exisiting, delete some now redundant and insert some new records - scope_identity used in subsequent relational setup etc etc.

If, for some reason, after we have all executed our changes and the file transport is fired up, just like in UAT, we see some errors not encountered in UAT, we might multilaterally make the decision to roll back the changes. Hence the roll back script.

SQL has this info when you BEGIN TRAN until you COMMIT TRAN or ROLLBACK TRAN. I guess your question is the same as mine - can you output that info as a script.

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