Get an IDataReader from a typed List
Get the latest version from the code on this post
Nothing like code churn in plain sight: Here is a pretty complete implementation. You can instantiate an IDataReader over IList IEnumerable, IEnumerable (ergo IQueryable). There is no compelling reason to expose a generic type parameter on the reader and by omitting it, I can allow IEnumerable<'a> (anonymous types). See tests.
The source, less xmldocs, is short enough to include here with a couple tests. The rest of the source, with xmldocs, and tests is here under Salient.Data.
using System;
using System.Linq;
using NUnit.Framework;
namespace Salient.Data.Tests
{
[TestFixture]
public class EnumerableDataReaderEFFixture
{
[Test]
public void TestEnumerableDataReaderWithIQueryableOfAnonymousType()
{
var ctx = new NorthwindEntities();
var q =
ctx.Orders.Where(o => o.Customers.CustomerID == "VINET").Select(
o =>
new
{
o.OrderID,
o.OrderDate,
o.Customers.CustomerID,
Total =
o.Order_Details.Sum(
od => od.Quantity*((float) od.UnitPrice - ((float) od.UnitPrice*od.Discount)))
});
var r = new EnumerableDataReader(q);
while (r.Read())
{
var values = new object[4];
r.GetValues(values);
Console.WriteLine("{0} {1} {2} {3}", values);
}
}
}
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using NUnit.Framework;
namespace Salient.Data.Tests
{
public class DataObj
{
public string Name { get; set; }
public int Age { get; set; }
}
[TestFixture]
public class EnumerableDataReaderFixture
{
private static IEnumerable<DataObj> DataSource
{
get
{
return new List<DataObj>
{
new DataObj {Name = "1", Age = 16},
new DataObj {Name = "2", Age = 26},
new DataObj {Name = "3", Age = 36},
new DataObj {Name = "4", Age = 46}
};
}
}
[Test]
public void TestIEnumerableCtor()
{
var r = new EnumerableDataReader(DataSource, typeof(DataObj));
while (r.Read())
{
var values = new object[2];
int count = r.GetValues(values);
Assert.AreEqual(2, count);
values = new object[1];
count = r.GetValues(values);
Assert.AreEqual(1, count);
values = new object[3];
count = r.GetValues(values);
Assert.AreEqual(2, count);
Assert.IsInstanceOf(typeof(string), r.GetValue(0));
Assert.IsInstanceOf(typeof(int), r.GetValue(1));
Console.WriteLine("Name: {0}, Age: {1}", r.GetValue(0), r.GetValue(1));
}
}
[Test]
public void TestIEnumerableOfAnonymousType()
{
// create generic list
Func<Type, IList> toGenericList =
type => (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(new[] { type }));
// create generic list of anonymous type
IList listOfAnonymousType = toGenericList(new { Name = "1", Age = 16 }.GetType());
listOfAnonymousType.Add(new { Name = "1", Age = 16 });
listOfAnonymousType.Add(new { Name = "2", Age = 26 });
listOfAnonymousType.Add(new { Name = "3", Age = 36 });
listOfAnonymousType.Add(new { Name = "4", Age = 46 });
var r = new EnumerableDataReader(listOfAnonymousType);
while (r.Read())
{
var values = new object[2];
int count = r.GetValues(values);
Assert.AreEqual(2, count);
values = new object[1];
count = r.GetValues(values);
Assert.AreEqual(1, count);
values = new object[3];
count = r.GetValues(values);
Assert.AreEqual(2, count);
Assert.IsInstanceOf(typeof(string), r.GetValue(0));
Assert.IsInstanceOf(typeof(int), r.GetValue(1));
Console.WriteLine("Name: {0}, Age: {1}", r.GetValue(0), r.GetValue(1));
}
}
[Test]
public void TestIEnumerableOfTCtor()
{
var r = new EnumerableDataReader(DataSource);
while (r.Read())
{
var values = new object[2];
int count = r.GetValues(values);
Assert.AreEqual(2, count);
values = new object[1];
count = r.GetValues(values);
Assert.AreEqual(1, count);
values = new object[3];
count = r.GetValues(values);
Assert.AreEqual(2, count);
Assert.IsInstanceOf(typeof(string), r.GetValue(0));
Assert.IsInstanceOf(typeof(int), r.GetValue(1));
Console.WriteLine("Name: {0}, Age: {1}", r.GetValue(0), r.GetValue(1));
}
}
// remaining tests omitted for brevity
}
}
/*!
* Project: Salient.Data
* File : EnumerableDataReader.cs
* http://spikes.codeplex.com
*
* Copyright 2010, Sky Sanders
* Dual licensed under the MIT or GPL Version 2 licenses.
* See LICENSE.TXT
* Date: Sat Mar 28 2010
*/
using System;
using System.Collections;
using System.Collections.Generic;
namespace Salient.Data
{
/// <summary>
/// Creates an IDataReader over an instance of IEnumerable<> or IEnumerable.
/// Anonymous type arguments are acceptable.
/// </summary>
public class EnumerableDataReader : ObjectDataReader
{
private readonly IEnumerator _enumerator;
private readonly Type _type;
private object _current;
/// <summary>
/// Create an IDataReader over an instance of IEnumerable<>.
///
/// Note: anonymous type arguments are acceptable.
///
/// Use other constructor for IEnumerable.
/// </summary>
/// <param name="collection">IEnumerable<>. For IEnumerable use other constructor and specify type.</param>
public EnumerableDataReader(IEnumerable collection)
{
// THANKS DANIEL!
foreach (Type intface in collection.GetType().GetInterfaces())
{
if (intface.IsGenericType && intface.GetGenericTypeDefinition() == typeof (IEnumerable<>))
{
_type = intface.GetGenericArguments()[0];
}
}
if (_type ==null && collection.GetType().IsGenericType)
{
_type = collection.GetType().GetGenericArguments()[0];
}
if (_type == null )
{
throw new ArgumentException(
"collection must be IEnumerable<>. Use other constructor for IEnumerable and specify type");
}
SetFields(_type);
_enumerator = collection.GetEnumerator();
}
/// <summary>
/// Create an IDataReader over an instance of IEnumerable.
/// Use other constructor for IEnumerable<>
/// </summary>
/// <param name="collection"></param>
/// <param name="elementType"></param>
public EnumerableDataReader(IEnumerable collection, Type elementType)
: base(elementType)
{
_type = elementType;
_enumerator = collection.GetEnumerator();
}
/// <summary>
/// Helper method to create generic lists from anonymous type
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static IList ToGenericList(Type type)
{
return (IList) Activator.CreateInstance(typeof (List<>).MakeGenericType(new[] {type}));
}
/// <summary>
/// Return the value of the specified field.
/// </summary>
/// <returns>
/// The <see cref="T:System.Object"/> which will contain the field value upon return.
/// </returns>
/// <param name="i">The index of the field to find.
/// </param><exception cref="T:System.IndexOutOfRangeException">The index passed was outside the range of 0 through <see cref="P:System.Data.IDataRecord.FieldCount"/>.
/// </exception><filterpriority>2</filterpriority>
public override object GetValue(int i)
{
if (i < 0 || i >= Fields.Count)
{
throw new IndexOutOfRangeException();
}
return Fields[i].Getter(_current);
}
/// <summary>
/// Advances the <see cref="T:System.Data.IDataReader"/> to the next record.
/// </summary>
/// <returns>
/// true if there are more rows; otherwise, false.
/// </returns>
/// <filterpriority>2</filterpriority>
public override bool Read()
{
bool returnValue = _enumerator.MoveNext();
_current = returnValue ? _enumerator.Current : _type.IsValueType ? Activator.CreateInstance(_type) : null;
return returnValue;
}
}
}
// <copyright project="Salient.Data" file="ObjectDataReader.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System;
using System.Collections.Generic;
using System.Data;
using Salient.Reflection;
namespace Salient.Data
{
public abstract class ObjectDataReader : IDataReader
{
protected bool Closed;
protected IList<DynamicProperties.Property> Fields;
protected ObjectDataReader()
{
}
protected ObjectDataReader(Type elementType)
{
SetFields(elementType);
Closed = false;
}
#region IDataReader Members
public abstract object GetValue(int i);
public abstract bool Read();
#endregion
#region Implementation of IDataRecord
public int FieldCount
{
get { return Fields.Count; }
}
public virtual int GetOrdinal(string name)
{
for (int i = 0; i < Fields.Count; i++)
{
if (Fields[i].Info.Name == name)
{
return i;
}
}
throw new IndexOutOfRangeException("name");
}
object IDataRecord.this[int i]
{
get { return GetValue(i); }
}
public virtual bool GetBoolean(int i)
{
return (Boolean) GetValue(i);
}
public virtual byte GetByte(int i)
{
return (Byte) GetValue(i);
}
public virtual char GetChar(int i)
{
return (Char) GetValue(i);
}
public virtual DateTime GetDateTime(int i)
{
return (DateTime) GetValue(i);
}
public virtual decimal GetDecimal(int i)
{
return (Decimal) GetValue(i);
}
public virtual double GetDouble(int i)
{
return (Double) GetValue(i);
}
public virtual Type GetFieldType(int i)
{
return Fields[i].Info.PropertyType;
}
public virtual float GetFloat(int i)
{
return (float) GetValue(i);
}
public virtual Guid GetGuid(int i)
{
return (Guid) GetValue(i);
}
public virtual short GetInt16(int i)
{
return (Int16) GetValue(i);
}
public virtual int GetInt32(int i)
{
return (Int32) GetValue(i);
}
public virtual long GetInt64(int i)
{
return (Int64) GetValue(i);
}
public virtual string GetString(int i)
{
return (string) GetValue(i);
}
public virtual bool IsDBNull(int i)
{
return GetValue(i) == null;
}
object IDataRecord.this[string name]
{
get { return GetValue(GetOrdinal(name)); }
}
public virtual string GetDataTypeName(int i)
{
return GetFieldType(i).Name;
}
public virtual string GetName(int i)
{
if (i < 0 || i >= Fields.Count)
{
throw new IndexOutOfRangeException("name");
}
return Fields[i].Info.Name;
}
public virtual int GetValues(object[] values)
{
int i = 0;
for (; i < Fields.Count; i++)
{
if (values.Length <= i)
{
return i;
}
values[i] = GetValue(i);
}
return i;
}
public virtual IDataReader GetData(int i)
{
// need to think about this one
throw new NotImplementedException();
}
public virtual long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
{
// need to keep track of the bytes got for each record - more work than i want to do right now
// http://msdn.microsoft.com/en-us/library/system.data.idatarecord.getbytes.aspx
throw new NotImplementedException();
}
public virtual long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
{
// need to keep track of the bytes got for each record - more work than i want to do right now
// http://msdn.microsoft.com/en-us/library/system.data.idatarecord.getchars.aspx
throw new NotImplementedException();
}
#endregion
#region Implementation of IDataReader
public virtual void Close()
{
Closed = true;
}
public virtual DataTable GetSchemaTable()
{
var dt = new DataTable();
foreach (DynamicProperties.Property field in Fields)
{
dt.Columns.Add(new DataColumn(field.Info.Name, field.Info.PropertyType));
}
return dt;
}
public virtual bool NextResult()
{
throw new NotImplementedException();
}
public virtual int Depth
{
get { throw new NotImplementedException(); }
}
public virtual bool IsClosed
{
get { return Closed; }
}
public virtual int RecordsAffected
{
get
{
// assuming select only?
return -1;
}
}
#endregion
#region Implementation of IDisposable
public virtual void Dispose()
{
Fields = null;
}
#endregion
protected void SetFields(Type elementType)
{
Fields = DynamicProperties.CreatePropertyMethods(elementType);
}
}
}
// <copyright project="Salient.Reflection" file="DynamicProperties.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Reflection.Emit;
namespace Salient.Reflection
{
/// <summary>
/// Gets IL setters and getters for a property.
///
/// started with http://jachman.wordpress.com/2006/08/22/2000-faster-using-dynamic-method-calls/
/// </summary>
public static class DynamicProperties
{
#region Delegates
public delegate object GenericGetter(object target);
public delegate void GenericSetter(object target, object value);
#endregion
public static IList<Property> CreatePropertyMethods(Type T)
{
var returnValue = new List<Property>();
foreach (PropertyInfo prop in T.GetProperties())
{
returnValue.Add(new Property(prop));
}
return returnValue;
}
public static IList<Property> CreatePropertyMethods<T>()
{
var returnValue = new List<Property>();
foreach (PropertyInfo prop in typeof (T).GetProperties())
{
returnValue.Add(new Property(prop));
}
return returnValue;
}
/// <summary>
/// Creates a dynamic setter for the property
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
public static GenericSetter CreateSetMethod(PropertyInfo propertyInfo)
{
/*
* If there's no setter return null
*/
MethodInfo setMethod = propertyInfo.GetSetMethod();
if (setMethod == null)
return null;
/*
* Create the dynamic method
*/
var arguments = new Type[2];
arguments[0] = arguments[1] = typeof (object);
var setter = new DynamicMethod(
String.Concat("_Set", propertyInfo.Name, "_"),
typeof (void), arguments, propertyInfo.DeclaringType);
ILGenerator generator = setter.GetILGenerator();
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Castclass, propertyInfo.DeclaringType);
generator.Emit(OpCodes.Ldarg_1);
if (propertyInfo.PropertyType.IsClass)
generator.Emit(OpCodes.Castclass, propertyInfo.PropertyType);
else
generator.Emit(OpCodes.Unbox_Any, propertyInfo.PropertyType);
generator.EmitCall(OpCodes.Callvirt, setMethod, null);
generator.Emit(OpCodes.Ret);
/*
* Create the delegate and return it
*/
return (GenericSetter) setter.CreateDelegate(typeof (GenericSetter));
}
/// <summary>
/// Creates a dynamic getter for the property
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
public static GenericGetter CreateGetMethod(PropertyInfo propertyInfo)
{
/*
* If there's no getter return null
*/
MethodInfo getMethod = propertyInfo.GetGetMethod();
if (getMethod == null)
return null;
/*
* Create the dynamic method
*/
var arguments = new Type[1];
arguments[0] = typeof (object);
var getter = new DynamicMethod(
String.Concat("_Get", propertyInfo.Name, "_"),
typeof (object), arguments, propertyInfo.DeclaringType);
ILGenerator generator = getter.GetILGenerator();
generator.DeclareLocal(typeof (object));
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Castclass, propertyInfo.DeclaringType);
generator.EmitCall(OpCodes.Callvirt, getMethod, null);
if (!propertyInfo.PropertyType.IsClass)
generator.Emit(OpCodes.Box, propertyInfo.PropertyType);
generator.Emit(OpCodes.Ret);
/*
* Create the delegate and return it
*/
return (GenericGetter) getter.CreateDelegate(typeof (GenericGetter));
}
#region Nested type: Property
public class Property
{
public GenericGetter Getter;
public PropertyInfo Info;
public GenericSetter Setter;
public Property(PropertyInfo info)
{
Info = info;
Setter = CreateSetMethod(info);
Getter = CreateGetMethod(info);
}
}
#endregion
///// <summary>
///// An expression based Getter getter found in comments. untested.
///// Q: i don't see a reciprocal setter expression?
///// </summary>
///// <typeparam name="T"></typeparam>
///// <param name="propName"></param>
///// <returns></returns>
//public static Func<T> CreateGetPropValue<T>(string propName)
//{
// var param = Expression.Parameter(typeof(object), "container");
// var func = Expression.Lambda(
// Expression.Convert(Expression.PropertyOrField(Expression.Convert(param, typeof(T)), propName), typeof(object)), param);
// return (Func<T>)func.Compile();
//}
}
}
Collections Generic List anonymous type to System Data.IDataReader
The DataTable.Load method expects a DataReader but you're giving it a List<anonymoustype>. You need to use a loop:
DataTable dtt = new DataTable();
dtt.Columns.Add("UserId", typeof(System.Guid));
dtt.Columns.Add("FullName", typeof(string));
dtt.Columns.Add("EmployeeCode ", typeof(string));
foreach(var employee in empList1)
dtt.Rows.Add(employee.UserId, employee.FullName, employee.EmployeeCode);
This is efficient and readable, but if you need a generic one-liner you have to use reflection: Convert generic List/Enumerable to DataTable?
How can I easily convert DataReader to ListT?
I have seen systems that use Reflection and attributes on Properties or fields to maps DataReaders to objects. (A bit like what LinqToSql does.) They save a bit of typing and may reduce the number of errors when coding for DBNull etc. Once you cache the generated code they can be faster then most hand written code as well, so do consider the “high road” if you are doing this a lot.
See "A Defense of Reflection in .NET" for one example of this.
You can then write code like
class CustomerDTO
{
[Field("id")]
public int? CustomerId;
[Field("name")]
public string CustomerName;
}
...
using (DataReader reader = ...)
{
List<CustomerDTO> customers = reader.AutoMap<CustomerDTO>()
.ToList();
}
(AutoMap(), is an extension method)
@Stilgar, thanks for a great comment
If are able to you are likely to be better of using NHibernate, EF or Linq to Sql, etc However on old project (or for other (sometimes valid) reasons, e.g. “not invented here”, “love of stored procs” etc) It is not always possible to use a ORM, so a lighter weight system can be useful to have “up your sleeves”
If you every needed too write lots of IDataReader loops, you will see the benefit of reducing the coding (and errors) without having to change the architecture of the system you are working on. That is not to say it’s a good architecture to start with..
I am assuming that CustomerDTO will not get out of the data access layer and composite objects etc will be built up by the data access layer using the DTO objects.
A few years after I wrote this answer Dapper entered the world of .NET, it is likely to be a very good starting point for writing your onw AutoMapper, maybe it will completely remove the need for you to do so.
.net: Converting a Generic List to a DataReader?
You can convert it to DataTable and send to SqlBulkCopy. It would be much easier, and there is no performance penalty, because all data is already present in memory.
There is similar question on SO.
Idatareader to List
DataReader is read-only, forward-only stream of data from a database. The way you are doing it is probably the fastest.
Using the DataReader can increase application performance both by
retrieving data as soon as it is available, rather than waiting for
the entire results of the query to be returned, and (by default)
storing only one row at a time in memory, reducing system overhead.
Retrieving Data Using the DataReader
Convert rows from a data reader into typed results
Do you really need a list, or would IEnumerable be good enough?
I know you want it to be generic, but a much more common pattern is to have a static Factory method on the target object type that accepts a datarow (or IDataRecord). That would look something like this:
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public static Employee Create(IDataRecord record)
{
return new Employee
{
Id = record["id"],
Name = record["name"]
};
}
}
.
public IEnumerable<Employee> GetEmployees()
{
using (var reader = YourLibraryFunction())
{
while (reader.Read())
{
yield return Employee.Create(reader);
}
}
}
Then if you really need a list rather than an IEnumerable you can call .ToList() on the results. I suppose you could also use generics + a delegate to make the code for this pattern more re-usable as well.
Update: I saw this again today and felt like writing the generic code:
public IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> BuildObject)
{
try
{
while (reader.Read())
{
yield return BuildObject(reader);
}
}
finally
{
reader.Dispose();
}
}
//call it like this:
var result = GetData(YourLibraryFunction(), Employee.Create);
Related Topics
Advantages to Using Private Static Methods
Token Based Authentication in ASP.NET Core (Refreshed)
Get List of Zero Reference Codes in Visual Studio
Practical Applications of Bitwise Operations
How to Print Out a Tree Structure
Deferred Execution and Eager Evaluation
How to Align Text in Columns Using Console.Writeline
Determine Assembly Version During a Post-Build Event
Expression.Lambda and Query Generation at Runtime, Simplest "Where" Example
ASP.NET Webapi: How to Perform a Multipart Post with File Upload Using Webapi Httpclient
Sending Outlook Meeting Requests Without Outlook
Why Would I Bother to Use Task.Configureawait(Continueoncapturedcontext: False);
Any Reason to Use Auto-Implemented Properties Over Manual Implemented Properties
Using Datetime in a SQLparameter for Stored Procedure, Format Error