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;
}
}
Accepted Answer
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.
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 ...
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why