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 SQL Server, so if there is specific functionality for both databases that will achieve this, I’m happy to use that as well.

  • Should I index a bit field in SQL Server?
  • Does SQL Server upgrade affect running replications
  • Best Practices working with Datetimeoffset
  • Determine provided values and copied values in SQL Server instead of update trigger
  • MSSQL driver for PHP (ODBC) randomly stop sending Unicode chars
  • Can't Compile using SqlDataConnection in F# on Mono in Linux
  • 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.

  • Removing the Allow Null check box ,, require the table to be re-created
  • Sql Server Value Method Returning only one value not multiple
  • SQL server - can connect via remote server but not local machine - why?
  • SQL Server Advance Forward X Rows With Loop Around
  • How can I extract string ‘abc.com’ from a string http://info@abc.com using SQL server 2008?
  • Partitioned view execution plan
  • 3 Solutions collect form web for “Is there an automatic way to generate a rollback script when inserting data with LINQ2SQL?”

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.