Solved

Catch ExecuteReader() Exceptions

Posted on 2006-06-21
5
1,323 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 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: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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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