What is Transaction:
A transaction is a group of database commands that are treated as a single unit. Database programmers determine what database commands belong in a transaction by using the ACID test: commands must be atomic, consistent, isolated, and durable.
Different ways of Transactions handling:
In ADO.NET, transactions are handled in different ways, depending on the level you are working at:
Dataset Transaction: Data sets provide transaction processing through the RejectChanges and Update methods. Data sets also provide an AcceptChanges method that resets the state of records in a data set to unchanged.
Database Transactions: Connection objects provide transaction processing through the transaction object. Transaction objects track commands performed on a database and provide the Rollback, Commit, and Save methods to restore database state, commit changes, or create a save point within a transaction, respectively.
Enterprise Transactions:The System.EnterpriseServices namespace provides enterprise-level transactions through the ContextUtil class. Enterprise-level transactions use the Microsoft Distributed Transaction Coordinator (DTC) provided with Microsoft SQL Server 2000 to track transactions across multiple Web forms and across multiple COM+ components.
Here we will talk about Database transaction.
You can manage transactions at the database level through a transaction object. Since there are two types of database connections in ADO.NET, there are also two types of transaction object: SqlTransaction and OleDbTransaction.
To use either type of transaction object, follow these steps:
Open a database connection.
Create the transaction object using the database connection object' s BeginTransaction method.
Create command objects to track with this transaction, assigning the Transaction property of each command object to the name of the transaction created in step 2.
Execute the commands. Because the purpose of transaction processing is to detect and correct errors before data is written to the database, this is usually done as part of an error-handling structure.
Commit the changes to the database or restore the database state, depending on the success of the commands.
Close the database connection.
There are two properties in the transaction class:
Connection: Indicates the SqlConnection object associated with the transaction.
IsolationLevel: Specifies the transaction's IsolationLevel.
The IsolationLevel property is an Enumeration object with the following members:
ReadUncommitted: Does not lock the records being read. This means that an uncommitted change can be read and then rolled back by another client, resulting in a local copy of a record that is not consistent with what is stored in the database. This is called a dirty read because the data is inconsistent.
Chaos: Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
ReadCommitted: Locks the records being read and immediately frees the lock as soon as the records are read. This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction. This is the default isolation level.
ReapeatableRead: Locks the records being read and keeps the lock until the transaction completes. This ensures that the data being read does not change during the transaction.
Serializable: Locks the entire data set being read and keeps the lock until the transaction completes. This ensures that the data and its order within the database do not change during the transaction.
The SqlTransaction object provides methods as well. You may use the following methods to work with the transaction:
Commit: The database transaction is committed.
Rollback: A database transaction is rolled back from a pending state. A transaction may not be rolled back once it's committed.
Save: Creates a savepoint in the transaction that can be used to roll back a portion of the transaction, and specifies the savepoint name.
SQL database connections provide one transaction capability that is unavailable for OLE database connections: the ability to create save points within a transaction. Save points let you restore the database state to a specific position within the current transaction.
Here is Example using transaction object:
using System;
using System.Data;
using System.Data.SqlClient;
namespace TransactionExample {
class MyTransactionClass {
static void Main(string[] args)
{
SqlTransaction trans;
SqlConnection conn;
SqlCommand comm;
try
{
conn = new SqlConnection("server=(local);Initial Catalog=Northwind;UID=sa;pwd=sqlpassword");
conn.Open();
comm = conn.CreateCommand();
trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
comm.Connection = conn;
comm.Transaction = trans;
comm.CommandText = "INSERT INTO [user_table] (id, name) VALUES ('100', 'Smith')";
comm.ExecuteNonQuery();
comm.CommandText = "INSERT INTO [user_table] (id, name) VALUES ('100', 'John')";
comm.ExecuteNonQuery();
trans.Commit();
}
catch(Exception e)
{
trans.Rollback();
return "could not save the data.";
}
finally
{
Conn.Close();
}
}
}
}
Hope it helps in understanding the transaction. Any commnet/feedback is welcome.
Atal.
|
| Author: Gowthami Sharma 11 May 2005 | Member Level: Bronze Points : 0 |
In C# I getthe following error. my code is
SqlConnection sqlConn = new SqlConnection( .....);
SqlTransaction sqlTran ;
SqlCommand cmd = new SqlCommand();
try
{
sqlConn.Open();
sqlTran = sqlConn.BeginTransaction();
cmd.Connection = sqlConn;
cmd.Transaction = sqlTran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = lsSPname.Trim() ;
cmd.ExecuteNonQuery();
sqlTran.Commit();
return 0;
}
catch(Exception ex)
{
sqlTran.Rollback();
}
|
| Author: Gowthami Sharma 11 May 2005 | Member Level: Bronze Points : 0 |
In C# I get compile time error. my code is :
SqlConnection sqlConn = new SqlConnection( " xyz");
SqlTransaction sqlTran ;
SqlCommand cmd = new SqlCommand();
try
{
sqlConn.Open();
sqlTran = sqlConn.BeginTransaction();
cmd.Connection = sqlConn;
cmd.Transaction = sqlTran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "procname";
cmd.ExecuteNonQuery();
sqlTran.Commit();
}
catch(Exception ex)
{
sqlTran.Rollback();
}
I get an error that sqlTran is unassigned variable. If I put the lines
sqlTran = sqlConn.BeginTransaction();
cmd.Connection = sqlConn;
cmd.Transaction = sqlTran;
before Try block, then itworks fine. Why it throw error whiel compiling?
|