Skip to content

Instantly share code, notes, and snippets.

@jferguson
Created January 26, 2012 07:12
Show Gist options
  • Save jferguson/1681480 to your computer and use it in GitHub Desktop.
Save jferguson/1681480 to your computer and use it in GitHub Desktop.
SqlBulkCopy Generic List<T>
public static void BulkInsert<T>(string connection, string tableName, IList<T> list)
{
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.BatchSize = list.Count;
bulkCopy.DestinationTableName = tableName;
var table = new DataTable();
var props = TypeDescriptor.GetProperties(typeof(T))
//Dirty hack to make sure we only have system data types
//i.e. filter out the relationships/collections
.Cast<PropertyDescriptor>()
.Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
.ToArray();
foreach (var propertyInfo in props)
{
bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
}
var values = new object[props.Length];
foreach (var item in list)
{
for (var i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
bulkCopy.WriteToServer(table);
}
}
@michal-ciechan
Copy link

Similar to paully21 i forked the code and made a few modifications:

  1. Added check to ignore any [DatabaseGenerated] annotated properties
  2. Added check to ignore any [NotMapped] annotated properties
  3. Added check to use [Column] attribute on properties if present to figure out what column to map to
  4. Added check to use [Table] attribute on class if present to figure out the TableName, otherwise use ModelName. Also added "string table" optional argument.
  5. Added "int batchSize = 0" optional argument, and set the BatchSize to that.
  6. Changed IList to IEnumerable
  7. Renamed "string connection" to "connectionString" to be more descriptive
  8. Added extension methods on DbContext so it can figure out the ConnectionString using the DbContext.Database.Connection.ConnectionString property

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment