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}
this._enumerator = this._sqlDataReader.GetEnumerator();
ReplyDeleteResharper says this line has a possible NullReferenceException on the reader. Should we be catching that too?