-
-
Save DavidPx/353a20c2f86530cadc63627e625bd9f4 to your computer and use it in GitHub Desktop.
Use SQLBulkLoad with linq-toSQL in LinqPad - now with Property support.
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
// 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(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I'll note that using the Linqpad-generated class for this operation isn't possible because it doesn't have the
Table
andColumn
attributes. Also, I had to use the name parameter for myColumn
attributes.