Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
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.
Posted 28-Feb-12 19:20pm
Edited 2-Mar-12 2:21am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Dear Friend,
 
Try to use the
BEGIN TRANSACTION
and
ROLLBACK 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
  Permalink  
v3
Comments
vikram5412 at 29-Feb-12 7:57am
   
Can you please explain it with an example.
Thanks
Varun Sareen at 3-Mar-12 0:57am
   
have updated my solution please check and see if it can help.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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
        }
    }
}
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. Smile | :)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Use transactions. If you are writing all procedures one by one from code then go with code level transaction i.e. Connection.BeginTransaction and have catch block rollback the transaction and commit under no exception.
 
or the other idea could be do have a single stored procedure and have transactions there. but making this single procedure depends on the incoming and outgoing parameters.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 5,130
1 DamithSL 4,237
2 Maciej Los 3,700
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,846


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 3 Mar 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100