?
Solved

Catch ExecuteReader() Exceptions

Posted on 2006-06-21
5
Medium Priority
?
1,333 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
Question by:Brian Bush
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
dstanley9 earned 2000 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:Brian Bush
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:Brian Bush
ID: 16963241
That makes sense.
I am basically doing just that now.
Thanks,
--brian
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

752 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