Import Excel Data Into SQL Table Using SQLBulkCopy In C#

SqlConnection sqlConnection = new SqlConnection();  
sqlConnection.ConnectionString = "server = VSBS01; database = dbHRVeniteck; User ID = sa; Password = veniteck@2016"; //Create DataBase Connection Details
con.Open(); //Open DataBase conection
objbulk.WriteToServer(Exceldt); //inserting Datatable Records to DataBase con.Close(); //Close DataBase conection
MessageBox.Show("Data has been Imported successfully.", "Imported", MessageBoxButtons.OK, MessageBoxIcon.Information);  

Full function code is given below.

private void InsertExcelRecords()  
{  

    try  
    {  
         
      //  ExcelConn(_path);  
        string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", _path);  
        Econ = new OleDbConnection(constr);  
        string Query = string.Format("Select [Employee Name],[DOB],[Email],[Mobile] FROM [{0}]", "Sheet1$");  
        OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
        Econ.Open();  

        DataSet ds = new DataSet();  
        OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
        Econ.Close();  
        oda.Fill(ds);  
        DataTable Exceldt = ds.Tables[0];  

        for (int i = Exceldt.Rows.Count - 1; i >= 0; i--)  
        {  
            if (Exceldt.Rows[i]["Employee Name"] == DBNull.Value || Exceldt.Rows[i]["Email"] == DBNull.Value )  
            {  
                Exceldt.Rows[i].Delete();  
            }  
        }  
        Exceldt.AcceptChanges();  
        //creating object of SqlBulkCopy      
        SqlBulkCopy objbulk = new SqlBulkCopy(con);  
        //assigning Destination table name      
        objbulk.DestinationTableName = "Student";  
        //Mapping Table column    
        objbulk.ColumnMappings.Add("[Employee Name]", "Name");  
        objbulk.ColumnMappings.Add("DOB", "DOB");  
        objbulk.ColumnMappings.Add("Email", "Email");  
        objbulk.ColumnMappings.Add("Mobile", "Mob");  

        //inserting Datatable Records to DataBase   
        SqlConnection sqlConnection = new SqlConnection();  
        sqlConnection.ConnectionString = "server = VSBS01; database = dbHRVeniteck; User ID = sa; Password = veniteck@2016"; //Connection Details    
        con.Open();  
        objbulk.WriteToServer(Exceldt);  
        con.Close();  
        MessageBox.Show("Data has been Imported successfully.", "Imported", MessageBoxButtons.OK, MessageBoxIcon.Information);  

    }  
    catch (Exception ex)  
    {  
        MessageBox.Show(string.Format("Data has not been Imported due to :{0}", ex.Message), "Not Imported", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
        pb1.Visible = false;  
        txtpath.Text = "";  
        btBrowse.Enabled = true;  
        label1.Visible = false;  
    }
}

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.

btUpload.Visible = false;

Check whether the task is running or not, if it is running, then return from background worker task.

if (bw.IsBusy)  
{  
    return;  
} 

To start or stop the background worker, use stopwatch, as shown below.

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.

bw.DoWork += (bwSender, bwArg) =>  
{       
    sWatch.Start();         //To start Watching  
    InsertExcelRecords(); //Function which created to export data  
};

After completing this task, you can write in runworkercomplete event in background worker, as shown below.

bw.RunWorkerCompleted += (bwSender, bwArg) =>  
{  
    //now you're back in the UI thread you can update the form  
    //remember to dispose of bw now                 

    sWatch.Stop();//To start Watching  
    //work is done, no need for the stop button now...  
    pb1.Visible = false; //To disable gif image  
    txtpath.Text = "";  
    btBrowse.Enabled = true;  
    label1.Visible = false;  
    bw.Dispose(); //To disposing background worker  
};  

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.

pb1.Visible = true;  
label1.Visible = true;  
btBrowse.Enabled = false;  

//Starts the actual work - triggerrs the "DoWork" event  
bw.RunWorkerAsync();   

The full code of upload button is shown below.

private void btUpload_Click(object sender, EventArgs e)  
{  
    btUpload.Visible = false;  
    _path = txtpath.Text;  
    if (txtpath.Text == "" || !txtpath.Text.Contains("EmployeeList.xlsx"))  
    {  
        MessageBox.Show("Please Browse EmployeeList.xlsx to upload", "", MessageBoxButtons.OK, MessageBoxIcon.Information);  
        txtpath.Text = "";  
        btUpload.Visible = false;  
        return;  
    }  
    if (bw.IsBusy)  
    {  
        return;  
    }  

    System.Diagnostics.Stopwatch sWatch = new System.Diagnostics.Stopwatch();  
    bw.DoWork += (bwSender, bwArg) =>  
    {  
        //what happens here must not touch the form  
        //as it's in a different thread          
        sWatch.Start();  
        InsertExcelRecords();  
    };  

    bw.ProgressChanged += (bwSender, bwArg) =>  
    {  
        //update progress bars here  
    };  

    bw.RunWorkerCompleted += (bwSender, bwArg) =>  
    {  
        //now you're back in the UI thread you can update the form  
        //remember to dispose of bw now                 

        sWatch.Stop();  

        //work is done, no need for the stop button now...  
        pb1.Visible = false;  
        txtpath.Text = "";  
        btBrowse.Enabled = true;  
        label1.Visible = false;  
        bw.Dispose();  
    };  

    //lets allow the user to click stop  
    pb1.Visible = true;  
    label1.Visible = true;  
    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);  

    btBrowse.Enabled = false;  

    //Starts the actual work - triggerrs the "DoWork" event  
    bw.RunWorkerAsync();  

    //InsertExcelRecords();                      
}

Output

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

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