Returns column values ​​as IEnumerable

Question

This code works for me:

public IEnumerable<string> GetEmpNames()
{
    var cmd = SqlCommand("select [EmpName] from [dbo].[Emp]");
    using (var rdr = cmd.ExecuteReader())
        while (rdr.Read())
            yield return (string) rdr["EmpName"];
}

However, I am wondering if there is a better way (LINQish) without resorting to yield return . (And LINQ to SQL is not an option :))

Answer 1

IEnumerable<string> result = DataContext.ExecuteQuery<string>(sqlstring)

http://msdn.microsoft.com/en-us/library/bb361109.aspx

Answer 2

You must not do this! Your reader should be closed as soon as possible. You do not want to keep it open while listing. It’s better to just create an explicit list and return it.

var cmd = SqlCommand("select [EmpName] from [dbo].[Emp]");
List<string> results = new List<string>();
using (var rdr = cmd.ExecuteReader()) {
    while (rdr.Read())
        results.Add((string) rdr["EmpName"]);
}
return results;

You can use Linq expressions for the DataReader by doing it:

using (var rdr = cmd.ExecuteReader()) {
    results = (from row in rdr.Cast<DbDataRecord>()
               select (string)row["EmpName"]).ToList();
}

, ToList(), , .

Edit

, , DataReader . MSDN:

SqlDataReader, SqlConnection SqlDataReader, SqlConnection, . SqlDataReader. , , .

, .