Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Catch ExecuteReader() Exceptions

Posted on 2006-06-21
5
Medium Priority
?
1,343 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
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.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

596 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