Tuesday, March 31, 2015

ADO.NET Bulk Insert

The SqlBulkCopy class in the System.Data.SqlClient namespace in .NET worked to significantly increase efficiency of inserting thousands of rows in a table. The inserts of about 100k rows with 24 columns of mixed data types went from minutes to seconds after switching from a row-by-row update with a stored procedure to the bulk insert. I set it to insert 1000 rows at a time with no callbacks and left the default timeout of 30 seconds.


The source data came from a query that the same code owns so I put all of the column names in an array of strings and did a Linq .Select to add them to the ColumnMappings. It didn't work at first, I found that I had to call .ToArray() after the Select() in order to iterate the array before calling WriteToServer to perform the insert.


I can see how this could be wrapped up and made dynamic by walking the ColumnName properties of a DataTable to add ColumnMappings if they are 1:1. Another potential pattern would be to wrap the table stuff in a class that knows about mappings and abstract it away a bit because it is a bit low level when working with business logic.

36 comments:

  1. Pretty article! I found some useful information in your blog....

    so here we provide,

    We provide you with flexible services and complete hybrid network solutions. It can provide your organisation with exceptional data speeds, advanced external security protection, and high-resilience by leveraging the latest SD-WAN and networking technologies to monitor, manage and strengthening your organisation’s existing network devices.

    https://www.quadsel.in/networking/>
    https://twitter.com/quadsel/
    https://www.linkedin.com/company/quadsel-systems-private-limited/
    https://www.facebook.com/quadselsystems/

    #quadsel #network #security #technologies #managedservices #Infrastructure #Networking #OnsiteResources #ServiceDeskSupport #StorageServices #WarrantyAMCServices #datacentersolutions #DataCenterBuild #EWaste #InfraConsolidation #DisasterRecovery #NetworkingServices #ImagingServices #MPS #Consulting #WANOptimisation #enduserservices

    ReplyDelete
  2. Very nice post..After reading your post,thanks for taking the time to discuss this, I feel happy about and I love learning more about this topic.
    Selenium Training in chennai | Selenium Training in anna nagar | Selenium Training in omr | Selenium Training in porur | Selenium Training in tambaram | Selenium Training in velachery

    ReplyDelete
  3. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you Best ccna certification service provider

    ReplyDelete
  4. This post is so interactive and informative.keep update more information...
    Android Training in Tambaram
    Android Training in Chennai

    ReplyDelete
  5. This post is so interactive and informative.keep update more information…
    CCNA course in Anna Nagar
    ccna course in Chennai

    ReplyDelete
  6. Great post. keep sharing such a worthy information.
    QTP Online Training

    ReplyDelete
  7. fine blog and honestly awesome. you could lead your hands for your palms on grip of some thing a good deal stepped forward however i nonetheless make known this affiliation maintain infuriating for the best.! Visual Paradigm 14 Crack

    ReplyDelete
  8. i discovered your this make recognized even though attempting to find statistics roughly blog-connected research ... it is a big pronounce .. desist posting and updating point out.! Office 2010 Activation Crack

    ReplyDelete
  9. it became a first rate unintentional to visit this nice of site and i am satisfied to recognize. thank you therefore lots for giving us a chance to have this possibility..! First Birthday Wishes For Baby Boy

    ReplyDelete


  10. If you want to visit the site, just click the link.
    https://easyserialkeys.com/edraw-max-crack-license-key/

    ReplyDelete