What are Database transactions and How to use transactions in ADO.NET
In General Transaction are used in Database or database Batch Programming.But Sometimes we have to Use Transactions from Front-End Coding(Using ADO.net).So in this Article i want to discuss what are Transactions ? when to Use a Transaction ? and How to Use a Transaction Using ADO.Net.These are the things i will discuss here with Code Snippets.
Definition of Transaction :
When you execute Multiple statements such as insert/Update/Delete two or more sql DML statements executed (Not Select) simultaneously/Subsequently at one batch (performed in a single Unit of work).Important Commands of Transactions :
There are two important Commands of Transactions Commit and Rollback Transactions.If all the Statements are executed Correctly than at the end we need to call Commit Command to SAVE the entire Transactions or else any of the executing Statement Fails we need to call Roll back Erase the successful executed statement Results.If you are Using Transactions till you call the commit command the inserted rows are not saved permanently . They can be erase by a rollback command.Scenario to use Transactions in ADO.NET
In General Transactions are Used in Database Programming. But there is no strict rule or provision that Transactions should Use only on Database or SQL batch programming. There are certain scenarios where Transactions have to Use in Front End Programming. Whenever we are Inserting Multiple row values in Database from Front End For Example I have Check box grid where all check boxes are checked we need those insert rows in to Database table with a single Button Click. Insert the row values in Database table each and every grid row item (loop with the help of for or foreach iterators is a very hectic activity for Database and as well as for Asp.Net Application. So for that we need to insert all the data at a time in Sql-server Database. One way to do it with ADO.NET is with SQLBULKCOPYHow to use Transactions in SQLbulkcopy
Now in Asp.net Using SqlBulkCopy is to insert/copy multiple row values in Database at a time. But there is a Possibility that one of the Row may failed to insert in the database table .And we need to stop the insertions of the other rows and revert back already saved rows. So for that we need to Use Transactions.
So How to Use Transactions in SqlBulkCopy we see here in the below code snippet. SqlBulkCopy takes three Arguements in its parameterised Constructor One is Connection two is in sqlBulkCopyOptions and Three is SqlTransactions
Structure of SQLbulkcopy.
sqlBulkCopy bcp = new SqlBulkCopy(
SqlConnection connection,
SqlBulkCopyOptions copyOptions,
SqlTransaction externalTransaction)
Now See the Code.
try
{
sqlConnection con =new SqlConnection(constring);
con.open();
SqlTransaction trans = con.BeginTransaction();
sqlBulkcopy bcp =new sqlBulkCopy(con , SqlBulkCopyOptions.KeepIdentity, trans);
bcp .BatchSize = 10;
bcp.DestinationTable="Tablename";
bulkCopy.WriteToServer(Datatable);
trans.Commit()
}
catch(Exception ex)
{
trans.RollBack();
}
Now we see how to use Transactions for an Inline query.Transactions for a Inline Query Using ADO.NET :
try
{
sqlConnection con =new SqlConnection(constring);
con.open();
SqlTransaction trans = con.BeginTransaction();
string strquery="insert into CreditedAccount(Name,Amount) values("Bhushan",550)";
SqlCommand cmd =new sqlcommand(strquery,con);
cmd.Transaction = trans;
cmd.ExecuteNonquery();
string strquery="insert into DebitedAccount(Name,Amount) values("Bhushan",550)";
SqlCommand cmd =new sqlcommand(strquery,con);
cmd.Transaction = trans;
cmd.ExecuteNonquery();
}
catch(Exception ex)
{
cmd.RollBack();
}
Note : SqlTransaction Class is available in importing the System.Data.SqlClient.
Follow
You can use TransactionScope class for handling Transaction
in ADO.net
e.g. using(TransactionScope Sc = new TransctionScope())
{
//Database operation Here
}
Thanks
Umesh