使用LINQ2SQL插入数据时，是否有一种自动的方法来生成回退脚本? [英] Is there an automatic way to generate a rollback script when inserting data with LINQ2SQL?

问题描述

让我们假设针对给定的DataContext我们有一堆LINQ2SQL InsertOnSubmit语句.如果SubmitChanges调用成功，是否有任何方法可以自动生成SQL命令列表(甚至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.

注意:目标数据库将是Oracle或SQL Server，因此，如果两个数据库都有特定的功能可以实现此目的，那么我也很乐意使用它.

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.

说明: 我不希望插入成功完成后自动发生回滚".我希望能够在原始程序完成数据插入后最多24小时(例如)通过DELETE(或其他方式)撤消" INSERT语句.我们可以忽略可能出现的任何可能的参照完整性问题.

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.

假设一个表A有两列:Id(自动生成的唯一ID)和Value(字符串)

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

如果LINQ2SQL代码执行两次插入

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

或类似的东西.我希望能够生成DELETE语句以匹配INSERT语句.或使用一些使我能够捕获事务并稍后执行回滚的功能.

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.

推荐答案

执行此操作实际上非常容易，因为您可以在构造中将SqlConnection传递到LINQ to SQL DataContext中.只需在事务中运行此连接，然后在完成后立即回滚该事务即可.

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.

这是一个例子:

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

这篇关于使用LINQ2SQL插入数据时，是否有一种自动的方法来生成回退脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！