Sometimes we come across a situation where we need to execute two or more SQL commands in such a way that - if any one of the statements fails, then no other statement will be able to change the database. For that we can use concept TRANSACTION
There are three importance commands to manage a transaction. BEGIN
TRANSACTION will begin a transaction, COMMIT TRANSACTION will commit the transaction to the database, and ROLLBACK TRANSACTION will roll the transaction back.
Within .NET, transactions are managed with the System.Data.SqlClient.SqlTransaction class.A transaction exists over a SqlConnection object and thus all the SqlCommand objects you create using that connection. Let's look at a quick example:
using System.Data.SqlClient;
SqlConnection myConnection = new SqlConnection("");
myConnection.Open();
// Start a local transaction
SqlTransaction myTrans = myConnection.BeginTransaction();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "Insert into user_reg (your column name)VALUES (your values goes here)";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "delete from user_reg where ID=101";//
// Attempt to commit the transaction.
myCommand.ExecuteNonQuery();
myTrans.Commit();
Response.Write("Both records are written to database.");
}
catch (Exception ep)
{
// Attempt to roll back the transaction.
myTrans.Rollback();
Response.Write(ep.ToString());
Response.Write("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
As you can see from this example, we first open a connection to the SQL database. We then call the
BeginTransaction method on the connection, keeping a reference to the object that it returns. At this point, the connection is bound to the SqlTransaction object that was returned. This means that any SqlCommand executed on that connection will be within the transaction.
There are essentially, two operations you can use on the
SqlTransaction object. Rollback will cancel your transaction, undoing all the changes that have been made. Commit will cause the transaction to be written to the database permanently. Either case will end the transaction.
Transactions are useful for several other things. First, they provide a way to rollback a group of SQL statements if a single one should.
Properties of Transactions
Transactions have the following four standard properties, usually referred to by the acronym ACID.
Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
Consistency − ensures that the database properly changes states upon a successfully committed transaction.
Isolation − enables transactions to operate independently of and transparent to each other.
Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.
Transaction Control
The following commands are used to control transactions.
COMMIT − to save the changes.
ROLLBACK − to roll back the changes.
SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
SET TRANSACTION − Places a name on a transaction.
Properties of Transactions
Transactions have the following four standard properties, usually referred to by the acronym ACID.
Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
Consistency − ensures that the database properly changes states upon a successfully committed transaction.
Isolation − enables transactions to operate independently of and transparent to each other.
Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.
Transaction Control
The following commands are used to control transactions.
COMMIT − to save the changes.
ROLLBACK − to roll back the changes.
SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
SET TRANSACTION − Places a name on a transaction.
