Is there a way to enclose a set of statements into a Transaction Context?

[dotnetman]

Hello friends,

Now I know that we can enclose a bunch of SQL queries (CommandText of Command object) in a transaction context. That way, if any of the queries fail, the entire transaction is rolled back. The command object allows us to do that. Now, my problem is that I have a bunch of methods, each one of which in turn fires a stored procedure to achieve the above functionality (multiple operations on DB). I call these methods one after other and all of these DB operations take place in a particular order. Now, is it possible that I can enclose this entire set of method calls in a transaction context? In the sense, all the stored procedures that are fired should be rolled back if a stored procedure fails.

 

Your suggestions are highly appreciated.

 

Thank you,

DNM

[PlausiblyDamp]

A Connection object has a BeginTransaction method - this returns a Transaction object; if you assign this to each command's Transaction property then they will all be part of the same transaction.

Alternatively you may want to investigate Component Services under windows (System.EnterpriseServices.dll).

[dotnetman]

A Connection object has a BeginTransaction method - this returns a Transaction object; if you assign this to each command's Transaction property then they will all be part of the same transaction.

Alternatively you may want to investigate Component Services under windows (System.EnterpriseServices.dll).

 

Thank you for your quick reply PD.

 

However, the problem in my case is that I do not have access to the connection or the command object directly. We have created something called as a Data Access Manager in the DAL that takes care of the connection and the command. So I was wondering if there was something that could treat a set of statements as a transaction. I am not sure if it is possible :(

[HJB417]

either recode the flawed DAL or explicity begin and end the transaction using sql, e.x.: "BEGIN TRANSACTION", "COMMIT", "ROLLBACK", etc. I would prefer you perform the former as you will only be building a city on a swamp by doing the latter.