Developers implementing data-driven applications are very much familiar with ADO.NET library. Those working closely with SQL Server day-in day-out know about classes like Dataset, SqlDataReader, SqlConnection, SqlCommand etc. Have you heard to SqlBulkCopy available in System.Data namespace since .NET Framework 2.0? Perhaps not all of us. If you haven’t then I am sure you will be tempted to use it in your next killer application or maybe you might refactor your existing applications.
SqlBulkCopy: As a developer we always want to improve application either by making UI better or by loading data quicker or doing some other clever stuffs to satisfy broad range of end users - continuous improvement (Kaizen) is the key. If there is a need to import large dataset containing millions of records, you have many options. I have listed two of most widely used methods.
1. Read source file line by line and keep on dumping records into database. This is very time consuming.
2. Create SSIS package to import. This is fastest way and it works fine as long as source file maintains header information same always.
Unfortunately, source file header information gets modified for different reasons. This change shouldn't break your data import process. Here comes SqlBulkCopy class handy. Column mapping feature allows map source column header to correct destination table column. It notifies number of records being inserted thus it is possible to update UI progress bar accordingly. Data loading is amazingly quick. It drastically reduces time taken to load data into database. Personal experience is that source file containing half a million records could be dumped in couple of a seconds which would otherwise take a few minutes using method 1 outlined above.
How does the SqlBulkCopy class looks? Please refer MSDN
Thanks,
Milan Gurung
No comments:
Post a Comment