How to Commit and Rollback Transaction in .NET & Sql Server

If you are sending several commands to SQL Server database, you should make it a habit to build your application to be rollback capable in order to recover all commands that were already executed. In ADO.NET, we can achieve this recovery process by using the SqlTransaction object. A SqlTransaction’s ROLLBACK method can undo any work performed since the BeginTransaction method was executed while the COMMIT method ends a transaction and makes all changes visible to other users.

Example C#:

SqlTransaction trans = null;

try
{
con.Open();
// BeginTransaction() Requires Open Connection
trans = con.BeginTransaction();

// Assign Transaction to Command
cmd.Transaction = trans;

//1st command to execute. This will be rollback if 2nd command fails
cmd.CommandText = “INSERT INTO HeaderTab (Description) VALUES (‘Inserted New Category’)”;
cmd.ExecuteNonQuery();

//2nd command to execute
cmd.CommandText = “INSERT INTO DetailedTab (Description) VALUES (‘TEST CATEGORY’)”;
cmd.ExecuteNonQuery();

trans.Commit();
}
catch
{
trans.Rollback();
Console.WriteLine(“Error while processing command. Previous execution was rollbacked”);
}
finally
{
con.Close();
}

Example SqlServer:

CREATE PROCEDURE DeleteEmployee
( @EmployeeID int )
AS
BEGIN
BEGIN TRY
 BEGIN TRANSACTION TraDel— Start the transaction

— Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

— If we reach here, success!
COMMIT TRANSACTION TraDel
END TRY
BEGIN CATCH
— Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION TraDel

— Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s