Saturday, 10 September 2016

Chapter-7 Managing Ado.net Transactions




Processing Transaction
A Transaction in Ado.net is a group of Commands that change the data source in a database.The transaction which is treated as Single unit and assures the commands will run sequentially .

1)      Ado.Net transaction  can be defined as a sequence of operations that are performed together as a single logical unit of work.
2)      If a transaction is successful, all the data modifications performed in the database will be committed and saved.
3)      If a transaction fails or an error occurs, then the transaction is rolled back to undo the data modifications done in the database.


4)      For a transaction to commit successfully within a database, it should possess the following four properties:

a)      Atomicity : Single unit of work. States that either all the modifications are performed or none of them are performed.
b)      Consistency : All relationships between data in a database are maintained correctly. States that data is in a consistent state after a transaction is completed successfully to maintain the integrity of data
c)       Isolation : Change made by the other clients cannot effect the current changes. States that any data modification made by one transaction must be isolated from the modifications made by the other transaction
d)      Durability :Once a change is made, it is permanent . If a system error or power failure occurs before a set of commands, those commands are undone and the data is restored to its Original state. States that any change in data by a completed transaction remains permanently in effect in the database

5)      Transaction is important for Web application because it is distributed among different clients across the world. There are various Key problems:-

a)      Contention for resources : Various clients try to change the same record and same time.
b)      Unexpected Failures : Internet is not 100% reliable due to sometime the power failure or internet failure by service provider ,the user abnormally disconnect by the web server.
c)       Web application life cycle : We application life cycle is not same as Window application life cycle. Web forms have only one instant .Client can leave your website at any point of contact after entering the address in the new browser.

6)      ADO.NET provides support for two types of transactions :-

a)      Local transactions : A local transaction performs on a single data source. Because local transactions are performed on a single data source, these transactions are efficient to operate and easy to manage.
b)      Distributed transactions : A distributed transaction performs on multiple data sources. Distributed transactions enable you to incorporate several distinct transactional operations into an atomic unit that either succeed or fail completely.

7)      In Ado.net transaction handle at different ways:-

a)      Datasets provide transaction processing through RejectChanges and Update methods.
b)      Database connection objects provide transaction processing through the Transaction object.
c)       System.EnterpriseService namespace provide enterprise-level transaction through ContextUtil class.

8)      ADO.NET has an interface, IDbTransaction that contains methods for creating and performing local transactions against a single data source. 

Types of Transaction Classes
Description
System.Data.SqlClient.SqlTransaction
Transaction class for .NET framework data provider for SQL Server
System.Data.OleDblClient.OleDbTransaction
Transaction class for .NET framework data provider for OLE DB
System.Data.Odbc.OdbcTransaction
Transaction class for .NET framework data provider for ODBC
System.Data.OracleClient.OracleTransaction
Transaction class for .NET framework data provider for Oracle

Let us understand how to create a local transaction.

//Creating a connection to the data source
string connectString ="Initial Catalog=AdventureWorks; Data Source=SQLSERVER01;User id=sa;Password=Demo#1234";
SqlConnection cn = new SqlConnection();
cn.Open();
cn = connectString;
SqlTransaction tran = null;
try
{
//Calling the BeginTransaction() method
tran = cn.BeginTransaction();
//Creating the command object and passing the SQL command
SqlCommand cmd = new SqlCommand("INSERT INTO
empdetails(ccode,cname, caddress,cstate,ccountry, cDesignation,cDepartment)VALUES(1101,’Jeniffer Kelvin’', ‘Paris’,'London','UK','Manager','Finance')", cn, tran);
cmd.ExecuteNonQuery();                                  
//Committing the transaction if the command succeeds
tran.Commit();
Console.WriteLine("Transaction
Committed\n)";
}
catch (SqlException ex)
{
//Rolling back the transaction if the command fails
tran.Rollback();
Console.WriteLine("Error - TRANSACTION ROLLED BACK\n" + ex.Message);
}
catch (System.Exception ex)
{
Console.WriteLine("System Error\n" + ex.Message);
}
finally
{
cn.Close();
}
Console.ReadLine();
}
}
}

DataSet Transaction

DataSet provide implicit transaction processing because changes to a data set are not made in database until you invoke the Update method on the data adapter.
If an error occurs during the Update method none of the changes from the data set is made in the database.

ButtonRestore_click()
{
//Restore the dataset its original state
Ds.RejectChanges();
Gridview1.DataBind();
}
ButtonCommit_Click()
{
int rows;
//Update the database from the data set
rows=adt.Update(ds);
//Save changes to state variable
Session[“dsContacts”]=ds;
Gridview1.DataBind();
}

RejectChanges method returns the data set to its state before the row was deleted. Data set AcceptChanges method is the inverse of RejectChanges, it reset the DataRowState property for all the changes rows in dataset to Unchanged and remove any deleted rows.

AcceptChanges method prevents the Update method from making those changes in the database.It is useful only when you do not intend to update a database from data set.

Managing Distributed Transactions

1)      Distributed transactions are performed on multiple data sources or multiple connections within a data source.
2)      Distributed transactions are created in the System.Transaction namespace.
3)      The System.Transaction namespace has a TransactionScope class, which enables a developer to create and manage distributed transactions.
4)      To create a distributed transaction, a TransactionScope object is created in a using block.
5)      The TransactionScope object decides whether to create a local transaction or a distributed transaction. This is known as transaction promotion.
6)      Let us understand how to create a distributed transaction.

Database Transaction Steps:-

1)      Open a database connection
2)      Create the transaction object using database connection BeginTransaction method
3)      Create command objects to track with this transaction, assigning transaction
4)      Execute the commands
5)      Commit the changes to database or restore the database
6)      Close the database connection

using (TransactionScope ts = new TransactionScope())
{
//Creating the TransactionScope object
using(SqlConnection cn = new SqlConnection("InitialCatalog=ABC;DataSource=SQLSERVER01;Userid=sa;Password=Demo#1234"))
{
cn.Open();
//Creating a SqlCommand object to insert a record in the Department table
using(SqlCommand cmd = new SqlCommand("INSERT INTO Department(cUserName,cPassword)
VALUES('Darren','Cooper')", cn))
{
int rowsUpdated =cmd.ExecuteNonQuery();
if (rowsUpdated > 0)
{
SqlConnection cn1 = new
SqlConnection("Initial Catalog=ABC;Data Source=SQLSERVER01;User id=sa;Password=Demo#1234"))
{
cn1.Open();
//Creating another connection to the same data source
using (SqlCommand cmd1 =new SqlCommand("DELETE Department WHERE cDepartmentCode=1111", cn1))
{
int rowsUpdated1 =      cmd1.ExecuteNonQuery();
if (rowsUpdated1 > 0)
{
//Calling the Complete() method to commit the transactions
ts.Complete();
Console.WriteLine("Transaction Committed\n");
cn1.Close();
}
}}}
cn.Close();
}}
Console.ReadLine();
}}}

Performing Bulk Copy Operations in a Transaction

1)      Bulk copy operations can be performed as an isolated operation or as a part of a transaction.
2)      By default, a bulk copy operation is its own transaction.
3)      To perform a bulk copy operation, you need to create a new instance of BulkCopy class with a connection string.
4)      The bulk copy operation creates, and then, commits or rolls back the transaction.

Specifying Isolation Levels of a Transaction

1)      An isolation level determines the effect a transaction has on other transactions that are currently running, and vice versa.
2)      By default, all transactions are completely isolated and run concurrently without impacting each other.
3)      The isolation level of a transaction specifies the locking strategy used by the connection running the transaction to prevent concurrency problems when multiple transactions access the same data.
4)      The following table describes the concurrency errors that can occur if multiple transactions access the same data at the same time.

Concurrency Error
Description
Dirty read
A transaction reads the data that has not been committed by the other transaction. This can create problem if a transaction that has added the data is rolled back.
Nonrepeatable read
A transaction reads the same row more than once and a different transaction modifies the row between the reads.
Phantom read
A transaction reads a rowset more than once and a different transaction inserts or deletes rows between the first transaction’s reads.

1)      The various types of isolation levels of a transaction are:

a)      Read Uncommitted : 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 data is stored in databse .It is also known as dirty read.
b)      Read Committed with Locks : Locks the records being read and immediately free the lock as soon as the records have been read. This prevents any changes from being read before they committed.
c)       Read Committed with Snapshots : Locks the records after taking Snapshots and immediately free the lock as soon as the records have been read.
d)      Repeatable Read : Locks the records being read and keeps the lock until the transaction completes. This ensures that data being read does not change during the transaction:
e)      Serializable : Locks the entire data set being read and keeps the lock until the transaction completes. This ensured that data and its order within database donot change during the transaction.
SQL database connections provide one transaction capability that is unavailable for OLE database connection : the ability to create save points within the transactions.

The following table describes the various isolation levels and the corresponding concurrency errors for a transaction.

Isolation Level
Dirty Read
Nonrepeatable Read
Phantom Read
Read Uncommitted
Yes
Yes
Yes
Read Committed with Locks
No
Yes
Yes
Read Committed with Snapshots
No
Yes
Yes
Repeatable Read
No
No
Yes
Snapshot
No
No
No
Serializable
No
No
No

Let us understand how to set the isolation level of a transaction to Read Committed.

//Initializing the TransactionOptions object
TransactionOptions options = new TransactionOptions();
//Setting the Isolation level of both transactions to Read Committed
options.IsolationLevel =System.Transactions.IsolationLevel.ReadCommitted;
using(TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, options)){
//Creating a connection to a data source
using (SqlConnection cn = new SqlConnection("Initial Catalog=ABC;Data Source=SQLSERVER01;User id=sa;Password=Demo#1234;")){
cn.Open();
//Creating a SqlCommand object to insert a record in the Department table
using(SqlCommand cmd = new SqlCommand("INSERT INTO Department(cDepartmentCode ,vDepartmentName,vDepartmentHead,vLocation) VALUES(2016,'IT','Lara King','Houston')", cn)){
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated > 0)
{
//Creating another connection to the same data source
using (SqlConnection cn1 = new SqlConnection("Initial Catalog=ABC;Data Source=SQLSERVER01;User id=sa;Password=Demo#1234"))
{
cn1.Open();
//Creating another command object  to insert a record in the Department table
using(SqlCommand cmd = new SqlCommand("INSERT INTO Department(cUserName,cPassword) VALUES('Jeniffer','kelivn123')", cn1))
{
int rowsUpdated1 = cmd1.ExecuteNonQuery();
if (rowsUpdated1 > 0)
{
//Calling the Complete() method to commit both transactions
ts.Complete();
}}}}

Summary :
a)      A transaction is a logical unit of work that must be completed to maintain the consistency and integrity of a database.
b)      Transaction group database commands so that they success or fail in all-or-nothing fashion. This ensures that changes are not partially made, thus preserving the integrity of the database.
c)       A transaction has the following properties:
i)        Atomicity
ii)       Consistency
iii)     Isolation
iv)     Durability
d)      The two types of transaction are:
i)        Local transactions
ii)       Distributed transactions
e)      A local transaction performs on a single data source. The IDbTransaction interface contains methods for creating and performing local transactions against a data source.
f)       To use a transaction , follow these steps:
i)        Begin the transaction
ii)       Perform command and make the change that are part of transaction
iii)     Check for errors
iv)     If error occurred ,undo(roll back) the change.If no error occurred, commit the changes. This ends the Transaction.
g)      A distributed transaction performs on multiple data sources. A distributed transaction enables you to incorporate several distinct transactional operations into an atomic unit that either succeed or fail completely.
h)      Bulk copy operations can be performed as an isolated operations or as a part of a transaction. By default, a bulk copy operation is its own transaction.
i)        An isolation level determines the effect a transaction has on other transactions that are currently running, and vice versa.
j)        The various concurrency errors that can occur when multiple transactions access the same data at the same time are:
i)        Dirty read
ii)       Nonrepeatable read
iii)     Phantom read
k)      The various types of isolation levels are for a transaction are:
i)        Read Uncommitted
ii)       Read Committed with Locks
iii)     Read Committed with Snapshots
iv)     Repeatable Read
v)      Serializable
l)        Manage data set transaction using Update method to commit changes and the RejectChanges method to Undo ( or roll back) changes.
m)    Manage database transaction using the transaction object’s Commit and Rollback methods.


I hope that the functionality of the above attribute is clear to you now. Now you can use this code in your application. If you have further question, just drop a line below and I will try to answer you as soon as possible.

Last but not least, connect with me on Twitter , Facebook , LinkedIn and Google+ for technical updates and articles news. We won’t spam or share your email address as we respect your privacy.



No comments:

Post a Comment