Import Excel Data Into SQL Table Using SQLBulkCopy In C#

In this article we will see how to import Excel data to Sql Server DataTable using SqlBulkCopy Class. So let's create this Windows Forms application:

Then create an Sql Server database and name it Database1 and add a DataTable Employee.

Excel file:

Now let's move to code, add these directives and declare the SqlConnection:

using System.Data.OleDb;
using System.Data.SqlClient;
.
.
.
SqlConnection sqlconn = new SqlConnection(WindowsFormsApplication5.Properties.Settings.Default.Database1ConnectionString);

BrowseButton click event:

private void BrowseButton_Click(object sender, EventArgs e)
{
    OpenFileDialog od = new OpenFileDialog();
    od.Filter = "Excell|*.xls;*.xlsx;";
    DialogResult dr = od.ShowDialog();
    if (dr == DialogResult.Abort)
        return;
    if (dr == DialogResult.Cancel)
        return;
    textBox1.Text = od.FileName.ToString();
}

ImportDataFromExcel() function:

public void ImportDataFromExcel(string excelFilePath)
{
    string ssqltable = "Employee";
    string myexceldataquery = "select Id,Name,Address from [Feuil1$]";
    try
    {
        string sexcelconnectionstring = @"provider=microsoft.ACE.OLEDB.12.0;data source=" + excelFilePath +
        ";extended properties=" + "\"excel 12.0;hdr=yes;\"";
        sqlconn.Open();
        OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
        OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
        oledbconn.Open();
        OleDbDataReader dr = oledbcmd.ExecuteReader();
        SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconn);
        bulkcopy.DestinationTableName = ssqltable;
        while (dr.Read())
        {
            bulkcopy.WriteToServer(dr);
        }
        dr.Close();
        oledbconn.Close();
        MessageBox.Show( "File imported into sql server.");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message.ToString());
    }
}

UploadButton click event:

private void UploadButton_Click(object sender, EventArgs e)
{
    ImportDataFromExcel(textBox1.Text);
}