Chandradev's Blog

If we are doing two or more tasks with database, then we should have to use transaction to ensure the task had been completed successfully.

There are two methods to do this task.

1. Using Store procedure in database
2. Using C# code.

Syntax for transaction in store procedure ,please refer by old post

https://chandradev819.wordpress.com/2010/10/24/try-catch-syntax-in-store-procedure/

Using C# code

Ado.net 2.0 and later vesion support transaction feature.

We can write code like this.

 

using (SqlConnection connection =

new SqlConnection(connectionString))

{

using (SqlCommand command =

connection.CreateCommand())

{

SqlTransaction transaction = null;

try

{

// BeginTransaction() Requires Open Connection

connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command

command.Transaction = transaction;

// Execute 1st Command

command.CommandText = “Insert …”;

command.ExecuteNonQuery();

// Execute 2nd Command

command.CommandText = “Update…”;

command.ExecuteNonQuery();

transaction.Commit();

}

catch

{

transaction.Rollback();

throw;

}

finally

{

connection.Close();

}

}

}