Read excel sheet and compare with sql table

////////////////////////////////////////////////////////////////////////

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
  <connectionStrings>
          <!--<add name = "constr" connectionString = "Data Source=.\SQL2008R2;Database=PersonsDB;User Id=mudassar;password=xxxxx"/>-->
          <add name = "constr" connectionString = "Data Source=.;Database=Test;Integrated Security=true;"/>
          <add name = "Excel03ConString" connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
          <add name = "Excel07+ConString" connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>

</configuration>

//////////////////////////////////////////////////////////////////////////////////

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }


    protected void Upload(object sender, EventArgs e)
    {
        //Upload and save the file
        string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(excelPath);
        DataTable sqlTable = new DataTable();
        DataTable dtExcelData = new DataTable();
        string conString = string.Empty;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;

        }
        conString = string.Format(conString, excelPath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();


            //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
            dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
            new DataColumn("Name", typeof(string)),
            new DataColumn("Salary",typeof(decimal)) });

            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();

            //***************Code to get the SQL Table in DataTable Start*************//
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT *  From tblPersons", conn))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(sqlTable);
                    }
                }
            }
            //***************Code to get the SQL Table in DataTable End*************//

            if (sqlTable.Columns.Count == dtExcelData.Columns.Count)
            {
                int columnCount = sqlTable.Columns.Count;
                int counter = 0;
                foreach (DataColumn col in sqlTable.Columns)
                {
                    foreach (DataColumn col1 in dtExcelData.Columns)
                    {
                        if (col.DataType == col1.DataType)
                        {
                            counter++;
                        }
                    }
                }
                if (columnCount == counter)
                {
                    string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            //Set the database table name
                            sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

                            //[OPTIONAL]: Map the Excel columns with that of the database table
                            sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
                            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                            sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }
                    }
                }
            }
        }
    }


}


//////////////////////////////aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button Text="Upload" OnClick = "Upload" runat="server" />
    </div>
    </form>
</body>
</html>

///////////////////////////////////////

defaults2

/////////////////////////////

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    private DataTable GetSqlTable()

    {

        DataTable dt = new DataTable();

        DataColumn idColumn = new DataColumn("id", typeof(string));

        DataColumn nameColumn = new DataColumn("name", typeof(string));

        DataColumn addressColumn = new DataColumn("address", typeof(string));

        dt.Columns.Add(idColumn);

        dt.Columns.Add(nameColumn);

        dt.Columns.Add(addressColumn);

        DataRow row = dt.NewRow();

        row[idColumn.ColumnName] = "1";

        row[nameColumn.ColumnName] = "c";

        row[addressColumn.ColumnName] = "c1";

        dt.Rows.Add(row);

        row = dt.NewRow();

        row[idColumn.ColumnName] = "5";

        row[nameColumn.ColumnName] = "h";

        row[addressColumn.ColumnName] = "h1";

        dt.Rows.Add(row);

        return dt;  //for testing, Instead connecting to database, I created the datatable in code-behind dynamically.

    }

    private DataTable ReadExcelTable()

    {

        string filePath = Server.MapPath("upload/test.xlsx");

        //string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", filePath);

        string connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filePath);

        //  constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath); 

        DataTable dt = null;

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        using (DbConnection connection = factory.CreateConnection())

        {

            connection.ConnectionString = connectionString;

            using (DbCommand command = connection.CreateCommand())

            {

                // Cities$ comes from the name of the worksheet

                //command.CommandText = "SELECT * FROM [Sheet1$]";

                command.CommandText = "SELECT * FROM [Sheet1$]";

                connection.Open();

                using (DbDataAdapter adapter = factory.CreateDataAdapter())

                {

                    adapter.SelectCommand = command;

                    DataSet ds = new DataSet();

                    adapter.Fill(ds);

                    dt = ds.Tables[0];

                }

            }

        }

        return dt;

    }

    private void CheckInsertAndUpdateColumnOfExcel()

    {

        DataTable excelTable = ReadExcelTable();

        DataTable sqlTable = GetSqlTable();

        DataRow[] excelRowsList = excelTable.Select();

        DataRow[] sqlTableList = sqlTable.Select();

        string[] oldSqlIdList = new string[sqlTableList.Count()];

        int index = 0;

        foreach (DataRow row in sqlTableList)

        {

            oldSqlIdList[index] = row[0].ToString();

            index++;

        }

        var list = from x in excelRowsList

                   join s in sqlTableList on x[0].ToString() equals s[0].ToString()

                   select x;

        var list2 = from x in excelRowsList

                    where !oldSqlIdList.Contains(x[0].ToString())

                    select x;

        DataRow[] updateColumn = list.ToArray();  //get the update column

        DataRow[] insertColumn = list2.ToArray();  //get the insert column

        List<string> updateIdList = new List<string>();

        List<string> insertIdList = new List<string>();

        foreach (DataRow row in updateColumn)

        {

            updateIdList.Add(row[0].ToString());

        }

        foreach (DataRow row in insertColumn)

        {

            insertIdList.Add(row[0].ToString());

        }

        Response.Write("the rows id need to update are " + string.Join(",", updateIdList.ToArray()));

        Response.Write("<br/>");

        Response.Write("the rows id need to insert are " + string.Join(",", insertIdList.ToArray()));

    }

    protected void btnCompare_Click(object sender, EventArgs e)

    {

        CheckInsertAndUpdateColumnOfExcel();

    }

}

/////////////////////////////////////////////////////////////////////////////////

default1

///////////////

 private DataTable GetSqlTable()

    {

        DataTable dt = new DataTable();

        DataColumn idColumn = new DataColumn("id", typeof(string));

        DataColumn nameColumn = new DataColumn("name", typeof(string));

        DataColumn addressColumn = new DataColumn("address", typeof(string));

        dt.Columns.Add(idColumn);

        dt.Columns.Add(nameColumn);

        dt.Columns.Add(addressColumn);

        DataRow row = dt.NewRow();

        row[idColumn.ColumnName] = "1";

        row[nameColumn.ColumnName] = "c";

        row[addressColumn.ColumnName] = "c1";

        dt.Rows.Add(row);

        row = dt.NewRow();

        row[idColumn.ColumnName] = "5";

        row[nameColumn.ColumnName] = "h";

        row[addressColumn.ColumnName] = "h1";

        dt.Rows.Add(row);

        return dt;  //for testing, Instead connecting to database, I created the datatable in code-behind dynamically.

    }

    private DataTable ReadExcelTable()

    {

        string filePath = Server.MapPath("upload/test.xlsx");

        //string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", filePath);

        string connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filePath);

        //  constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath); 

        DataTable dt = null;

        DbProviderFactory factory =  DbProviderFactories.GetFactory("System.Data.OleDb");

        using (DbConnection connection = factory.CreateConnection())

        {

            connection.ConnectionString = connectionString;

            using (DbCommand command = connection.CreateCommand())

            {

                // Cities$ comes from the name of the worksheet

                //command.CommandText = "SELECT * FROM [Sheet1$]";

                command.CommandText = "SELECT * FROM [Sheet1$]";

                connection.Open();

                using (DbDataAdapter adapter = factory.CreateDataAdapter())

                {

                    adapter.SelectCommand = command;

                    DataSet ds = new DataSet();

                    adapter.Fill(ds);

                    dt = ds.Tables[0];

                }

            }

        }

        return dt;

    }

    private void CheckInsertAndUpdateColumnOfExcel()

    {

        DataTable excelTable = ReadExcelTable();

        DataTable sqlTable = GetSqlTable();

        DataRow[] excelRowsList = excelTable.Select();

        DataRow[] sqlTableList = sqlTable.Select();

        string[] oldSqlIdList = new string[sqlTableList.Count()];

        int index = 0;

        foreach (DataRow row in sqlTableList)

        {

            oldSqlIdList[index] = row[0].ToString();

            index++;

        }

        var list = from x in excelRowsList

                   join s in sqlTableList on x[0].ToString() equals s[0].ToString()

                   select x;

        var list2 = from x in excelRowsList

                    where !oldSqlIdList.Contains(x[0].ToString())

                    select x;

        DataRow[] updateColumn = list.ToArray();  //get the update column

        DataRow[] insertColumn = list2.ToArray();  //get the insert column

        List<string> updateIdList = new List<string>();

        List<string> insertIdList = new List<string>();

        foreach (DataRow row in updateColumn)

        {

            updateIdList.Add(row[0].ToString());

        }

        foreach (DataRow row in insertColumn)

        {

            insertIdList.Add(row[0].ToString());

        }

        Response.Write("the rows id need to update are " + string.Join(",", updateIdList.ToArray()));

        Response.Write("<br/>");

        Response.Write("the rows id need to insert are " + string.Join(",", insertIdList.ToArray()));

    }

    protected void btnCompare_Click(object sender, EventArgs e)

    {

        CheckInsertAndUpdateColumnOfExcel();

    }