如何在MS SQL中应用Rolback

问题描述

循环:从1-5000开始
{
将数据插入表中；
}

假设我正在从循环中向数据库中插入一些值.而且我的循环最多可以持续5000次.每次我进行一些计算并插入
计算结果.但是在2000次之后，我的插入命令发现了一个异常，并且
停止工作；如果我的循环无法继续进行5000次，那么我将面临问题.
在这种情况下，我需要删除所有先前插入的值(在异常之前).我该怎么做?有人告诉我有关回滚系统的信息.但是我不知道它是如何工作的

注意:我正在使用MS SQL Server 2005

提前谢谢..

Rashed

Hi,

Loop: From 1-5000
{
Insert data into table;
}

suppose i m inserting some values into database from loops. and my loops will continue up to 5000 times. Every times i m doing some calculation and inserting the
calculated result. But after 2000 times my insert command found an exception and
stop to work; If my loops does not continue 5000 times then i will face problem.
In this case , i need to delete all the previous inserted values(before exception).How can i do this?. Some body told me about rolback system. But i dont know how it works

NB: I am using MS SQL Server 2005

Thanks in advance..

Rashed

推荐答案

BEGIN TRY --Start the Try Block..
    BEGIN TRANSACTION -- Start the transaction..

        UPDATE MyChecking SET Amount = Amount - @Amount
            WHERE AccountNum = @AccountNum

        UPDATE MySavings SET Amount = Amount + @Amount
            WHERE AccountNum = @AccountNum

    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
END CATCH

那就是如果您要在TSQL存储过程中完成全部操作

您也可以使用.Net代码实现相同的功能，例如Google进行 c#SQL事务或类似的方法:)

http://www.knowdotnet.com/articles/transactions.html [ ^ ]

That''s if you were doing the whole thing in a TSQL stored procedure

You can achieve the same thing using .Net code as well, google for c# SQL transactions or something along those lines :)

http://www.knowdotnet.com/articles/transactions.html[^]

BEGIN TRANSACTION

执行您的工作，如果一切都成功完成，则发出

Perform your work, and if it all completes successfully, you issue a

COMMIT TRANSACTION

如果需要回滚，则发出

ROLLBACK TRANSACTION

方法2:在C#内部

打开连接时，需要向其附加事务.为此，我想显式创建一个事务，如下所示:

Method 2: Inside C#

When you open your connection, you need to attach a transaction to it. To do this, I like to explicitly create a transaction like this:

connection.Transaction = new SqlTransaction();

Do您的工作，如果成功，请按以下方式提交事务:

Do your work, and if it is successful, commit the transaction like this:

connection.Transaction.Commit();

如果失败，请使用

connection.Transaction.Rollback();

由此您可以知道我已将回滚放在catch块中.

From this you can tell that I put the rollback in a catch block.

这篇关于如何在MS SQL中应用Rolback的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！