Best method to use IDataReader as IEnumerable<T>?
I need to use Linq on any IDataReader implementations like this
var c = sqlDataReader.AsEnumerable().Count();
Example:
public abstract class Test
{
public abstract Sql开发者_如何学CDataReader GetSqlDataReader();
public void Foo()
{
SqlDataReader sqlDataReader = GetSqlDataReader();
IEnumerable<SqlDataReader> sqlEnumerable = sqlDataReader.AsEnumerable();
var c = sqlEnumerable.Count();
var s = sqlEnumerable.Sum();
SqlDataReader first = sqlEnumerable.First();
var t = first.GetSqlXml(10);
}
}
What is the best way to write this. Please, write your snippet.
You can use this:
MyDataReader.Cast<IDataRecord>()
But don't forget to have the linq statement execute before you close the DataReader.
using ToList() for instance
Try, this:
public static class DataReaderExtension
{
public class EnumeratorWrapper<T>
{
private readonly Func<bool> moveNext;
private readonly Func<T> current;
public EnumeratorWrapper(Func<bool> moveNext, Func<T> current)
{
this.moveNext = moveNext;
this.current = current;
}
public EnumeratorWrapper<T> GetEnumerator()
{
return this;
}
public bool MoveNext()
{
return moveNext();
}
public T Current
{
get { return current(); }
}
}
private static IEnumerable<T> BuildEnumerable<T>(
Func<bool> moveNext, Func<T> current)
{
var po = new EnumeratorWrapper<T>(moveNext, current);
foreach (var s in po)
yield return s;
}
public static IEnumerable<T> AsEnumerable<T>(this T source) where T : IDataReader
{
return BuildEnumerable(source.Read, () => source);
}
}
You could create an extension method to do this (see caveats below):
public static class DataReaderExtension
{
public static IEnumerable<Object[]> AsEnumerable(this System.Data.IDataReader source)
{
if (source == null)
throw new ArgumentNullException("source");
while (source.Read())
{
Object[] row = new Object[source.FieldCount];
source.GetValues(row);
yield return row;
}
}
}
Found here: http://www.thinqlinq.com/default/Consuming-a-DataReader-with-LINQ.aspx
As pointed out by @LukeH, note that as IDataReader only supports reading once, forwards, you'll only be able to query the enumerable once. (To get round this you could call ToList/ToArray, then query that).
Note that SqlDataReader
already impliments IEnumerable so you won't need to do this in the example you've given.
Also, be aware that it's probably better to do any filtering/aggrigating on the server (via LINQ to SQL for example)
Here are my two cents :
public static IEnumerable<T> Enumerate<T>(this T reader) where T: IDataReader
{
using(reader)
while(reader.Read())
yield return reader;
}
public void Test()
{
var Res =
from Dr in MyDataReader.Enumerate()
select new {
ID = (Guid)Dr["ID"],
Description = Dr["Desc"] as string
};
}
I felt the urge to post this, because it is very important to dispose a DataReader
after use, and no answer mentioned it.
That's why my implementation has a using
statement around the while
loop. In this way, I can do "one hand" queries whithout worrying about the DataReader
disposal.
You can just load the DataReader
into a DataTable
and then Select()
:
DataTable dt = new DataTable();
dt.Load(dataReader);
DataRow[] rows = dt.Select(); //DataRow[] Implements IEnumerable
or
IEnumerable<DataRow> rows = dt.AsEnumerable();
I've used the following but I like @Serge's suggestion better and it reminds be of what I believe I used to do but then forgot that IDataReader implements
var reader = command.ExecuteReader();
var records = Enumerable
.Range(0, int.MaxValue)
.TakeWhile(i => reader.Read())
.Select(i => reader as IDataRecord);
Today I implemented the following generic solution as we don't like to depend on any third party dependencies or any complex solution.
Simple and Fast IDataReader
to IEnumerable<T>
conversion using reflection + lambda expressions
public IEnumerable<T> ConvertToEnumerable<T>(SqlDataReader dr) where T : class, new()
{
List<string> lstColumns = Enumerable.Range(0, dr.FieldCount).Select(dr.GetName).ToList();
List<PropertyInfo> lstProperties = typeof(T).GetProperties().Where(x => lstColumns.Contains(x.Name, StringComparer.OrdinalIgnoreCase)).ToList();
while (dr.Read())
{
var entity = new T();
lstProperties.Where(w => dr[w.Name] != System.DBNull.Value).ToList().ForEach(i => i.SetValue(entity, dr[i.Name], null));
yield return entity;
}
}
Usages
SqlDataReader dr;
...
var result = ConvertToEnumerable<Foo>(dr).FirstOrDefault();
Or
var result = ConvertToEnumerable<Foo>(dr).ToList();
精彩评论