The method below describes how to upload a file to a webserver and then import the file into SQL using either LinqToSQL or SQL Bulk Copy. The sample code only shows how to import xls and xlsx files but it could easily be extended to support csv files too. Sample code is attached at the bottom.
We will be uploading data from an Excel file containing columns CompanyName and Phone and loading that into the Northwind Shippers table.
We’ll start by uploading the file to the webserver. This is done with the help of the FileUpload web control. The FileUpload control has a SaveAs method which saves the contents of the file into the location that we specify. The file will be stored in a temp folder under App_Data since App_Data is not browsable directly by users.
Once we have successfully uploaded the file to the webserver, we use an OleDbConnection and an OleDbDataReader to read each row from the Excel file. The OleDb connection string varies by file extension. The connection strings are shown below:
| Extension |
ConnectionString |
| xls |
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1” |
| xlsx |
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FilePath];Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1 |
HDR=Yes specifies that the first row of the data contains column names and not data
IMEX=1 specifies that the driver should always read the “intermixed” data columns as text.
The query we will be using with the connection is "SELECT CompanyName, Phone FROM [Sheet1$]". This assumes that we have an excel sheet called Sheet1 with header columns CompanyName and Phone.
Method 1: Using LINQ To SQL
Using the OleDBDataReader, we read each record and create a new Shipper object for each OleDbDataReader record as shown below. We add this object to the Shipper collection object that is associated with the Shipper table in the database using InsertOnSubmit and call SubmitChanges. This loads all the Excel records into the Shipper table.
Note: Since we are calling SubmitChanges without any Transaction defined, LINQ to SQL automatically starts a local transaction and uses it to execute the insert statements. When all insert statements successfully complete, LINQ to SQL commits the local transaction – nice:-) This occurs behind the scenes.
1: //ref: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader(VS.71).aspx
2: using (var context = new NorthwindDataContext())
3: { 4: using (var myConnection = new OleDbConnection(base.SourceConnectionString))
5: using (var myCommand = new OleDbCommand(query, myConnection))
6: { 7: myConnection.Open();
8: var myReader = myCommand.ExecuteReader();
9: while (myReader.Read())
10: { 11: context.Shippers.InsertOnSubmit(new Shipper()
12: { 13: CompanyName = myReader.GetString(0),
14: Phone = myReader.GetString(1)
15: });
16: }
17: }
18:
19: context.SubmitChanges();
20: }
Method 2: Using SQL BulkCopy
With the BulkCopy method, we first have to define the Column Mappings since we will not be inserting data into the autogenerated ShipperID Primary Key column. The first column in the Excel file (CompanyName) has to be mapped to the second column in the Shipper table and the second column (Phone) has to be mapped to the third column in the Shipper table as shown below.
We read each record from the OleDbDataReader and using the BulkCopy WriteToServer overload that takes in an IDataReader (which the OleDbDataReader implements). The BulkCopy, using this method bulk loads the Shippers destination table with the data from the OleDbDatareader.
1: //ref: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader(VS.71).aspx
2: //ref: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
3: using (var myConnection = new OleDbConnection(base.SourceConnectionString))
4: using (var destinationConnection = new SqlConnection(destinationConnectionString))
5: using (var bulkCopy = new SqlBulkCopy(destinationConnection))
6: { 7: //Map first column in source to second column in sql table (skipping the ID column).
8: //Excel schema[CompanyName,Phone] Table schema[ShipperID, CompanyName, Phone]
9: bulkCopy.ColumnMappings.Add(0, 1);
10:
11: bulkCopy.ColumnMappings.Add(1, 2);
12: bulkCopy.DestinationTableName = "dbo.Shippers";
13:
14: using (var myCommand = new OleDbCommand(query, myConnection))
15: { 16: myConnection.Open();
17: destinationConnection.Open();
18:
19: var myReader = myCommand.ExecuteReader();
20: while (myReader.Read())
21: { 22: bulkCopy.WriteToServer(myReader);
23: }
24: }
25: }
You will need the 2007 Office System Driver : Data Connectivity Components installed on your server also.
The BulkCopy object is much faster than LINQ to SQL. I am copying Pablo Castro’s newsgroup response:
- No per-row statement execution. When you do multiple inserts without bulk-copy, each insert is a statement in itself (regardless of whether it's batched together with other statements). With bulk-copy, we don't incur the cost of executing a statement for each row, the whole copy operation is a single thing.
- No multiple network round-trips. Once the bulk-insert operation is setup, we send rows from the client to the server continously, without going back-and-forth over the wire.
- Server storage engine also can greatly optimize how rows are inserted when performing a bulk-copy operation. How much can be optimized depends a lot on the recovery model the tarder database is set to; in "simple" and "bulk logged" the overhead of logging is greatly reduced during bulk-copy operations, helping a lot with performance.
Sample Code
Mirror