Site sponsored by: Idera Try Idera’s new SQL admin toolset
SQL Server Performance

  • Home
  • Articles
  • Forums
  • Tips
  • FAQ's
  • Blogs
  • Software
  • Books
  • About Us
RSS Feeds
Sign in | Join


Article Topics

All Articles
Peformance Tuning
Audit
Business Intelligence
Clustering
Reporting Services
Developer
General DBA
ASP.NET / ADO.NET

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...
Latest Articles

Slowly Changing Dimensions in SQL Server 2005
Audit Data Modifications
SQL Server 2008’s Management Data Warehouse
Same Report but Different Methods in SQL Server Reporting Services ...

More     
 
Latest FAQ's

SSIS Lookups are Case Sensitive
Convert Number to Words in SSRS
After installing SP2 on SQL Server 2005 x64, when trying to ...
Remote Name Could not be Resolved in SQL Server Reporting Services ...

More     
   
Latest Software Reviews

SQL Server DBA Dashboard
SwisSQL DBChangeManager
SQLMesh - SQL Server Search Tool
SoftTreeTech SQL Assistant

More     

articles >> asp.net / ado.net >> Working with Exceptions in ADO.NET

Working with Exceptions in ADO.NET

By : Joydip Kanjilal
Feb 08, 2007
Printer friendly

ADO.NET is a very rich data access technology with a plenty of powerful features - improved performance, an optimized SQL Provider, seamless support for XML and ability to work in connected and disconnected mode, to name a few. Handling exceptions properly is one of the prime concerns when working with any data access technologies. The new version of ADO.NET includes powerful support for working with exceptions efficiently. This article throws light on how to handle exceptions efficiently when working with ADO.NET and highlights the best practices that can be followed in this context.


What are Exceptions?

An exception is an error that occurs at runtime. If it is not handled properly, it terminates the normal flow of the program. According to MSDN, “An exception is any error condition or unexpected behavior encountered by an executing program. Exceptions can be raised because of a fault in your code or in code you call (such as a shared library), unavailable operating system resources, unexpected conditions the common language runtime encounters (such as code that cannot be verified), and so on. Your application can recover from some of these conditions, but not others. While you can recover from most application exceptions, you cannot recover from most runtime exceptions.”

The .NET runtime creates an exception object to represent an exception when it occurs. It also creates an Exception Information Table for each executable. There are four different types of exception handlers, namely

A finally handler
A fault handler
A type-filtered handler
A user-filtered handler

Exceptions in ADO.NET

In ADO.NET 1.1, there was no common base class for ADO.NET exceptions. According to Frans Bouma, "every provider has its own exception for reporting errors, and they're not derived from a generic ADO.NET exception. This thus makes it hard to catch db specific exceptions in generic code and handle it". With ADO.NET 2.0 however, we have a new common exception base class called the System.Data.Common.DbException class. The generic DbException class in ADO.NET 2.0 exposes all the information a provider specific exception would require. This class enables catching the provider specific exceptions in a generic manner.

However, exceptions should be avoided at all cost. Exceptions are expensive; they slow down the application, and use resources, regardless of whether or not they are handled. In addition, "logic by exception" can make debugging very tedious.

Exception Handlers

Exceptions are handled using the try, catch and finally blocks. The try block contains code that raises an exception. The exceptions that are raised in the try block are caught in one or more appropriate catch blocks. Why appropriate? The reason is that if an exception has occurred, only one of the catch blocks would be executed that is most appropriate to the type of the exception that has occurred. The finally block contains any necessary cleanup code. It should be noted that a try block should contain one or more catch blocks or at least a finally block. Why is a finally block required?

A finally block is executed whether or not an exception occurs. Hence, it is particularly useful for cleanup operations. MSDN states, “the purpose of a finally statement is to ensure that the necessary cleanup of objects, usually objects that are holding external resources, happens immediately, even if an exception is thrown”. Let us consider that we have opened a database connection in a try block. If an exception occurs, it is caught in the catch block just after the try block as shown in the code snippet below:

string connectionString = ...; // Some connection string
SqlConnection sqlConnection = null;
try
{
sqlConnection = new SqlConnection(connectionString);
          sqlConnection.Open();
          //Some code
}
 
catch (Exception ex)
{
//Some exception handling code
}
 
finally
{
           sqlConnection.Close();
}

Remember that the connections that are opened as late as possible and released as early as possible. They should be closed immediately we are done using them. Database connections should be closed as soon as you are done using them for efficient connection pooling and hence enhanced performance. Refer to my article on connection pooling and its efficient usage at Understanding Connection Pooling in NET. It is advisable to open the database connections in the try block and close them in the finally block. This would ensure that the connections would be properly closed irrespective of whether an exception has occurred or not. In the code example above, the connection is guaranteed to be closed as we have made a call to the Close() method on the SqlConnection instance in the finally block. I would however prefer to use the “using” statement for handling exceptions when using unmanaged resources. Let me explain what it is and how it can be used.


    Next Page>>    








Home | Peformance Articles | Audit Articles | Business Intelligence Articles | Clustering Articles | Developer Articles | Reporting Services Articles | DBA Articles | ASP.NET / ADO.NET Articles | DBA FAQ's | Developer Peformance FAQ's | DBA Peformance FAQ's | Developer FAQ's | Clustering FAQ's | Error Messages | Audit Tool Reviews | Backup Tool Reviews | Coding Tool Reviews | Compare Tool Reviews | Documentation Tool Reviews | Design Tool Reviews | Monitoring Tool Reviews | Log Tool Reviews | Reporting Tool Reviews | Clustering Tool Reviews | Security Tool Reviews | Change Management Tool Reviews | Remote Access Tool Reviews | Book Reviews | Security Tool Reviews | QDPMA Performance Tuning | ADO.NET / ASP.NET | Administration | Analysis/OLAP Services | Application Development | Configuration | Components | ETL | Hardware | High Availability | Hints | Index | Misc | Operating Systems | Performance Tuning | Replication | T-SQL | Views