Bulk Upload Excel to SQL Server in C#.Net

One of the biggest issues is to load files or other type of formats in a standard database, before doing something with the data. Although this can be achieve using an automated way, sometimes business requirements leads to creating a specific workflow for data loading. This is one approach to the workflow:

1. Create your form for the upload

2. Create your DB Structure

3. Create your connection string to communicate with the DB

App

<?xml version="1.0" encoding="utf-8" ?>
  <configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="prod" connectionString="server=serverName; database=dbName; UID=databaseUser;password=password;Trusted_Connection = False"/>
  </connectionStrings>
</configuration>

4. Create the Form structure to handle the excel reading as well sending the data to the DAO class for the bulk Upload

Form1

using ExcelDataReader;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TaskUploader
{
    public partial class Form1 : Form
    {
        String conn;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try {
            label2.Text = "";
            textBox1.ReadOnly = true;
            textBox1.Text = "No files selected";
            conn = ConfigurationManager.ConnectionStrings["prod"].ConnectionString;
            button2.Enabled = false;
            }
            catch (Exception ex)
            {
                label2.Text = "Error: " + ex.Message;
                throw new System.Exception("Error: " + ex.Message);
            }
        }


        private void button1_Click(object sender, EventArgs e)
        {

            try
            {
                openFileDialog1.Title = "Select Tasks";
                openFileDialog1.FileName = "";
                openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx";

                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    textBox1.Text = openFileDialog1.SafeFileName;
                    button2.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                label2.Text = "Error: " + ex.Message;
                throw new System.Exception("Error: " + ex.Message);

            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                button2.Enabled = false;
                using (FileStream fs = File.Open(openFileDialog1.FileName, FileMode.Open, FileAccess.Read))
                {
                    IExcelDataReader reader = ExcelReaderFactory.CreateReader(fs);
                    DataSet result = reader.AsDataSet();
                    TasksLocaleDao tasks = new TasksLocaleDao();
                    DataTable tasksColumns = tasks.getMetadata(conn);
                    tasks.truncateTable(conn);
                    if (tasks.insertTasks(tasksColumns, result, conn))
                    {
                        String nRows = tasks.countRows(conn);
                        MessageBox.Show("Transaction Completed: Inserted " + nRows + " rows.");
                    }
                    button2.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                label2.Text = "Error: " + ex.Message;
                throw new System.Exception("Error: " + ex.Message);
            }

        }
    }
}

5. Create your DAO class to save the data into the DB

DAO

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TaskUploader
{
    class TasksLocaleDao : ITasksLocale
    {

        string tableName = "TaskByLocale";

        public String countRows(string connectionString)
        {
            SqlConnection myConnection = null;
            SqlCommand sqlcomm = null;
            DataTable dt = new DataTable();

            try
            {
                using (myConnection = new SqlConnection())
                {
                    myConnection.ConnectionString = connectionString;
                    myConnection.Open();

                    using (sqlcomm = new SqlCommand())
                    {
                        sqlcomm.Connection = myConnection;

                        sqlcomm.CommandType = CommandType.StoredProcedure;

                        sqlcomm.CommandText = "TaskByLocale_CountTaskByLocale";

                        dt.Load(sqlcomm.ExecuteReader());
                    }
                }
            }
            catch (Exception e)
            {
                throw new System.Exception("No database access TaskByLocale__CountTaskByLocale -->" + e.Message);
            }
            finally
            {
                if (myConnection != null && myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return dt.Rows[0].ItemArray[0].ToString();
        }

        public DataTable getMetadata(string connectionString)
        {
            SqlConnection myConnection = null;
            SqlCommand sqlcomm = null;
            DataTable dt = new DataTable();

            try
            {
                using (myConnection = new SqlConnection())
                {
                    myConnection.ConnectionString = connectionString;
                    myConnection.Open();

                    using (sqlcomm = new SqlCommand())
                    {
                        sqlcomm.Connection = myConnection;

                        sqlcomm.CommandType = CommandType.StoredProcedure;
                        sqlcomm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar)).Value = this.tableName;

                        sqlcomm.CommandText = "TaskByLocale_GetTableMetadata";

                        dt.Load(sqlcomm.ExecuteReader());
                    }
                }
            }
            catch (Exception e)
            {
                throw new System.Exception("No database access TaskByLocale_GetTableMetadata -->" + e.Message);
            }
            finally
            {
                if (myConnection != null && myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return dt;
        }

        public void truncateTable(string connectionString)
        {
            SqlConnection myConnection = null;
            SqlCommand sqlcomm = null;

            try
            {
                using (myConnection = new SqlConnection())
                {
                    myConnection.ConnectionString = connectionString;
                    myConnection.Open();

                    using (sqlcomm = new SqlCommand())
                    {
                        sqlcomm.Connection = myConnection;

                        sqlcomm.CommandType = CommandType.StoredProcedure;

                        sqlcomm.CommandText = "TaskByLocale_TruncateTaskByLocale";

                        sqlcomm.ExecuteReader();
                    }
                }
            }
            catch (Exception e)
            {
                throw new System.Exception("No database access TaskByLocale_TruncateTaskByLocale -->" + e.Message);
            }
            finally
            {
                if (myConnection != null && myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

        }

        public Boolean insertTasks(DataTable tasksColumns, DataSet result, string connectionString)
        {
            SqlBulkCopy copy = new SqlBulkCopy(connectionString);

            try
            {
                ArrayList columns = new ArrayList();
                columns.Add(result.Tables[0].Rows[0].ItemArray.Select(x => x.ToString()).ToArray());
                result.Tables[0].Rows.RemoveAt(0);
                result.Tables[0].Columns.RemoveAt(1);


                copy.ColumnMappings.Add(0, 0);
                copy.ColumnMappings.Add(1, 1);
                copy.ColumnMappings.Add(2, 2);
                copy.ColumnMappings.Add(3, 3);
                copy.ColumnMappings.Add(4, 4);


                copy.DestinationTableName = this.tableName;
                copy.WriteToServer(result.Tables[0]);
            }
            catch (Exception e)
            {
                throw new System.Exception("Error: " + e.Message);
            }
            finally
            {
                if (copy != null) { copy.Close(); }
            }

            return true;
        }

    }
}