Cant get Exceptions & Dispose() to work in this method

I have the following function which reads from a firebird database. The Function works but does not handle exceptions (Required).

public IEnumerable<DbDataRecord> ExecuteQuery(string Query)
{
    var FBC = new FbCommand(Query, DBConnection);

    using (FbDataReader DBReader = FBC.ExecuteReader())
    {
        foreach (DbDataRecord record in DBReader)
            yield return record;
    }
}

Adding try/catch to this function gives an error regarding yield. I understand why I get the error but any workround I've tried has resulted in DBReader being disposed indirectly via using() too early or Dispose() not being called all. How do I get this code to use Exceptions & Cleanup without having to wrap the method or duplicate DBReader which might contain several thousand record?

Update:

Here is an example of an attempted fix. In this case DBReader is being disposed too early.

    public IEn开发者_开发知识库umerable<DbDataRecord> ExecuteQuery(string Query)
    {   
        var FBC = new FbCommand(Query, DBConnection);

        FbDataReader DBReader = null;

        try
        {
            using (DBReader = FBC.ExecuteReader());
        }
        catch (Exception e)
        {
            Log.ErrorException("Database Execute Reader Exception", e);
            throw;
        }

        foreach (DbDataRecord record in DBReader) <<- DBReader is closed at this stage
            yield return record;

    }

The code you've got looks fine to me (except I'd use braces round the yield return as well, and change the variable names to fit in with .NET naming conventions :)

The Dispose method will only be called on the reader if:

• Accessing MoveNext() or Current in the reader throws an exception
• The code using the iterator calls dispose on it

Note that a foreach statement calls Dispose on the iterator automatically, so if you wrote:

foreach (DbDataRecord record in ExecuteQuery())
{
    if (someCondition)
    {
        break;
    }
}

then that will call Dispose on the iterator at the end of the block, which will then call Dispose on the FbDataReader. In other words, it should all be working as intended.

If you need to add exception handling within the method, you would need to do something like:

using (FbDataReader DBReader = FBC.ExecuteReader())
{
    using (var iterator = DBReader.GetEnumerator())
    {
        while (true)
        {
            DbDataRecord record = null;
            try
            {
                if (!iterator.MoveNext())
                {
                    break;
                }
                record = iterator.Current;
            }
            catch (FbException e)
            {
                // Handle however you want to handle it
            }
            yield return record;
        }
    }
}

Personally I'd handle the exception at the higher level though...

This line won't work, note the ; at the end, it is the entire scope of the using()

    try
    {
        using (DBReader = FBC.ExecuteReader())
          ;  // this empty statement is the scope of using()
     }

The following would be the correct syntax except that you can't yield from a try/catch:

    // not working
    try
    {
        using (DBReader = FBC.ExecuteReader())
        {
          foreach (DbDataRecord record in DBReader) 
             yield return record;
        }
    }
    catch (Exception e)
    {
        Log.ErrorException("Database Execute Reader Exception", e);
        throw;
    }

But you can stay a little closer to your original code:

    // untested, ought to work
    FbDataReader DBReader = null;

    try
    {
        DBReader = FBC.ExecuteReader();
    }
    catch (Exception e)
    {
        Log.ErrorException("Database Execute Reader Exception", e);
        throw;
    }

    using (DBReader)
    {
      foreach (DbDataRecord record in DBReader) // errors here won't be logged
        yield return record;  
    }

To catch errors from the read loop as well see Jon Skeet's answer.