Find Difference of Data Between Two Data Sources

Context

One of the friends in C-sharpcorner has asked me for help displaying the difference of data from two different data sources. One is from a SQL Server Database and another one is from an Excel sheet. I thought it might be useful to post this as an article rather than sending a response to his message.

Problem

The user has got two sets of data sources. One is a table in SQL Server and another is an Excel sheet. So, we have 3 gridview controls in our web page. The first grid shows the data from a Database table and the second one shows the data from an Excel sheet and the third one should show the difference of the data between the two grid views.

Solution

Create a table in SQL Server as below:

CREATE TABLE [dbo].[UserData](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [Name] [varchar](50) NULL,

      [Password] [varchar](50) NULL,

 CONSTRAINT [PK_UserData] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY

Enter data in the preceding table. I have entered the following data in the table:
 

ID

Name

Password

1

abc

123

2

xyz

321

3

NNN

789

4

PPP

8998

Here is the stored procedure to get the data from the table:

ALTER PROCEDURE [dbo].[GetUserData]

AS     

    select Name,Password from userdata;

Now create an Excel sheet document. It has two columns, the same as in the table above. One is "Name" and the other one "password"; see:

Name

Password

abc

123

xyz

321

asas

856

jklo

854


Since we need to compare two sets of data, we need to ensure that the column names are the same so that we can compare them using the features available in C# for .NET 3.5.

Now let us build the UI for displaying the data from the two data sources and also the difference between the two data sets. My ASPX file looks as in the following:
 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridBind._Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Label runat="server" ID="lblDataDisplay">Display Data From Database</asp:Label>

        <asp:GridView runat="server" ID="DisplayDBData"> </asp:GridView>

   </div>

   <br/>

   <div>

       <asp:Label runat="server" ID="lblExcelDisplay">Display Data From Excel</asp:Label>

        <asp:GridView runat="server" ID="DisplayExcelData"></asp:GridView>

   </div>

   <br/>

   <div>

       <asp:Label runat="server" ID="Label1">Display Data Difference Between Database and Excel</asp:Label>

        <asp:GridView runat="server" ID="DisplayDifferenceData"></asp:GridView>

    </div>

  </form>

</body>

</html>

My Code behind file looks as in the following:
 

using System;

using System.Collections;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.OleDb;

 

namespace GridBind

{

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

    {

        /// <summary>

        /// Page Load method

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        protected void Page_Load(object sender, EventArgs e)

        {

            //Get data from UserData Table from DB

            var ds = GetUserData();

            //Assign the data to the first gridview

            DisplayDBData.DataSource = ds;

            DisplayDBData.DataBind();

 

            DataSet dataSet;

            //Get data from Excel data source and bind it to second gridview

            var oleDbConnection = GetDataFromExcel(out dataSet);

            DisplayExcelData.DataSource = dataSet.Tables[0];

            DisplayExcelData.DataBind();

            oleDbConnection.Close();

 

            //Find the difference of data and bind the data to third gridview

            var dtAll = GetDataDifference(ds, dataSet);

            DisplayDifferenceData.DataSource = dtAll;

            DisplayDifferenceData.DataBind();

 

 

        }

 

        /// <summary>

        /// Gets data difference between two data sets

        /// </summary>

        /// <param name="ds"></param>

        /// <param name="dataSet"></param>

        /// <returns>DataTable value</returns>

  private static DataTable GetDataDifference(DataSet ds, DataSet      dataSet)

        {

 

            //Query first dataset

IEnumerable<DataRow> query1 = from userData in ds.Tables[0].AsEnumerable()

            select userData;

 

            //Query second dataset

IEnumerable<DataRow> query2 = from userData in dataSet.Tables[0].AsEnumerable()

            select userData;

 

            //Create data tables and get the data from above queries

            DataTable userData1 = query1.CopyToDataTable();

            DataTable userData2 = query2.CopyToDataTable();

 

//Now use Except operator to find the data in first set and not in second

var userDataFirstSet = userData1.AsEnumerable().Except(userData2.AsEnumerable(),

                                                           DataRowComparer.Default);

            //Find data in second and not in first

var userDataSecondSet = userData2.AsEnumerable().Except(userData1.AsEnumerable(),

                                                                 DataRowComparer.Default);

            //Create a new data table and add new columns

            DataTable dtAll = new DataTable();

            dtAll.Columns.Add(new DataColumn("Name", typeof (string)));

            dtAll.Columns.Add(new DataColumn("Password", typeof (string)));

 

//Bind the data from both user data sets and add it to the datatable

            foreach (var dataRow in userDataFirstSet)

            {

                DataRow dr = dtAll.NewRow();

                dr[0] = dataRow[0];

                dr[1] = dataRow[1];

                dtAll.Rows.Add(dr);

            }

 

            foreach (var dataRow in userDataSecondSet)

            {

                DataRow dr = dtAll.NewRow();

                dr[0] = dataRow[0];

                dr[1] = dataRow[1];

                dtAll.Rows.Add(dr);

            }

            return dtAll;

        }

 

        /// <summary>

        /// Method to get data from excel document

        /// </summary>

        /// <param name="dataSet"></param>

        /// <returns></returns>

        private static OleDbConnection GetDataFromExcel(out DataSet dataSet)

        {

            OleDbConnection oleDbConnection;

//Since I am using excel 2007, I have to specify the OLEDB connector for 2007

            OleDbDataAdapter oleDbDataAdapter;

            oleDbConnection =

                new OleDbConnection(

@"provider=Microsoft.ACE.OLEDB.12.0;Data Source='c: \UserData.xlsx';Extended Properties=Excel 12.0;");

oleDbDataAdapter = new OleDbDataAdapter("select rtrim(ltrim(name)) as [Name],rtrim(ltrim(password)) as [Password] from [Sheet1$]", oleDbConnection);

            oleDbDataAdapter.TableMappings.Add("Table", "ExcelTable");

            dataSet = new DataSet();

            oleDbDataAdapter.Fill(dataSet);

            return oleDbConnection;

        }

 

        /// <summary>

        /// Method to get data from database

        /// </summary>

        /// <returns></returns>

        private static DataSet GetUserData()

        {

            SqlConnection sqlConnection = new SqlConnection();

            sqlConnection.ConnectionString =

"Data Source=(local);Initial Catalog=MyTestDB;Persist Security Info=True;User ID=userid;Password=password;Pooling=False";

            SqlCommand sqlCommand = new SqlCommand();

 

            sqlCommand.Connection = sqlConnection;

            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlCommand.CommandText = "GetUserData";

            sqlConnection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);

            DataSet ds = new DataSet();

 

            adapter.Fill(ds);

            return ds;

        }

    }

}

Now let us run our code and see the output:

DtSrc1.jpg

You can extend or change the program with more efficient code. This article was written for first-time programmers.