Upload Excel data in database using two tier architecture in ASP.Net using ClosedXML

Hi RivickJoe,

Please refer below sample.

Database

I have made use of the following table Customers with the schema as follows. 

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:FileUpload runat="server" ID="fuExcel" />
<asp:Button ID="btnSave" runat="server" OnClick="OnSave" Text="Save" />

Class Library

Namespaces

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

Logic

public class DataAccess
{
    public void Save(DataTable dt)
    {
        foreach (DataRow row in dt.Rows)
        {
            string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constring))
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@Name, @Country)", con))
                {
                    cmd.Parameters.AddWithValue("@Name", row["Name"].ToString());
                    cmd.Parameters.AddWithValue("@Country", row["Country"].ToString());
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
}

Code

Namespaces

using System.Data;
using ClosedXML.Excel;
using SolutionName;

C#

protected void OnSave(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    using (XLWorkbook workbook = new XLWorkbook(fuExcel.PostedFile.InputStream))
    {
        IXLWorksheet worksheet = workbook.Worksheet(1);
        bool firstRow = true;
        foreach (IXLRow row in worksheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
    }

    DataAccess da = new DataAccess();
    da.Save(dt);
}

Output