How do I return an IEnumerable<> using ADO.NET?

Question

How do I return an IEnumerable<> using ADO.NET?

ado.net c# dapper

Question

I've been using Dapper and with my current project I'm going to have to use ADO.NET. My question is how do I return an IEnumerable using ADO.NET? Here is what I have using Dapper. Can someone help me with converting this to do the same but with ADO?

public IEnumerable<Favorites> GetFavorites()
{
    using (SqlConnection sqlConnection = new SqlConnection(connString))
    {
        sqlConnection.Open();
        var work = sqlConnection.Query<Favorites>("Select * from favorites");
        return work;
    }
}

ironman

Popular Answer

If you can do you work all within the using block, you can take advantage of LINQ and the IEnumerable interface on DbDataReader. It provides self-contained pure ADO.NET with a reasonably small amount of code.

var command = connection.CreateCommand();
command.CommandText = "select * from favorites";
using (var reader = command.ExecuteReader()) {
    var work = from IDataRecord r in select ...;
    ... use work ...
}

Edward Brey

View more on Stack Overflow

Steve Czetty · Accepted Answer · 7/24/2012 3:38:29 PM

You can use yield return like so:

public IEnumerable<Favorites> GetFavorites()
{
    using (SqlConnection sqlConnection = new SqlConnection(connString))
    {
        sqlConnection.Open();
        using (SqlCommand cmd = sqlConnection.CreateCommand())
        {
            cmd.CommandText = "Select * from favorites";
            cmd.CommandType = CommandType.Text;
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // Create a Favorites instance
                    var favorites = new Favorites();
                    favorites.Foo = reader["foo"];
                    // ... etc ...
                    yield return favorites;
                }
            }
        }
    }
}

Obviously, you can refactor this by creating a Favorites constructor that accepts an IDataReader or SqlDataReader, or creating a utility method to populate the values, or whatever, but this is the basic structure.

How do I return an IEnumerable<> using ADO.NET?

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator