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

Working with Exceptions in ADO.NET

By : Joydip Kanjilal Feb 08, 2007

Page 2 / 2

Using the “using” statement

The using statement can be used to specify a boundary for the object outside of which, the object is destroyed automatically. The runtime invokes the Dispose method of the objects that are specified within this statement when the control comes out of this block. This is why this is a preferred choice when using exceptions for managing resources in .NET. Refer to the following code that uses the “using” statement:

string connectionString = ...; // Some connection string

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
  sqlConnection.Open();
  //Some code
}

Note that when the end of the using block would be encountered, the Dispose () method will be immediately called on the instance. Note that when the Dispose() method is called on this connection instance, it checks to see if the connection is opened; if open it would close it implicitly prior to disposing off the instance. Please refer to my article at When and How to Use Dispose and Finalize in C# on Dispose and Finalize for more information on when and how to use them appropriately.

The above code gets translated implicitly to:

string connectionString = ...; // Some connection string

SqlConnection sqlConnection = new SqlConnection(connectionString));

try
{
  sqlConnection.Open();
  //Some code
}

finally
{
   ((IDispose)sqlConnection).Dispose();
}

Remember to keep the try block as short as possible. Note that in the code example above, we have just opened the connection in the try block. Do not use any unnecessary code/logic in the try block that is not supposed to throw any exception. Do not catch any exception that you cannot handle and avoid rethrowing exceptions unnecessarily as it is very expensive.  

 

Prevent unnecessary database hit

One of the most useful of all features of ADO.NET is that you can attach messages to each row of data in a DataSet object. The SqlDataAdapter class attaches error messages to the rows of a DataSet if a specific database action has not been successfully completed. We can then check whether there are any errors in a DataSet prior to sending the same for updating the database using the HasErrors property of the DataSet instance. This, if used judiciously, can prevent an unnecessary database hit. Please refer to the code snippet that follows:

DataSet newDataSet = previousDataSet.GetChanges(DataRowState.Modified);

    if (newDataSet.HasErrors)
    {
      // If there are errors take appropriate action   
    }

    else
    {

      // Necessary code to update the database
    }

References

Please refer to the following links for more information on this topic:

Exceptions Overview (MSDN)
Exceptions in ADO.Net 2.0
Exception Handling in C#
Working with Exceptions using C#

Conclusion

Exceptions should be used appropriately in ADO.NET and only used when required -- in exceptional cases. Even if they come in handy, exceptions tend to slow down the execution speed of the program and consume more resources. In this article, I have examined how to make proper use of exceptions when working with ADO.NET.