Working Around The Yield Restriction In Try/Catch

The code below is a workaround for the restriction that you can’t yield from inside a try/catch statement that contains a catch. Note: you can yield from inside a try/catch statement that only has a finally clause (no catch). What I need to do was:

• Call Microsoft SQL Server and return a result set.
• Support IEnumerable so I could take advantage of SQL Servers forward only cursors and Linq.
• Catch any exceptions of type SQLException and clear the connection pool.

Here is the code I would like to have compile (THIS DOES NOT WORK):

static public IEnumerable<DbDataRecord> Execute(
    String connectionString,
    String statement)
{
    using (SqlConnection sqlConnection =
        new SqlConnection(connectionString))
    {
        try
        {
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand(statement, sqlConnection);

            using (SqlDataReader sqlDataReader =
                sqlCommand.ExecuteReader())
            {
                foreach (DbDataRecord dbDataRecord in
                    sqlDataReader.Cast<DbDataRecord>())
                {
                    yield return (dbDataRecord);
                }
            }
        }
        catch (SqlException)
        {
            SqlConnection.ClearPool(sqlConnection);
            throw;
        }
    }
}

This code throws the compile error:

 

error CS1626: Cannot yield a value in the body of a try block with a catch clause

 

To solve this issue, I implemented another class that supported IDisposable. This allowed me to create the SqlConnection and SqlDataReader as class properties, which I could clean up in Dispose(). Once that was done, the code above was flattened to remove the using statements. This reduced the code to four major statements: the SqlConnection constructor, open, execute and the yield loop. I broke them done into three methods (putting the SqlConnection constructor in the class constructor), wrapping each with a try/catch. However, I still had an issue with yielding inside the try/catch. To solve this issue I called GetEnumerator() and enumerated the SqlDataReader using MoveNext() and Current() to and fetched the DbDataRecord inside the try/catch. Once I have DbDataRecord, I can exit the try/catch and yield. There is a very interesting for loop below that provides context to the yield statement, it is much easier to understand if you walk the code with the debugger.

 

The class follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Threading;
using System.Collections;

namespace BlogLinq
{
    internal class ExecutionContext : IDisposable
    {
        SqlConnection _sqlConnection = null;
        SqlDataReader _sqlDataReader = null;
        IEnumerator _enumerator = null;

        internal static IEnumerable<DbDataRecord> Execute(
            String connectionString,
            String statement)
        {
            using (ExecutionContext exectionContext =
                new ExecutionContext(connectionString))
            {
                exectionContext.Open();
                exectionContext.Execute(statement);

                foreach (DbDataRecord dbDataRecord in exectionContext.Read())
                    yield return dbDataRecord;
            }
        }

        protected ExecutionContext(String connectionString)
        {
            // Create The SqlConnection this doesn't require
            // Reestablished Connection on SqlException
            _sqlConnection = new SqlConnection(connectionString);
        }

        private void Open()
        {
            try
            {
                _sqlConnection.Open();
            }
            catch (SqlException)
            {
                SqlConnection.ClearPool(_sqlConnection);
                throw;
            }
        }

        private void Execute(String statement)
        {
            try
            {
                // Create The Command 
                SqlCommand sqlCommand = new SqlCommand(statement, _sqlConnection);

                // Execute the Command And Get The Reader
                _sqlDataReader = sqlCommand.ExecuteReader();

                // From the Sql Data Reader Get The Enumerator
                _enumerator = _sqlDataReader.GetEnumerator();

            }
            catch (SqlException)
            {
                SqlConnection.ClearPool(_sqlConnection);
                throw;
            }
        }

        private IEnumerable<DbDataRecord> Read()
        {
            // The loop to give yield return  context.
            for (; ; )
            {
                // Always Initalize the output as null this
                // will happen for every fetch since it is instead
                // the yield return loop
                DbDataRecord output = null;

                try
                {
                    // Get the Next Row From the Enumerator
                    if (_enumerator.MoveNext())
                        output = (DbDataRecord)_enumerator.Current;
                }
                catch (SqlException)
                {
                    SqlConnection.ClearPool(_sqlConnection);
                    throw;
                }

                // Now that we are outside The try/catch, determine
                // if we are yielding or their are no more rows.
                if (output != null)
                    yield return output;
                else
                    yield break;
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (_sqlDataReader != null)
                _sqlDataReader.Dispose();

            if (_sqlConnection != null)
                _sqlConnection.Dispose();
        }

        #endregion
    }
}

 

To call this class the statement looks like this:

foreach (DbDataRecord dbDataRecord in ExecutionContext.Execute(
    ConfigurationManager.ConnectionStrings["ColorDatabase"].ConnectionString,
    "SELECT ColorName, CompanyId FROM Colors"))
{
    Console.WriteLine(dbDataRecord["ColorName"]);
}

{6230289B-5BEE-409e-932A-2F01FA407A92}