Yesterday, we released BenGribaudoLLC.IEnumerableHelpers.DataReaderAdapter as a NuGet package!

What does it to?

Adapts IEnumerable<T> to the IDataReader interface. Enables enumerable sequences to be used where a data reader is expected.

Great for loading data from a List<T>, LINQ expression or CSV parser into a database using SqlBulkCopy! Streams data to SqlBulkCopy, bypassing the need to first materialize the entire sequence in memory and load it into a DataTable.

var data = from p in DataContext.People
            where o.Age >= 18
            select new { p.FirstName, p.LastName, p.Age};

using (var bulkCopy = new SqlBulkCopy(connection)) {
  bulkCopy.DestinationTableName = "Adults";
  bulkCopy.WriteToServer(data.AsDataReader());
}

How does it work?

Each item in the enumerable becomes a record returned by the data reader.

AsDataReader()

Used when the enumerable contains IEnumerable<T>s. The items in the inner enumerable become the data record’s fields. Optionally, field names may be provided.

var data = new[] {
  new[] { "Joe", "Smith" },
  new[] { "Bob", "Brown" }
};
 
var reader = data.AsDataReader();
//var reader = data.AsDataReader(fieldNames: new[] { "FirstName", "LastName" });
while (reader.Read())
{
  Console.WriteLine($"{reader.GetValue(0) } {reader.GetValue(1)}");
  //Console.WriteLine($"{reader["FirstName"] } {reader["LastName"]}");
}
// Outputs:
// Joe Smith
// Bob Brown

AsDataReaderOfObjects()

Used when the enumerable contains objects whose public readable instance properties should become the fields returned by the data reader.

var data = new[]
{
  new Item { ItemNumber = 1, Name = "Widget", Price = 10.00m },
  new Item { ItemNumber = 2, Name = "Gadget", Price = 4.99m },
};
 
var reader = data.AsDataReaderOfObjects();
while (reader.Read())
{
  var priceForTwo = reader.GetDecimal(reader.GetOrdinal("Price")) * 2;
  Console.WriteLine($"Item # {reader["ItemNumber"]} - {priceForTwo:C2}");
}

// Outputs:
// Item 1 - $20.00
// Item 2 - $9.98

Notes

• .Net Standard 1.6’s SqlBulkCopy.WriteToServer() does not work with IDataReader, limiting this package’s usefulness on that platform. .Net Standard 2.0 should resolve this issue as it will add a SqlBulkCopy.WriteToServer overload that accepts IDataReader.
• This package’s data reader implementation does not support IDataReader.GetSchemaTable().