Bring Bulk insert into the ORM era


no comments

Bring Bulk insert into the ORM era

modern application increasingly drifting toward Object Relational Mapping (ORM).

but ORMs does not designed for bulk Insert operations,

there were a few community solution, including this one, which deal with the issue,

those solution are using the old ugly DataTable,

which means ugly mapping from DTO to DataTable (and potential memory leak

if the DataTable does not disposed).


this post will suggest alternative solution which will use relative small helper class that can

convert any IEnumerable<T> into partial implementation of IDataReader

that can be handed to the SqlBulkCopy class.

the source code for this post available for download here.

how does it work?

you can convert any collection into BulkReader<T> and hand it to

bulk copy provider.

Code Snippet
  1. static void BulkInsert(IEnumerable<Dto> dtos)
  2. {
  3.     var bulkProvider = new SqlBulkCopyProvider(CONN_STR, TBL_NAME);
  4.     var reader = new BulkReader<Dto>(dtos, 3, ValueMappingStrategy);
  5.     bulkProvider.WriteToServer(reader);
  6. }
  8. private static object ValueMappingStrategy(int index, Dto dto)
  9. {
  10.     switch (index)
  11.     {
  12.         case 1:
  13.             return dto.Name;
  14.         case 2:
  15.             return dto.Date;
  16.         case 3:
  17.             return dto.Approve;
  18.         default:
  19.             throw new IndexOutOfRangeException();
  20.     }
  21. }

in the above snippet, we are supplying the data collection,

amount of dto’s fields and Func<int, T, object> as mapping strategy.


in real world most Bulk Insert scenario are limit to relative small amount of table.

if so you can inherit BulkReaderBase<T> and get a typed reader

instead of using the Func<int, T, object> strategy.


Code Snippet
  1. private class DtoBulkReader : BulkReaderBase<Dto>
  2. {
  3.     public DtoBulkReader(
  4.         IEnumerable<Dto> source)
  5.         : base(source, 3) { }
  7.     public override object GetValue(int i)
  8.     {
  9.         switch (i)
  10.         {
  11.             case 1:
  12.                 return Current.Name;
  13.             case 2:
  14.                 return Current.Date;
  15.             case 3:
  16.                 return Current.Approve;
  17.             default:
  18.                 throw new IndexOutOfRangeException();
  19.         }
  20.     }
  21. }



I was working on this helper along with Amit Elfacy and Amir Shitrit.


Source Code

the source code for this post available for download here.


kick it on Shout it

Digg This
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>