GENERAL QUERY :- ROLL BACK TRANSACTIONS

Posted by Devbhagat2003 under VB.NET on 4/23/2010 | Views : 3295 | Status : [Member] | Replies : 2
Can I roll back my db changes. Here is a scenario.

Code:

Try
Function 1():- Enters a record in Employee table
Function 2():- Enter a record in customer table
Function 3():- Some Updates to other tables.Emails etc
Catch

Finally

End Try

Q:- If there is an Exception thrown in Function3(), Can I roll back the work done in Function1() and Function2()??
function 1's give new employee id and this new Employee ID is used in 2nd function.
Second function use the New Employee Id, and creates New Customer Id,
Third Function, use both Employee Id, Customer Id and do some emailing part, update entries in other tables.

From here, if in Function3(), any exception occurs, I want New employee Id, and new customer Id, gets deleted.
or If any exception occurs in Function 2, only employee id should be removed.

I CAN NOT CREATE 1 FUNCTION FOR THESE 3 FUNCTIONS, AS IN MY REAL code, THERE IS A LOT OF OTHER WORK GOING ON IN 3 FUNCTIONS.

CAN YOU PLEASE HELP?

Answer:- Which I think will work?
Throw your own exception from function 2 and 3, then accordingly, delete the previous data entered
Try
Function 2():-
Catch

Dim myEx as new exception("101")
throw myEx

Finally

End Try


Try
Function 3():-
Catch

Dim myEx as new exception("102")
throw myEx

Finally
End Try



main Function from where these 3 are called:

if exception.message= "101"
' Code to delete employee id

else if exception.message= "102"
' Code to delete employee id and code to delete customer id

IS IT THE GOOD APPROACH TO DO THIS ? IS THERE A METHOD LIKE BEGIN TRANS, END TRANS

pLEASE REPLY.

REGARDS,
DEV




Responses

Posted by: Abhi2434 on: 4/23/2010 [Member] [Microsoft_MVP] [MVP] Silver

Up
0
Down
Yes it is possible.

I guess, why dont you create one Stored Proc and execute the elements. You can easily open a new Transaction to the SP, and run your query. Whenever any error encounters, just rollback the Transaction.

Other than that
you can use
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
command.Transaction = transaction;

to do the same .
Check it here :
http://www.sql-server-performance.com/articles/asp_ado/ado_transactions_p1.aspx

:)

www.abhisheksur.com

Devbhagat2003, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 4/23/2010 [Member] Bronze

Up
0
Down


Yes it is possbile to roll back the transcation

SqlConnection objCon = new SqlConnection(ConfigurationManager.AppSettings["Constring"].ToString());
SqlTransaction Strac;
objCon.Open();
Strac = objCon.BeginTransaction();
try
{
function1();
function2();
function3();
Strac.Commit();
}
catch (Exception ex)
{
Strac.Rollback();
}


if any exception is raised in any of the three functions the transcation is rolled back.

Thanks
Pavan Kumar
Mark Answer if this fits the need

Devbhagat2003, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response