Skip to content

Instantly share code, notes, and snippets.

@DavidPx
Forked from jmreynolds/BulkInsertAll.cs
Last active January 15, 2020 16:39
Show Gist options
  • Save DavidPx/353a20c2f86530cadc63627e625bd9f4 to your computer and use it in GitHub Desktop.
Save DavidPx/353a20c2f86530cadc63627e625bd9f4 to your computer and use it in GitHub Desktop.
Use SQLBulkLoad with linq-toSQL in LinqPad - now with Property support.
// Add this to LinqPad "C# Program"
// Example use:
// Insead of this: (create a List<CS_POSITION>)
// CS_POSITIONs.InsertOnSubmit(position);
// This:
// positions.Add(position);
//
// Then instead of this:
// SubmitChanges()
// This:
// BulkInsertAll<CS_POSITION>(positions)
void BulkInsertAll<T>(List<T> entities)
{
var conn = (SqlConnection)this.Connection;
Type t = typeof(T);
var tableAttribute = (TableAttribute)t.GetCustomAttributes(typeof(TableAttribute), false).Single();
var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableAttribute.Name };
var properties = t.GetMembers().Where(p =>
{
var columnAttribute = Attribute.GetCustomAttribute(p, typeof(ColumnAttribute)) as ColumnAttribute;
if (columnAttribute != null) return true;
return false;
}).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type memberType = null;
switch (property)
{
case PropertyInfo pi:
memberType = pi.PropertyType;
break;
case FieldInfo fi:
memberType = fi.FieldType;
break;
default:
throw new InvalidOperationException();
}
if (memberType.IsGenericType && memberType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
memberType = Nullable.GetUnderlyingType(memberType);
}
table.Columns.Add(new DataColumn(property.Name, memberType));
}
// https://stackoverflow.com/a/50995201/89176
table.Columns.Cast<DataColumn>().ToList().ForEach(x =>
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));
foreach (var entity in entities)
{
var cols = properties.Select(property =>
{
switch (property)
{
case FieldInfo fi:
return fi.GetValue(entity) ?? DBNull.Value;
case PropertyInfo pi:
return pi.GetValue(entity) ?? DBNull.Value;
default:
throw new InvalidOperationException();
}
}).ToArray();
table.Rows.Add(cols);
}
conn.Open();
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.WriteToServer(table);
conn.Close();
}
@DavidPx
Copy link
Author

DavidPx commented Jan 15, 2020

I'll note that using the Linqpad-generated class for this operation isn't possible because it doesn't have the Table and Column attributes. Also, I had to use the name parameter for my Column attributes.

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