Transaction, RollBack, CommitTransaction

[Sanket Patil]

Hi To All....! I have two stored procedure 1)sp_User_Master 2)sp_User_Detail. When I save data it first execute sp_User_Master stored procedure and then sp_User_Detail. Now these are two diffrent stored procedure and are executed one after another. My Problem is when an error occurs in sp_User_Detail stored procedure i am unable to rollback the data saved using sp_User_Master. So is it possible to rollback the data saved by first executed stored procedure. Please help. Thanks to all.

.

[Muhammad Shahid Farooq]

your code look like this: try { begin tran exec sp_User_Master exec sp_User_Detail commit tran } catch (Execption objException) { rollback transaction }

[DoctorMick]

1. What database are you using? 2. What programming language are you using?

[Sanket Patil]

I am Using SQL SERVER 2005 and C SHARP.NET

.

[Robin_Roy]

Use a controller procedure and structured error handling technique. The controller procedure will call the 2 procedures for data insertion and with structured error handling you can eassily RollBack the transaction if any of the procedure fails...

[www Developerof NET]

use this,

cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "Your_SP";
cmd.CommandType = CommandType.StoredProcedure;
if (con.State != ConnectionState.Open)
con.Open();
cmd.Transaction = con.BeginTransaction();

        try
        {
            parentmainid=Subroutine\_For\_Parent\_Insert(userid);
            for (items = 0; items <= \_alldata.Count - 1; items++)
            {
                Subroutine\_For\_Child\_Insert(parentmainid,childdata);
            }
            cmd.Transaction.Commit();
            \_status = true;
        }
        catch (Exception e)
        {
            cmd.Transaction.Rollback();
            \_status = false;
        }

Some important points : 1>Remember to declare cmd as global or pass it as byreference. 2> Do not reinitialze the connection in any of the functions. 3> Use cmd.Parameters.clear() on every function call or else you would get a "Too many parameters error". 4> Do not reinitialize the cmd object during the transaction or else the transaction will lose the data and won`t be rolled back.

When you fail to plan, you are planning to fail.