Working with Tranasction in Dot net.

Posted by vishalneeraj-24503 under ADO.NET category on | Points: 40 | Views : 938
Hi all,

Sometimes it's required to have working with Transaction in our code.If there are multiple DML operations going/executing on.
So we must use Transaction,because if any of the transaction fails,then it's necessary to rollback all the operations that were done.

Dot net provides SqlTransaction class,which is used in dealing with transaction related operations.SqlTransaction has Commit as well as RollBack method.

We can understand it by an example:-

Suppose,we have Employee_Master table, and we are saving multiple records into it.

Here is the code in the btn_save event:-

protected void btn_save_Click(object sender, EventArgs e)
SqlTransaction transaction = null;
SqlConnection con = null;
int row_affected = 0;

con = new SqlConnection("your connection string");

transaction = con.BeginTransaction();
SqlCommand cmd = con.CreateCommand();
cmd.Transaction = transaction;

cmd.CommandText = "Insert into employee_master(employee_code,employee_first_name,employee_last_name,address) values('abc00','vishal','kumar','pune')";

row_affected = cmd.ExecuteNonQuery();

cmd.CommandText = "Insert into employee_master(employee_code,employee_first_name,employee_last_name,address) values('abc01','rajesh','kumar','nagpur')";

row_affected += cmd.ExecuteNonQuery();

cmd.CommandText = "Insert into employee_master(employee_code,employee_first_name,employee_last_name,address) values('abc02','nitin','patil','pune')";

row_affected += cmd.ExecuteNonQuery();

//Commit all the changes.

if (row_affected > 0)
//message shows Records successfully saved.
catch (Exception ex)
//Rollback all the changes to its previous state.

Comments or Responses

Posted by: Suchitrab on: 12/11/2013 Level:Starter | Status: [Member] | Points: 10
Will this create any lock on the table?
How good is it to handle the transaction on .net app then having the same on the SQL Stored Procedure?
please clarify
Posted by: vishalneeraj-24503 on: 12/11/2013 Level:Platinum | Status: [Member] [MVP] | Points: 10
Hi,when you deal with multiple DML operations,then it's good thing to work with Transaction.
Both way you can work,means if you are writing Inline code,then have Transaction it Code otherwise in Stored Procedure.

Login to post response