Skip to content

Instantly share code, notes, and snippets.

@mahizsas
Forked from paully21/gist:7056277
Last active August 29, 2015 14:15
Show Gist options
  • Save mahizsas/1e02532399af72ef92d9 to your computer and use it in GitHub Desktop.
Save mahizsas/1e02532399af72ef92d9 to your computer and use it in GitHub Desktop.
BulkInsert with SqlBulkCopy
public static void BulkInsert<T>(string connection, string tableName, IList<T> list, string[] propsToSkip)
{
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock))
{
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")
//skip properties to skip
&& !propsToSkip.Contains(propertyInfo.Name)
//skip properties marked with NotMapped attributes
&& IsMapped(propertyInfo))
.ToArray();
int propertyCount = 0;
foreach (var propertyDescriptor in props)
{
PropertyInfo propertyInfo = propertyDescriptor.ComponentType.GetProperty(propertyDescriptor.Name);
//Get underlying table's column name instead of property name
bulkCopy.ColumnMappings.Add(propertyDescriptor.Name, GetTableColumnName(propertyInfo));
table.Columns.Add(propertyDescriptor.Name, Nullable.GetUnderlyingType(propertyDescriptor.PropertyType) ?? propertyDescriptor.PropertyType);
propertyCount++;
}
var values = new object[propertyCount];
foreach (var item in list)
{
for (var i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
bulkCopy.BatchSize = 5000;
bulkCopy.BulkCopyTimeout = 12000;
bulkCopy.WriteToServer(table);
}
}
public static bool IsMapped(PropertyDescriptor propertyDescriptor)
{
var property = propertyDescriptor.ComponentType.GetProperty(propertyDescriptor.Name);
var type = typeof(NotMappedAttribute);
var prop = property.GetCustomAttributes(type, false);
return prop.Count() == 0;
}
public static string GetTableColumnName(PropertyInfo property)
{
var type = typeof(ColumnAttribute);
var prop = property.GetCustomAttributes(type, false);
if (prop.Count() > 0)
return ((ColumnAttribute)prop.First()).Name;
return property.Name;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment