Question How to use transaction?

[raysefo]

Hi,

I have to use transactions in order to gain control over db operations like insert and updates. The problem is, those db operations are inside seperate functions. They should be inside seperate functions because i m using them for 13 different web forms. What i want to do is, if one of those db operations (lets say operation1 is insert1 to table1 and operation2 is insert2 to table2) fails then i have to rollback.

Any ideas?

thanks in advance.

Best Regards

 

[kulrom]

Try this:

CREATE PROCEDURE PerformTransaction
 (
    @FunctionOneParam int,
    @FunctionTwoParam int
)

AS

           DECLARE @Success bit;

	BEGIN TRANSACTION
	BEGIN TRY

                  EXEC Your_Function_No_1 @FunctionOneParam;
                  EXEC Your_Function_No_2 @FunctionTwoParam;

                  -- you can call as much as you want functions here but if only one fails it will rollback :)
                  COMMIT TRANSACTION
                  SET @Success = 1
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SET @Success = 0
	END CATCH

           RETURN @Success

 

[cjard]

If youre looking to control transactions from the .NET side of things you should investigate the .Transaction property of your database connection, or the TransactionScope object in MSDN