如何在MS SQL中应用Rolback [英] How to apply Rolback in MS SQL

问题描述

循环:从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 TRANS [ ^ ]和试用中 [

BEGIN TRANS[^] and ROLLBACK [^] are what you want here.

You might want to wrap the whole thing in a TRY CATCH[^] which makes exception handling much easier...

That might look something like...

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[^]

这取决于您是否从外部来源或SQL Server内部.两者都依赖事务，但是它们使用不同的语法.正如您尚未说明这是什么语言一样，我将不得不在此处做出某些假设(例如，您正在使用C#).

方法1:在SQL Server内部.

在SQL Server中，您需要使用

It depends whether you are doing this from an external source, or inside SQL Server. Both rely on transactions, but they use different syntax. As you haven''t stated what language this is from, I''m going to have to make certain assumptions here (e.g. you are using C#).

Method 1: Inside SQL Server.

In SQL Server, you need to start a transaction using

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！