Solved

Catch ExecuteReader() Exceptions

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

Title # Comments Views Activity
C#.net split string and then check appropriate checkboxlist 4 55
need to convert function to c# 5 55
C# Windows app updating JSON file error. 9 49
c#, case, if 4 37
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
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…
Suggested Courses

734 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