Introduction about SQL Transaction

Gomathi Palaniswamy

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,

Commit
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.

System.Data.SqlClient Rollback CommitSQL TransactionSqlTransactionhow to rollback transactionrollback sql transaction

Comments

Join the conversation! Your thoughts help the community grow.