Import data from MS Excel to SQL Server in C#

In many occasions, we need to get data from Microsoft Excel to SQL Server Database for displaying, calculating and manipulating data when developing .NET Applications. So in this case, you can follow this post to import data from MS Excel file.


 
At the beginning create a database in SQL Server using Microsoft SQL Server Management Studio in my case I created my database name as “test”.  Design and create the table using following Query.

 CREATE TABLE student
(
Student_id int,
first_name VARCHAR(255),
last_name VARCHAR(255),

); 

Create MS Excel file and save anywhere ( in my case D:\\text.xls) as shown in figure below.

 Create a new Windows Form Application Project in Visual Studio, add a button and DataGridView. 

Generate event by double clicking the button, under this method we can write the code for Import Excel File.
Add these two namespaces to your class file.
using System.Data.OleDb;
using System.Data.SqlClient;
Add the below code into the event handling method of the button.


 
private void btnImportExcelFile_Click(object sender, EventArgs e)
        {
            //database name
            string table = "student";
            // Excel sheet name
            string excelQuery = "select * from [Sheet1$]";
            try
            {
                //create  connection strings
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\test.xls;Extended Properties=\"Excel 8.0;HDR=Yes;\";";
                string sqlConnectionstring = "Server=localhost\\sqlexpress;Database=test;User Id=sa;Password=abc123";
                //To clear the prevois data in the database
                string resetQuery = "delete from " + table;
                SqlConnection connection = new SqlConnection(sqlConnectionstring);
                SqlCommand cmd = new SqlCommand(resetQuery, connection);
                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
                //copy data from the excel file
                OleDbConnection con = new OleDbConnection(connectionString);
                OleDbCommand cmdOLEDB = new OleDbCommand(excelQuery, con);
                con.Open();
                OleDbDataReader dataReader = cmdOLEDB.ExecuteReader();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlConnectionstring);
                bulkcopy.DestinationTableName = table;
                while (dataReader.Read())
                {
                    bulkcopy.WriteToServer(dataReader);
                }

                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            fillDataGridView();
        }

// fill the data from database to datagridview
private void fillDataGridView() {
            string connectionString = "Server=localhost\\sqlexpress;Database=test;User Id=sa;Password=abc123;";
            string sql = "SELECT * FROM student";
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            connection.Open();
            dataadapter.Fill(ds, "test");
            connection.Close();
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "test";
        }
Test your project and see the result also checking in the Database.

Do not forget to leave your comments below.