HOW TO UPLOAD BULK EXCEL DATA IN SQL SERVER USING C#

HOW TO UPLOAD BULK EXCEL DATA IN SQL SERVER IN WINDOWS FORM USING C#

     In this post, I am going to explain and demonstrate how to upload bulk excel data in sql server in windows form using c#.NET.

First of all create Excel workbook as shown in image below and insert some data into it. Please design excel sheet like whatever I shown in image

I want to copy this data into a SQL Server Database Table, called “tbl_DataFromExcelFiles”, with the same schema. Design your tables in database like this.

Now, design your windows form as per your requirements

Double click on brows button and write this code.

OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = @"C:\";

            openFileDialog1.RestoreDirectory = true;

            openFileDialog1.Title = "Browse Excel Files";

            openFileDialog1.Filter = "Excel files (*.xls or *.xlsx)|*.xls;*.xlsx";

            openFileDialog1.CheckFileExists = true;

            openFileDialog1.CheckPathExists = true;

            openFileDialog1.RestoreDirectory = true;

            // openFileDialog1.ShowDialog();

            if (openFileDialog1.ShowDialog() == DialogResult.OK)

            {

                txtPath.Text = openFileDialog1.FileName;

            }

Double click in on Upload button and write this code.

if (con.State == ConnectionState.Closed)

                {

                    con.Open();

                }

                //file upload path

                Cursor.Current = Cursors.WaitCursor;

                string path = txtPath.Text;

                //Create connection string to Excel work book

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

                //Create Connection to Excel work book

                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                //Create OleDbCommand to fetch data from Excel

                OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);

                excelConnection.Open();

                OleDbDataReader dReader;

                dReader = cmd.ExecuteReader();

                SqlBulkCopy sqlBulk = new SqlBulkCopy(con);

                //Give your Destination table name

                sqlBulk.DestinationTableName = "tbl_DataFromExcelFiles";

                sqlBulk.WriteToServer(dReader);

                excelConnection.Close();

                Cursor.Current = Cursors.Default;

                MessageBox.Show("Successfull");

Complete Code form1.cs Files:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

using System.Data.SqlClient;

namespace UploadBulkExcelDataInSQLSERVER

{

    public partial class Form1 : Form

    {

string ConnectionString = "Data Source=MY_PC;Initial Catalog=Test;User ID=sa;Password=abc";

        SqlConnection con;

        public Form1()

        {

            InitializeComponent();

            con = new SqlConnection(ConnectionString);

        }

        private void BtnBrowse_Click(object sender, EventArgs e)

        {

            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = @"C:\";

            openFileDialog1.RestoreDirectory = true;

            openFileDialog1.Title = "Browse Excel Files";

            openFileDialog1.Filter = "Excel files (*.xls or *.xlsx)|*.xls;*.xlsx";

            openFileDialog1.CheckFileExists = true;

            openFileDialog1.CheckPathExists = true;

            openFileDialog1.RestoreDirectory = true;

            // openFileDialog1.ShowDialog();

            if (openFileDialog1.ShowDialog() == DialogResult.OK)

            {

                txtPath.Text = openFileDialog1.FileName;

            }

        }

        private void BtnUpload_Click(object sender, EventArgs e)

        {

            try

            {

                if (con.State == ConnectionState.Closed)

                {

                    con.Open();

                }

                //file upload path

                Cursor.Current = Cursors.WaitCursor;

                string path = txtPath.Text;

                //Create connection string to Excel work book

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

                //Create Connection to Excel work book

                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                //Create OleDbCommand to fetch data from Excel

                OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);

                excelConnection.Open();

                OleDbDataReader dReader;

                dReader = cmd.ExecuteReader();

                SqlBulkCopy sqlBulk = new SqlBulkCopy(con);

                //Give your Destination table name

                sqlBulk.DestinationTableName = "tbl_DataFromExcelFiles";

                sqlBulk.WriteToServer(dReader);

                excelConnection.Close();

                Cursor.Current = Cursors.Default;

                MessageBox.Show("Successfull");

            }

            catch (Exception exp)

            {

                MessageBox.Show(exp.Message.ToString());

            }

            finally

            {

                con.Close();

            }

        }

    }

}

Run our application and Select Excel Files and click on Upload Button.

download Source Code: Click Here

Written by Ravi Kumar Soni