Working with Transactions in ADO.NET

A transaction is a group of operations combined into a logical unit of work that is either guaranteed to be executed as a whole or rolled back. Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing is an indispensible part of ADO.NET. It guarantees that a block of statements will either be executed in its entirety or rolled back,( i.e., none of the statements will be executed). Transaction processing has improved a lot in ADO.NET 2.0. This article discusses how we can work with transactions in both ADO.NET 1.1 and 2.0 versions.

Implementing Transactions in ADO.NET

Note that in ADO.NET, the transactions are started by calling the BeginTransaction method of the connection class. This method returns an object of type SqlTransaction. Other ADO.NET connection classes like OleDbConnection, OracleConnection also have similar methods. Once you are done executing the necessary statements within the transaction unit/block, make a call to the Commit method of the given SqlTransaction object, or you can roll back the transaction using the Rollback method, depending on your requirements (if any error occurs when the transaction unit/block was executed).
To work with transactions in ADO.NET, you require an open connection instance and a transaction instance. Then you need to invoke the necessary methods as stated later in this article. Transactions are supported in ADO.NET by the SqlTransaction class that belongs to the System.Data.SqlClient namespace.
The two main properties of this class are as follows:

Connection: This indicates the SqlConnection instance that the transaction instance is associated with
IsolationLevel: This specifies the IsolationLevel of the transaction
The following are the methods of this class that are noteworthy:
Commit() This method is called to commit the transaction
Rollback() This method can be invoked to roll back a transaction. Note that a transaction can only be rolled back after it has been committed.
Save() This method creates a save point in the transaction. This save point can be used to rollback a portion of the transaction at a later point in time. The following are the steps to implement transaction processing in ADO.NET.

Connect to the database
Create a SqlCommand instance with the necessary parameters
Open the database connection using the connection instance
Call the BeginTransaction method of the Connection object to mark the beginning of the transaction
Execute the sql statements using the command instance
Call the Commit method of the Transaction object to complete the
transaction, or the Rollback method to cancel or abort the transaction
Close the connection to the database




The following code snippet shows how we can implement transaction processing using ADO.NET in our applications.

string connectionString = ...; //Some connection string
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Transaction = sqlTransaction;

try
{
sqlCommand.CommandText = "Insert into Employee (EmpCode, EmpName) VALUES (1, 'Joydip')";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Insert into Dept (DeptCode, DeptName, EmpCode) VALUES (9, 'Software', 1)";
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
//Usual code
}

catch(Exception e)
{
sqlTransaction.Rollback();
//Usual code
}

finally
{
sqlConnection.Close();
}

The next piece of code illustrates how we can use the “using” statement for the above code. According to MSDN, the "using" statement, "defines a scope, outside of which an object or objects will be disposed. A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement".

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
sqlConnection.Open();
transaction = sqlConnection.BeginTransaction();

command.Transaction = transaction;

command.CommandText = "Insert into employee (empID, empName) values (1, 'Joydip');
command.ExecuteNonQuery();

command.CommandText = "Insert into dept (deptID,deptName,empID) values (9,'Software',1)";
command.ExecuteNonQuery();

transaction.Commit();
}
catch(Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
sqlConnection.Close();
}
}



The Microsoft's ADO.NET version 2.0 added a lot of new features to its earlier counterpart to add moer flexibility and ease of use. As far as transactions are concerned, a new namespace called System.Transactions has been introduced that promises a significantly improved support for distributed transactions. It contains a class called TransactionScope that can run a set of statements. It can also determine whether the objects in the scope have support for transactions. If the transaction has completed successfully, the changes are committed to the database else it is rolled back. We need to specify whether the transaction block is complete by making a call to the TransactionScope.Complete method explicitly, else, the transaction would be rolled back when the transaction instance would be discarded by the implicit Dispose method.
The following piece of code illustrates what we have learnt so far in this section.

bool IsConsistent = false;
using (System.Transactions.TransactionScope transactionScope = new System.Transactions.TransactionScope())
{
SqlConnection sqlConnection = newSqlConnection(connectionString);
string sqlString = "Update emp set empName = 'Joydip Kanjilal' where empID = 9";
SqlCommand cmd1 = newSqlCommand(sql, cn);
sqlConnection.Open();
cmd1.ExecuteNonQuery();
sqlConnection.Close();
transactionScope.Consistent = IsConsistent;
}

TransactionScope also has support for distributed transactions. We can implement transactions for multiple database connections using it. The following piece of code shows how we can implement transactional support for multiple databases using the TransactionScope class.

using (TransactionScope transactionScope = new TransactionScope())
{
using (SqlConnection codesDatabaseConnection = new SqlConnection(codesDatabaseConnectionString))
{
SqlCommand sqlCommandCodes = codesDatabaseConnection.CreateCommand();
sqlCommandCodes.CommandText = "Insert Into codes (codeID,codeText) values (1,'Test')";
codesDatabaseConnection.Open();
sqlCommandCodes.ExecuteNonQuery();
codesDatabaseConnection.Close();
}

using (SqlConnection statesDatabaseConnection = new SqlConnection(statesDatabaseConnectionString))
{
SqlCommand sqlCommandStates = statesDatabaseConnection.CreateCommand();
sqlCommandStates.CommandText = "Insert into States(stateID,stateName) values (1, 'Test')";
codesDatabaseConnection.Open();
sqlCommandStates.ExecuteNonQuery();
statesDatabaseConnection.Close();
}

transactionScope.Complete();
}


Points to be noted

It should be noted that the SqlTransaction object returned by the BeginTransaction () method has to be assigned to the Transaction property of the Command object; else an InvalidOperationException will be thrown by the application when the first query is executed. Likewise, the Connection instance should be open by invoking the Open method on it prior to starting a new transaction; else an InvalidOperationException would be thrown. In order to improve the performance of applications, we should try to keep the transactions (the transaction units/blocks that contain the statements to be executed in a batch as a whole) as short as possible. This will help minimize the lock contention and hence increase throughput. Further, we should analyze whether or not we actually require a transaction for a batch of statements. Try not to unnecessarily have transactional statements in you code as it might have a performance drawback due to the reasons stated above

Ways for online privacy....

REUTERS - Here are 10 ways to keep personal information secure when online:
1) Favour common sense over technological solutions. Keep personal documents safe, preferably in a locked drawer. Shred bank statements, credit card slips and bills before throwing them away.
2) If it's too good to be true, it is. Never open unsolicited "spam" messages. Delete e-mails offering cash, free gifts or stock tips. Millions of unsolicited "spam" messages are sent every day in an attempt to defraud computer users.
3) Basic prevention helps. Protect your computer against identity theft. Install security software to combat viruses, spyware and spam and keep it updated.
4) Know enough about your firewall, the barrier between the public Internet and a personal computer, to know when it is working and when it isn't. Don't worry about the geeky complexity of it all, just know it's operating.
5) Beware of "phishing", where criminals trick people into revealing personal or financial details, often by sending e-mails purporting to be from a bank. Never casually reply to requests for your personal financial details.
6) Keep your private e-mail address secure. Consider using different e-mail accounts for shopping, banking, friends and work. There are many free account providers.
7) Do not use the same password for different sites. Choose passwords with a mix of letters, numbers and symbols. Don't use obvious passwords, such as your first name or "123456" and don't write them down. For memory's sake, choosing a basic root word, then rotating numbers, is safer.
8) Make online payments safely. Never enter a card number unless there is a padlock in the Web browser's frame, rather than the Web page. The Web address should begin with "https" -- the extra "S" stands for "secure". Consider reserving one credit card for Web use or signing up for a separate online payment service like PayPal.
9) Secure your wireless network at home and be wary when using public access points. Encrypt the connection to scramble communications over the network. Turn off the wireless network when you're not using it.
10) Treat your laptop computer like cash -- never leave it in a locked car or turn your back while using it in a public place. The same holds true with your mobile phone: Lock your phone (and any passwords you keep on it) when not using it.
For more on privacy, see http://uk.reuters.com/news/globalcoverage/onlinePrivacy
(Sources: Britain's Home Office (interior ministry); Get Safe Online, a government-backed British Internet security advice Web site; OnGuardOnline.gov, a site run by the U.S. Federal Trade Commission; www.diyonlinesecurity.co.uk)

10 Commandments for Success..!!

Here are ten points gleaned from the writings of Sri Swami Sivananda, which can lead any individual towards success in their chosen path in life. These guidelines have the power to benefit you - physically, mentally and spiritually.
1. Have a simple and unassuming manner of life.
2. Live not to eat, but eat to live.
3. Bear no envy. Commit no slander. Speak no falsehood. Practise no deceit. Harbor no malice. You will be ever joyful, happy and peaceful.
4. Righteousness is the rule of life. Lead a virtuous life. Strictly adhere to Dharma. Human life is not human without virtues. Study the lives of saints and draw inspiration from them.
5. Cultivate a melting heart, the giving hand, the kindly speech, the life of service, equal vision, and impartial attitude.
Your life will, indeed, be blessed.
6. Lead a regulated life. Take hold of each day as if it were the last day, and utilize every second in prayer, meditation and service. Let your life become a continuous sacrifice to God.
7. Live in the present. Forget the past. Give up hopes of the future.
8. Understand well the meaning of life, and then start the quest.
9. Life is thy greatest gift. Utilize every second profitably.
10. Success often comes to those who dare and act. It seldom comes to the timid.
May you all attain success in life!

- Swami Sivananda--

INTEGRITY THROUGH TRUST AND CARING

When I was a freshman at College, I needed to send my oboe through the mail to have some repair work done. I was really worried about mailing it; my first oboe teacher had sold the instrument to me, and I didn’t want it to be damaged. A friend told me that I could insure it through UPS, and directed me to a small neighborhood business called “Touchette” that did UPS insurance. I took the bus downtown, oboe in my lap, and when I arrived at the store the proprietor was very kind and reassuring. He helped me package the oboe as well as possible, and was able to insure it. However, the total cost was $30, and I had only brought $20 with me. My heart sank at the thought of squeezing another bus ride into my hectic schedule when he said. “That’s OK, I trust you. Just give me your name and number, and pay me the rest when you get the chance.” I was astounded. Who had heard of a business owner just volunteering to trust someone like that? That act of kindness stuck in my memory and still reminds me that integrity is built through trust and caring for one another’s needs.

Refactoring your code with VS.NET 2005

Introduction
There are developers who write code and then there are developers who write better code. The difference is obvious. Writing good code is skill that not all developers have. This also implies that this skill can be improved. A part of this skill includes ability to refactor the code that you or somebody else has written. VS.NET 2005 includes a set of features that help you in this regards. This article is going to discuss what refactoring is and how VS.NET 2005 helps you to refactor your code.
What is refactoring?
As a developer all of us write code in one or the other language. Often the code we write is not the best piece of software in terms of its organization, arrangement, readability and structure. When we (or somebody else) review our own code we realize that it needs to be improved or even rewritten at certain places. This process of improving our code is called as refactoring. Note that the term refactoring implies that we are improving the code without changing its intended functionality or meaning.
Some of the common refactoring tasks include:
Changing signature of methods
Renaming variables
Creating interfaces out of existing code
Convert a block of code into a separate method
Refactor menu
Now comes the interesting part. VS.NET 2005 includes a menu called "Refactor" that helps you to perform many of the common refactoring tasks. The following figure shows the Refactor menu and its items.
Renaming variables, properties and methods
Many times it happens that after writing huge amount of code you feel like changing names of variables, properties and methods involved. Common reason being improved readability. The traditional approach is to use "Find and Replace" option from edit menu. Unfortunately our code is not a work document and invoking "Find and Replace" may not prove useful. Moreover chances of accidentally replacing unintended tokens are high (recollect how often you used Ctrl+Z after that mighty Replace All command). The Rename menu option from the Refactor menu helps you do this task easily.
You need to go the variable or property or method name that you intend to change and select "Rename" menu option. This will open a dialog as shown below:
You can enter the new name for the variable, property or method and click OK to change it everywhere. You can also preview the changes before you apply them. The following figure shows the preview dialog. See how you can uncheck the locations where change is going to occur.
Additionally you can control if you want to change the name in comments and string literals.
Extracting a method
Consider a typical scenario. You start coding a lengthy method consisting of some complex sets of algorithms. After completing the method you realize it has become huge and complex enough that other team members can not understand it with ease. Hence, you decide to break it down in multiple small functions. This not only simplifies your code but also improves its readability and maintainability. The "Extract Method" option of Refactor menu does the job for you.
In order to use this menu option you must select a block of code that you want to separate out as a method and then click on the "Extract Method" menu option. The following figure shows the extract method dialog.
Once you enter the name of the new method to be created and click OK it does two things for you:
It creates a new method as per your choice and places all the selected code inside it
It replaces the selected lines with a call to this newly created method
Creating properties that wrap class level variables
Let's accept the fact that many developers have habit of exposing class level variables (fields) to the external world. As per object oriented programming one should allow access to variables only through properties or methods. Such circumstances can be dealt with the help of "Encapsulate Field" option of Refactor menu.
Select the class level variable that you want to wrap inside a property and choose the "Encapsulate Field" option. This will open a dialog as shown below:
You need to enter the property name and decide if you want to update the references to the variable from outside the class or from outside as well as inside of the class. Just like Rename dialog you can preview the changes before applying.
Extracting interfaces
Sometime it happens that after coding a class you realize that there are going to be other classes bearing similar structure but different implementation. This is a perfect case for interfaces and "Extract Interface" option from Refactor menu does the job for you.
You need to be in the class and select "Extract Interface" to pop up a dialog as shown below:
You need to specify the name of the interface, the file name in which to store the interface and the members to be included in the interface. Clicking on OK creates a new file and creates an interface within in. Also, the class from which you extracted the interface is automatically marked to be implementing the interface.
Promoting local variables to method parameters
Another common scenario is converting local variables from a method as method parameters. In this case "Promote local variable to parameter" option of the Refactor menu comes handy. Simply place your custom at the line where you have declared the variable and click on this option. You fill find that the variable now appears as a method parameter and all the calls to the method get updated accordingly. Note that in order for this option to work it is necessary to initialize the variable to some value.
Removing method parameters
They way you may need to promote local variables as method parameters, you may also need to remove some or all method parameters (changes are always unexpected you know). In such cases "Remove Parameters" option from Refactor menu comes handy.
You need to be inside the method whose parameters are to be removed and select "Remove Parameters" option. This will bring a dialog as shown below:
You can then remove the required parameters. The good part is that all the method calls as well as interface definitions will also be updated according to the new signature. As before you can preview changes before applying.
Reorder method parameters
Adding method parameters, removing them is fine. But what about re-ordering them? Don't worry. There is "Reorder Parameters" option that does just that. This option works only for methods with two or more parameters. When selected it opens a dialog as shown below:
You can move the parameters up or down and see the preview as before. As with remove parameter option all the method calls automatically get updated as per the new order of parameters.
Summary
Refactoring is important and common requirement in many development scenarios. VS.NET 2005 provides necessary tools to quickly refactor your code and improve its overall organization, structure and readability