Working with Tranasction in Dot net.

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;

        try
        {
            con = new SqlConnection("your connection string");
            con.Open();

            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.
            transaction.Commit();

            if (row_affected > 0)
            {
                //message shows Records successfully saved.
            }
        }
        catch (Exception ex)
        {
            //Rollback all the changes to its previous state.
            transaction.Rollback();
            Response.Write(ex.Message.ToString());           
        }
}