At my current job we load massive amounts of measurement data into the database. This is done using SqlBulkCopy. The SqlBulkCopy class is a very usefull class that lets you efficiently bulk load a SQL Server table with data from another source. The function in the SqlBulkCopy class that starts the bulk copy is WriteToServer. This function can be used with a DataTable or any class implementing IDataReader. We used it with datatables resulting in the following code.

DataRow row = _measDatatable[_currentIndex].NewRow();
row["SensorID"] = (byte)dataPacket.TargetAudience;
row["ColourID"] = GetColourByCharacter(Convert.ToChar(dataPacket.Measurement.Substring(3, 1)));
row["DirectionID"] = GetDirectionByCharacter(Convert.ToChar(dataPacket.Measurement.Substring(4, 1)));
row["Timestamp"] = dataPacket.Timestamp;
row["Value"] = int.Parse(dataPacket.Measurement.Substring(5, 5));
_measDatatable[_currentIndex].Rows.Add(row);

And

public bool BulkCopy(DataTable source)
{
   using (SqlBulkCopy copy = new SqlBulkCopy(_connection))
   {
      copy.DestinationTableName = source.TableName;
      copy.WriteToServer(source);
      return true;
   }
}

In all the other code we have replaced the DataTables with List<T> implementations where T is a simple POCO object, but here it was not that simple. If you want to use SqlBulkCopy with a List<T> or any IEnumerable<T> you have to create a wrapper class implementing IDataReader. I have done just that.

The final code looks like this:

var list = new List<Meas>();
var meas = new Meas() {
    SensorID = (byte)dataPacket.TargetAudience,
    ColourID = GetColourByCharacter(Convert.ToChar(dataPacket.Measurement.Substring(3, 1))),
    DirectionID = GetDirectionByCharacter(Convert.ToChar(dataPacket.Measurement.Substring(4, 1))),
    Timestamp = dataPacket.Timestamp,
    Value = int.Parse(dataPacket.Measurement.Substring(5, 5)),
 }; 
 list.Add(meas);

And

public bool BulkCopy<T>(IEnumerable<T> source) 
{
  var reader = new BulkListReader<T>(source, _connection);
  using (SqlBulkCopy copy = new SqlBulkCopy(_connection)) 
  { 
    foreach (var map in reader.ColumnMapping) 
	{ 
	  copy.ColumnMappings.Add(map); 
    } 
	copy.DestinationTableName = reader.DestinationTableName; 
	copy.WriteToServer(reader); 
	return true; 
  } 
} 

The POCO object should have the same propertynames as the destination table has columnnames. Default the DestinationTableName is the same as the POCO object class name, but you can specify a different name. The BulkCopyListReader class is the wrapper class around the List<T> object implementing the IDataReader. The main concern of the BulkCopyListReader wrapper was of course performance. The BulkCopyListReader stores delegates to the properties in T in a static dictionary. The first construction of a BulkCopyListReader<T> will take a little time creating these static delegates. The Bulkcopy<T> function after initialising is as fast as the normal BulkCopy function.

The source is on GitHub, but you can also reference the package using NuGet.