Over the years I've worked on a number of projects that have shared a common requirement - the ability for users to quickly import large amounts of data into a back end SQL
Server database. One such project was a web application used by teachers and other staff members. The software was initially purchased for just two schools in the district,
but was soon expanded to encompass other schools. Every few months one or two new schools were brought into the fold; every time a new school was added an administrative
user would have to create accounts for the new teachers and staff members so that they could sign into the site. Initially, the application offered a web page for the
administrator to create new user accounts one at a time, but this interface quickly became tedious and impractical once larger schools with upwards of 100 users were
brought online.
To allow school administrators to quickly import new users we created a web page from which a user could upload an Excel spreadsheet that contained the one row for every new
user; the columns in the spreadsheet mapped to table columns in the database. After uploading this spreadsheet, the application would walk through each row and insert a record
into the table. Through this mechanism an administrator could create the 100+ user accounts by first building an Excel spreadsheet (something many of the schools already had
on file) and then upload that spreadsheet. This technique may sound familiar - in Importing an Excel Spreadsheet
Using Typed DataSets and TableAdapters, author Nannette Thacker walked through building such an interface.
While the described approach works well when importing hundreds of records, it starts to seriously slow down when importing thousands or tens of thousands of records.
The slowdown is due to the fact that each imported record sends its own INSERT statement to the database. This results in a lot of "chatter" between the web
server and the database server. If you are importing data to Microsoft SQL Server the good news is that this process can be dramatically sped up using ADO.NET's
SqlBulkCopy class. In my testing, importing 10,000 records using
the one INSERT statement per import record took more than three seconds to complete; using SqlBulkCopy took a fraction of a second.
This article look at how to use the SqlBulkCopy class to efficiently execute bulk operations against Microsoft SQL Server. Read on to learn more!
Read More >