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.