SqlBulkCopy Generic List<T>

gistfile1.cs

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);
     }
}

Jarod,

Thanks for the code, it was exactly what I was looking for.

I made a few modifications.

1. I added a way for the caller to exclude fields from the bulk copy (this is useful for identity columns)
2. I added a check for the NotMapped attribute as well...Sometimes we have properties marked as NotMapped (readonly properties like FullName or LocalTime) and it's useful to have a way to tell the BulkCopy to not include them
3. I added a method to get the underlying table column name from the field. Sometimes they can be different and that was causing issues.

I forked it over to gist:7056277

If you have any suggestions, please let me know.

Thanks again,

Paul

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