Introduction about SQL Transaction


This article explains about the SQL Transaction and need of this transaction. The SQL transaction is used to control the database access. The namespace of SQL transaction is System.Data.SqlClient.

The transactional statements can be two types. They are,
  1. Commit
  2. Rollback
Commit:

The commit statement is used to commit the data as it is. That is the data changes can be committed for the current transaction. 

Rollback:

The rollback statement help you to rollback all the changes for the current transaction.

Let see the transactions with an example.

"System.Data.SqlClient" this is the namespace for SQL transaction.

Start the transaction like

SqlTransaction trasaction=connection.BeginTransaction();
try
{
    con.Open();
    //to begin the trancation
    trasaction = con.BeginTransaction();
    //call stored procedure
    SqlCommand oc = new SqlCommand("detail", con);
    oc.CommandType = CommandType.StoredProcedure;
    oc.Transaction = trasaction;
    //insert value
    oc.Parameters.AddWithValue("@name", textBox1.Text);
    oc.Parameters.AddWithValue("@mail", textBox2.Text);
    int affrow = oc.ExecuteNonQuery();
    // if the command executed successfully the data's are committed for this transaction else it move to catch block
    trasaction.Commit();
    MessageBox.Show("Transaction commited");
}
catch(Exception ex)
{
    //is there any error occurred in try block the data's are get rollback
    trasaction.Rollback();      
    MessageBox.Show(ex.ToString());
}

In the above coding the data's will be changed if no error occurs during the process. If any occur in this transformation the above transaction will be rollbacked to its original state.