How to upload Excel file to MS SQL Server Database In C#.NET

Today i'm going to show you how to upload Excel file to MS SQL Server Database. If you already have data in MS Excle file just create the your sql table based on Excel sheet columns. In my case use this Excel file to upload data to the database. Based on my Excel file database should be looks like below one.

CREATE DATABASE testDb

use testDb

CREATE TABLE tStudent 
(
STUDENT VARCHAR(64),
ROLLNO VARCHAR(16),
COURSE VARCHAR(32)
)

Execute the above queries in the MS Sql Server. After that create a Windows Form Application that looks like below one.

Use below ID 's for the Buttons And Text Boxes

TextBox ID - : txtPath
Browser Button ID - : btnBrowse
Add To Database Button ID - : btnAdd

 After that Double Click on the OpenFileDialog tool in the toolBox. Next Double Click on the Browse Button and add below code to the the button click. This code provide the Dialog Box to select corresponding excel sheet from your machine.


     if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtPath.Text = openFileDialog1.FileName;
            }

After that add below name spaces to the source code

using System.Data.OleDb;
using System.Data.SqlClient;

Next Create a method call "importdatafromexcel". It's should be looks like below one.

  public void importdatafromexcel(string excelfilepath)
        {
       
            string myexceldataquery = "select student,rollno,course from [Sheet1$]";

            try
            {

                //create our connection strings
                string excelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";Extended Properties=Excel 12.0;";

                //Path of the MS SQL server
                string ssqlconnectionstring = "Add Your Connection String";
                   

                //series of commands to bulk copy data from the excel file into our sql table
                OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring);
                OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
                oledbconn.Open();
                OleDbDataReader dr = oledbcmd.ExecuteReader();


                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ssqlconnectionstring))                     
                {

                    bulkCopy.DestinationTableName = "tStudent";
                        

                    try
                    {
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(dr);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                   
                        oledbconn.Close();
                        MessageBox.Show("Successfully Uploaded to the database");
                    }
                }
               
              
            }
            catch (Exception ex)
            {
                 MessageBox.Show(ex.Message);
            }
        }

Then Double Click on the Add To Database button and call the importdatafromexcel method by providing the File Path of the excel file.

  importdatafromexcel(txtPath.Text); // Path of the Excel file

Now simply run your project. The completed source code you can download from here