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;
}
}
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.
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 ...
}