-
-
Save mahizsas/1e02532399af72ef92d9 to your computer and use it in GitHub Desktop.
BulkInsert with SqlBulkCopy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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