SqlDataReader to IEnumerable

Question

I'm trying to write a method that retrieves a collection of type T for a query that returns a single column of values.

What I've tried so far:

public static IEnumerable<T> ExecuteReader<T>(string query)
{
    using (SqlConnection cn = new SqlConnection(conn.ConnectionString))
    {
        cn.Open();
        using (SqlCommand cmd = new SqlCommand(query, cn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
               while (reader.Read()) 
               { 
                   yield return (T)reader[0]; 
               }
            }
        }
    }

Sample usage:

foreach (var dbname in ExecuteReader<string>("SELECT Name FROM sys.databases"))
{
    //iterate db names
}

Problem is now I'd like to add error handling to the code with try/catch, something like:

try
{
    //my code
}
catch
{
   //Error handling code
   return Enumerable.Empty<T>();
}

However yield return is not compatible with try/catch.

I've seen some alternatives on the web such as writing an extension for the IDataReader class but I feel like they could be outdated, so I'm open to suggestions or better ways to achieve this.

EDIT:

I feel like the answers are deviating from the question itself, maybe I didnt make myself clear. the try-catch or the yield are not relevant to me, this is simply a minor obstacle I found after writing my code.

What I wanted to know is if this is the right way of populating a list from a datareader where a single column of generic type is retrieved.

Note that `try {} catch { return Enumerable.Empty()}` is _not_ error handling. — Evk, Apr 05 '17 at 12:02
@Evk that was merely an example, I also want to register the error messages into some logs — Innat3, Apr 05 '17 at 12:05
Why do you want to add the try-catch inside this method? Why not higher up the stack? — jeroenh, Apr 05 '17 at 12:10
@jeroenh because then if I have several parts of my code calling this method I would have to implement the try-catch clause separately for each one of them... or is this the intended usage? — Innat3, Apr 05 '17 at 12:14
I think if you want to keep yield - you have to unwrap usings to try-finally and check exceptions separately. That is - check if cn.Open throws, check if ExecuteReader throws and so on. So that yield statement itself is not inside try-catch. — Evk, Apr 05 '17 at 12:16

score 3 · Accepted Answer · answered Apr 05 '17 at 12:21

You can make this method private and create a public method with the same signature that calls into the private method.

public static IEnumerable<T> ExecuteReader<T>(string query)
{
    try{
        return executeReader<T>(query);
    }
    catch(Exception ex){
        // your handling code here
    }
}

private static IEnumerable<T> executeReader<T>(string query)
{
    // same code as you have above in your example

    using (SqlConnection cn = new SqlConnection(conn.ConnectionString))
    {
        cn.Open();
        using (SqlCommand cmd = new SqlCommand(query, cn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
               while (reader.Read()) 
               { 
                   yield return (T)reader[0]; 
               }
            }
        }
    }
}

Alternatively you could not catch it at all and let the caller worry about the Exception. You could also write the code in such a way that there are many try/catch blocks but the yield is never found in one as @Evk suggested.

yes, this is a valid workaround, I will consider it as a valid answer later if I do not receive more satisfacting alternatives. — Innat3, Apr 05 '17 at 12:24
I just want to add that the point of my question wasn't to understand why yield cannot be used with try-catch, so it is a bit infuriating to get my question flagged as duplicate for that — Innat3, Apr 05 '17 at 12:28
@Innat3 - I just now saw your edit. I think it was not very clear because the last non-code 1/2 of your question focused on the problem with `yield` and `try/catch`. — Igor, Apr 05 '17 at 12:29

SqlDataReader to IEnumerable

1 Answers1