Bulk Inserting Into Your Database

So you have a list of domain objects you want to bang into the database really quickly. You don't want to change anything about the items, and the properties on the domain object map one to one to database field names.

There is a way to do it really fast using the SqlBulkCopy class - especially if you are inserting hundreds or thousands of rows at one time. Here is some code that does it for you,

public class BulkInserter
{
  private object lockObject = new object();

  public void BulkInsertRows<T>(List<T> insertionList, string connectionString,
    string databaseTableName)
  {
    lock (lockObject)
    {
      DataTable dt = ConvertTo<T>(insertionList);

      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, 
          SqlBulkCopyOptions.Default))
      {
        bulkCopy.BulkCopyTimeout = 60;
        bulkCopy.DestinationTableName = databaseTableName;
        bulkCopy.WriteToServer(dt);
      }
    }
  }

  private DataTable ConvertTo<T>(IList<T> list)
  {
    DataTable table = CreateTable<T>();
    Type entityType = typeof(T);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (T item in list)
    {
      DataRow row = table.NewRow();

      foreach (PropertyDescriptor prop in properties)
      {
        row[prop.Name] = prop.GetValue(item);
      }

      table.Rows.Add(row);
    }

    return table;
  }

  private DataTable CreateTable<T>()
  {
    Type entityType = typeof(T);
    DataTable table = new DataTable(entityType.Name);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (PropertyDescriptor prop in properties)
    {
      table.Columns.Add(prop.Name, prop.PropertyType);
    }

    return table;
  }
}

So what is the code doing? You pass in a list of domain objects, and the CreateTable and ConvertTo methods convert this list into a DataTable. This DataTable is what the SqlBulkCopy class uses to do the bulk insert.

The first method has a parameter databaseTableName. In hind site there is probably a better way to do that, perhaps typeof(T).Name or something similar.