Import Excel Data Into SQL Table Using SQLBulkCopy In C#

Introduction

Sometimes we will get a requirement that we need to export the data from Excel or other source into the database table. Here, I am explaining how we can export the data from an Excel sheet data into particular SQL table.

For exporting an Excel data, we need an Excel sheet with the data to be exported into SQL table.

For this demo, I have created a database and a table. I inserted two records, using the script given below. 
  1. CREATE Database db_Test  
  2. USE [db_Test]  
  3. GO  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[Student](  
  9.     [Name] [nvarchar](50) NULL,  
  10.     [DOB] [dateNULL,  
  11.     [Email] [nvarchar](150) NULL,  
  12.     [Mob] [nvarchar](50) NULL  
  13. ON [PRIMARY]  
  14.   
  15. GO  
  16. INSERT [dbo].[Student] ([Name], [DOB], [Email], [Mob]) VALUES (N'a'CAST(N'1990-01-01' AS Date), N'a@gmail.com', N'555555555')  
  17. INSERT [dbo].[Student] ([Name], [DOB], [Email], [Mob]) VALUES (N'b'CAST(N'1990-04-04' AS Date), N'b@gmail.com', N'777777777')   

The Excel sheet, which I have created is given below.


I have created win form Application and I added Form named as ImportFromExcel. I designed it, as given below (two buttons, One textbox, one label and one GIF image).



I added the code for browse button to browse an Excel file, as shown below.

To browse, I am calling OpenFileDialog class, given filter and add file path to Text Box. 

  1. OpenFileDialog od = new OpenFileDialog();  
  2.             od.Filter = "Excell|*.xls;*.xlsx;";  
  3.             od.FileName = "EmployeeList.xlsx";  
  4.             DialogResult dr = od.ShowDialog();  
  5.             if (dr == DialogResult.Abort)  
  6.                 return;  
  7.             if (dr == DialogResult.Cancel)  
  8.                 return;  
  9.             txtpath.Text = od.FileName.ToString();  
  10.      btUpload.Visible = true;   
Add string _path; outside this button, so that I can access this string outside this button and assign Excel file path to this _path string, as shown below.
  1. _path = txtpath.Text;  
Next is the main task, i.e we need to read the data from an Excel sheet and insert all the data into the table. This code can be done in an Upload button.

Before beginning, we know that this task is a log task, since we need to take the data from an Excel sheet and we need to insert all this data into the table. If we do this directly, then the form will respond until the task finishes, so we need to use Background worker process here, as shown below.
  • Add usingComponentModel; in namespace for using BackgroundWorker.
  • Add the lines given below into this form class.
  1. BackgroundWorker bw = new BackgroundWorker  
  2.         {  
  3.             WorkerReportsProgress = true,  
  4.             WorkerSupportsCancellation = true  
  5.         }   
Write the function for importing Excel data, as shown below.
  1. Crate function as below  
  2.          private void InsertExcelRecords()  
  3.   {  
  4.   }   

Inside this, we are going to write all the functionalities.

Create an Excel connection, using OLEDb. For this, first add namespace.

  1. using System.Data.OleDb;   

And add Excel connection as below 

  1. string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", _path);  
  2.                 Econ = new OleDbConnection(constr);   
Now, add select query to select the data from this Excel sheet and open this Excel connection, as shown below
  1. string Query = string.Format("Select [Employee Name],[DOB],[Email],[Mobile] FROM [{0}]""Sheet1$");  
  2.                 OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
  3.               Econ.Open();   

Note

In select statement, column names and Excel sheet column names should be the same and if an Excel sheet has column name with spaces like above Employee Name, then you need to provide this into two square braces.

Create one dataset and fill this data set with this selected items, using oledbdataadpter, as shown below.
  1. DataSet ds = new DataSet();  
  2.                 OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
  3.                 Econ.Close();//close Excel connection after adding to data set  
  4.                 oda.Fill(ds);  
  5.   DataTable Exceldt = ds.Tables[0]; //copy data set to datatable   
Check if any mandatory field is required before uploading to the table, if mandatory field value is empty in Excel, then we can remove that row from the above data table, as shown below.

Here, I am checking two mandatory fields, if any one value is null, then I will delete that row and update the data table. 

  1. for (int i = Exceldt.Rows.Count - 1; i >= 0; i--)  
  2.                 {  
  3.                     if (Exceldt.Rows[i]["Employee Name"] == DBNull.Value ||                         Exceldt.Rows[i]["Email"] == DBNull.Value )  
  4.                     {  
  5.                         Exceldt.Rows[i].Delete();  
  6.                     }  
  7.                 }  
  8.                 Exceldt.AcceptChanges();    
Now, we will use SQLbulkcopy to map this Excel data into student table, as shown below.
  1. //creating object of SqlBulkCopy      
  2.                 SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  3.                 //assigning Destination table name      
  4.                 objbulk.DestinationTableName = "Student";  
  5.                 //Mapping Table column    
  6.                 objbulk.ColumnMappings.Add("[Employee Name]""Name");  
  7.                 objbulk.ColumnMappings.Add("DOB""DOB");  
  8.                 objbulk.ColumnMappings.Add("Email""Email");  
  9.     objbulk.ColumnMappings.Add("Mobile""Mob");   

In source column, we have given Excel columns and destination column table column fields.

Now, we have to insert this mapped data into student table, as shown below.
  1. SqlConnection sqlConnection = new SqlConnection();  
  2.   
  3. sqlConnection.ConnectionString = "server = VSBS01; database = dbHRVeniteck; User ID = sa; Password = veniteck@2016"//Create DataBase Connection Details   
  4.   
  5. con.Open(); //Open DataBase conection  
  6.   
  7. objbulk.WriteToServer(Exceldt); //inserting Datatable Records to DataBase con.Close(); //Close DataBase conection  
  8.   
  9. MessageBox.Show("Data has been Imported successfully.""Imported", MessageBoxButtons.OK, MessageBoxIcon.Information);   

Full function code is given below.

  1. private void InsertExcelRecords()  
  2.         {  
  3.   
  4.             try  
  5.             {  
  6.                  
  7.               //  ExcelConn(_path);  
  8.                 string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", _path);  
  9.                 Econ = new OleDbConnection(constr);  
  10.                 string Query = string.Format("Select [Employee Name],[DOB],[Email],[Mobile] FROM [{0}]""Sheet1$");  
  11.                 OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
  12.                 Econ.Open();  
  13.   
  14.                 DataSet ds = new DataSet();  
  15.                 OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
  16.                 Econ.Close();  
  17.                 oda.Fill(ds);  
  18.                 DataTable Exceldt = ds.Tables[0];  
  19.   
  20.                 for (int i = Exceldt.Rows.Count - 1; i >= 0; i--)  
  21.                 {  
  22.                     if (Exceldt.Rows[i]["Employee Name"] == DBNull.Value || Exceldt.Rows[i]["Email"] == DBNull.Value )  
  23.                     {  
  24.                         Exceldt.Rows[i].Delete();  
  25.                     }  
  26.                 }  
  27.                 Exceldt.AcceptChanges();  
  28.                 //creating object of SqlBulkCopy      
  29.                 SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  30.                 //assigning Destination table name      
  31.                 objbulk.DestinationTableName = "Student";  
  32.                 //Mapping Table column    
  33.                 objbulk.ColumnMappings.Add("[Employee Name]""Name");  
  34.                 objbulk.ColumnMappings.Add("DOB""DOB");  
  35.                 objbulk.ColumnMappings.Add("Email""Email");  
  36.                 objbulk.ColumnMappings.Add("Mobile""Mob");  
  37.   
  38.                 //inserting Datatable Records to DataBase   
  39.                 SqlConnection sqlConnection = new SqlConnection();  
  40.                 sqlConnection.ConnectionString = "server = VSBS01; database = dbHRVeniteck; User ID = sa; Password = veniteck@2016"//Connection Details    
  41.                 con.Open();  
  42.                 objbulk.WriteToServer(Exceldt);  
  43.                 con.Close();  
  44.                 MessageBox.Show("Data has been Imported successfully.""Imported", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  45.   
  46.             }  
  47.             catch (Exception ex)  
  48.             {  
  49.                 MessageBox.Show(string.Format("Data has not been Imported due to :{0}", ex.Message), "Not Imported", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
  50.                 pb1.Visible = false;  
  51.                 txtpath.Text = "";  
  52.                 btBrowse.Enabled = true;  
  53.                 label1.Visible = false;  
  54.   
  55.             }  
  56.   
  57.         }   
Now, we can start writing the code below. Upload button click, as shown below.
Hide this upload button, so that the user will not click again, while its already uploading, as shown below.

  1. btUpload.Visible = false;   
Check whether the task is running or not, if it is running, then return from background worker task.
  1. if (bw.IsBusy)  
  2. {  
  3. return;  
  4. }   
To start or stop the background worker, use stopwatch, as shown below.

  1. System.Diagnostics.Stopwatch sWatch = new System.Diagnostics.Stopwatch();   
Now, we will call the log executing function. Here, we are exporting the data from an Excel sheet into table in background worker dowork event, as shown below.
  1. bw.DoWork += (bwSender, bwArg) =>  
  2.                  {  
  3.           
  4.                 sWatch.Start();         //To start Watching  
  5.                 InsertExcelRecords(); //Function which created to export data  
  6.     };   
After completing this task, you can write in runworkercomplete event in background worker, as shown below.
  1. bw.RunWorkerCompleted += (bwSender, bwArg) =>  
  2.             {  
  3.                 //now you're back in the UI thread you can update the form  
  4.                 //remember to dispose of bw now                 
  5.   
  6.                 sWatch.Stop();//To start Watching  
  7.                 //work is done, no need for the stop button now...  
  8.                 pb1.Visible = false//To disable gif image  
  9.                 txtpath.Text = "";  
  10.                 btBrowse.Enabled = true;  
  11.                 label1.Visible = false;  
  12.                 bw.Dispose(); //To disposing background worker  
  13.             };   
After this, we can write what all we need to do before task begins like making GIF image visible etc. and start running this task, as shown below.
  1. pb1.Visible = true;  
  2.             label1.Visible = true;  
  3.              btBrowse.Enabled = false;  
  4.   
  5.             //Starts the actual work - triggerrs the "DoWork" event  
  6.             bw.RunWorkerAsync();   

The full code of upload button is shown below. 

  1. private void btUpload_Click(object sender, EventArgs e)  
  2.         {  
  3.             btUpload.Visible = false;  
  4.             _path = txtpath.Text;  
  5.             if (txtpath.Text == "" || !txtpath.Text.Contains("EmployeeList.xlsx"))  
  6.             {  
  7.                 MessageBox.Show("Please Browse EmployeeList.xlsx to upload""", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  8.                 txtpath.Text = "";  
  9.                 btUpload.Visible = false;  
  10.                 return;  
  11.             }  
  12.             if (bw.IsBusy)  
  13.             {  
  14.                 return;  
  15.             }  
  16.   
  17.             System.Diagnostics.Stopwatch sWatch = new System.Diagnostics.Stopwatch();  
  18.             bw.DoWork += (bwSender, bwArg) =>  
  19.             {  
  20.                 //what happens here must not touch the form  
  21.                 //as it's in a different thread          
  22.                 sWatch.Start();  
  23.                 InsertExcelRecords();  
  24.             };  
  25.   
  26.             bw.ProgressChanged += (bwSender, bwArg) =>  
  27.             {  
  28.                 //update progress bars here  
  29.             };  
  30.   
  31.             bw.RunWorkerCompleted += (bwSender, bwArg) =>  
  32.             {  
  33.                 //now you're back in the UI thread you can update the form  
  34.                 //remember to dispose of bw now                 
  35.   
  36.                 sWatch.Stop();  
  37.   
  38.                 //work is done, no need for the stop button now...  
  39.                 pb1.Visible = false;  
  40.                 txtpath.Text = "";  
  41.                 btBrowse.Enabled = true;  
  42.                 label1.Visible = false;  
  43.                 bw.Dispose();  
  44.             };  
  45.   
  46.             //lets allow the user to click stop  
  47.             pb1.Visible = true;  
  48.             label1.Visible = true;  
  49.             MsgBox.Show("Uploading has been started !.\nyou are free to do any other tasks in this application,if you wish to close this screen  you can do it.but please don't close this application until upload message popups.""Upload processing..", MsgBox.Buttons.OK, MsgBox.Icon.Info, MsgBox.AnimateStyle.FadeIn);  
  50.   
  51.             btBrowse.Enabled = false;  
  52.   
  53.             //Starts the actual work - triggerrs the "DoWork" event  
  54.             bw.RunWorkerAsync();  
  55.   
  56.             //InsertExcelRecords();                      
  57.         }   
Output



Before importing, the screen will look, as shown below.



After importing, the screen will look, as shown below.