Solved

Catch ExecuteReader() Exceptions

Posted on 2006-06-21
5
1,309 Views
Last Modified: 2012-05-05
I use stored procedures a lot. I have been trying to figure out how to effectively wrap their execution in a try/catch block.
This is the typical scenario I use:

SqlConnection _connection = new SqlConnection(MyDatabaseConnectionString);
_connection.Open();

SqlCommand _command = new SqlCommand("MyStoredProcedure", _connection);
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.Add("@parameter", "MyParameterValue");

SqlDataReader _results = _command.ExecuteReader();
try {
      while (_results.Read()) {
            // Do something with the results.
      }
}
catch (Exception _ex) {
      Console.WriteLine(_ex.ToString());
      _results.Close();
}
finally {
      if (!_results.IsClosed) {
            _results.Close();
      }
      _connection.Close();
}

This works most of the time. The problem comes in when ExecuteReader() throws an Exception. If I drop it in the try/catch, how can I make sure that the SqlDataReader gets closed?

--brian
0
Comment
  • 2
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
dstanley9 earned 500 total points
ID: 16954700
Use a using block:

using (SqlDataReader _results = _command.ExecuteReader())
{
try {
     while (_results.Read()) {
          // Do something with the results.
     }
}
catch (Exception _ex) {
     Console.WriteLine(_ex.ToString());
     _results.Close();
}
finally {
     _connection.Close();
}
}

That way, the Reader is automatically disposed even if an exception occurs
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16958008
try
{
      SqlConnection _connection = new SqlConnection(MyDatabaseConnectionString);
      _connection.Open();

      SqlCommand _command = new SqlCommand("MyStoredProcedure", _connection);
      _command.CommandType = CommandType.StoredProcedure;
      _command.Parameters.Add("@parameter", "MyParameterValue");

      SqlDataReader _results = _command.ExecuteReader();
           while (_results.Read())
          {
                // Do something with the results.
          }
}
catch (Exception _ex)
{
              Console.WriteLine(_ex.ToString());
}
finally
{
           _results.Close();  
           _connection.Close();
}
0
 
LVL 6

Author Comment

by:theonlygoodisknowledge
ID: 16963152
dstanley9, my concern is what if something happens during the ExecuteReader() function call?
The exception will not be caught.

pradeep, neither _results, nor _connection are in scope for the finally block, because they were
declared in the try block.

--brian
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 16963197
Your initial concern was about closing the reader if an exception is thrown.  If an exception is thrown in ExecuteReader(), there is no reader to close, so you're OK.  If it's thrown within the using block, it will automatically be disposed (which closes it), so you're OK.  If you want to catch the exception, put the using block inside a try/catch block
0
 
LVL 6

Author Comment

by:theonlygoodisknowledge
ID: 16963241
That makes sense.
I am basically doing just that now.
Thanks,
--brian
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question