尝试捕获块存储过程中的问题 [英] Try catch block Problem with store procedures

问题描述

我在尝试块1中有4个存储过程，其中两个运行良好，并且成功更新了数据，但是第3个不起作用，如何处理此问题.如果四个过程中的任何一个不起作用，那么我需要它不会更新数据.

I have 4 store procedures in try block 1st two are running well and update data successfully but 3rd is not working how to handle this problem. if any one of four procedure will not work then i need it will not update data.

thanks in advance.

推荐答案

亲爱的朋友，

尝试使用

Dear Friend,

Try to use the

BEGIN TRANSACTION

和

and

ROLLBACK TRANSACTION

为了还原在sql表中完成的所有更改/修改

参考链接:-
http://msdn.microsoft.com/en-us/library/ms188929.aspx [ ^ ]

http://msdn.microsoft.com/en-us/library/ms181299.aspx [ ^ ]

有关如何使用Begin&的文章SQL Server中的回滚事务:-

http://www.codeproject.com/Articles/4451/SQL-Server-事务和错误处理 [ ^ ]

http://www.sqlteam.com/article/introduction-to-transactions [ ^ ]

http://dev.mysql.com/doc/refman/5.0/en/commit.html [ ^ ]

http://blog.sqlauthority.com/2010/03 /04/sql-server-rollback-truncate-command-in-transaction/ [

in order to revert all the changes/modification done in the sql table(s)

Refer links:-
http://msdn.microsoft.com/en-us/library/ms188929.aspx[^]

http://msdn.microsoft.com/en-us/library/ms181299.aspx[^]

Article(s) on how to use Begin & Rollback Transaction in SQL Server:-

http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling[^]

http://www.sqlteam.com/article/introduction-to-transactions[^]

http://dev.mysql.com/doc/refman/5.0/en/commit.html[^]

http://blog.sqlauthority.com/2010/03/04/sql-server-rollback-truncate-command-in-transaction/[^]

I hope these will help you out.

Thanks

开始交易
开始尝试
创建过程Procedure1
-过程代码在这里
创建过程Procedure2
-过程代码在这里
创建过程Procedure3
-过程代码在这里
创建过程Procedure4
-过程代码在这里
结束尝试
开始抓捕
回滚
尾部
结束交易

begin transaction
begin try
create procedure Procedure1
--procedure code goes here
create procedure Procedure2
--procedure code goes here
create procedure Procedure3
--procedure code goes here
create procedure Procedure4
--procedure code goes here
end try
begin catch
rollback
end catch
end transaction

这是我在C#中的sql转换模式.

this is my sql-transcation pattern in C#.

using (SqlConnection cn = new SqlConnection(connString)) {
    cn.Open();
    using (SqlTransaction tn = cn.BeginTransaction()) {
        try {
            using (SqlCommand cmd = cn.CreateCommand()) {
                cmd.Transaction = tn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter()).Direction 
                   = ParameterDirection.ReturnValue;

                cmd.CommandText = "MyStoredProcedure1";
                cmd.ExecuteNonQuery();
                if ((int)cmd.Parameters[0].Value != 0) 
                    throw new Exception("MyStoredProcedure1 Failed.");

                cmd.CommandText = "MyStoredProcedure2";
                cmd.ExecuteNonQuery();
                if ((int)cmd.Parameters[0].Value != 0) 
                    throw new Exception("MyStoredProcedure2 Failed.");

                cmd.CommandText = "MyStoredProcedure3";
                cmd.ExecuteNonQuery();
                if ((int)cmd.Parameters[0].Value != 0) 
                    throw new Exception("MyStoredProcedure3 Failed.");

                cmd.CommandText = "MyStoredProcedure4";
                cmd.ExecuteNonQuery();
                if ((int)cmd.Parameters[0].Value != 0) 
                    throw new Exception("MyStoredProcedure4 Failed.");

                tn.Commit();
            }
        } catch (Exception ex) {
            tn.Rollback();
            //TO DO:
            //handle exception after rollback
        }
    }
}

您可以通过StoredProcedure的ReturnValue来处理它，就像这样

you can handle it by StoredProcedure''s ReturnValue,like this

Create Proc MyStoredProcedure3
AS begin
Update myTable set MyField1='value' where MyKey='key'
IF @@ROWCOUNT = 0
    RETURN 1
ELSE
    RETURN 0
end

希望它可以帮助你. :)

hope it can help u. :)

这篇关于尝试捕获块存储过程中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！