Find Difference of Data Between imported Excel & oracle Database

Question

Dear developers,

 

I am trying to find the differences between Imported Excel File and gridView data imported from Oracle Database. I am trying to put the data into DataSet, so I can compare between them.

 

OracleDataImported v.s ImportedExcelFileData.

 

OracleDataImported Code:

private static DataSet OracleDataImported()
{
    String strConnString;
    strConnString = "DATA SOURCE=DatabaseName;USER ID=UserName;PASSWORD=123;";
    var conn = new OracleConnection(strConnString);
    String strSQL;

    OracleCommand cmd = conn.CreateCommand();
    strSQL = "select Name,Password from OUD.USERDATA";
    cmd = new OracleCommand(strSQL, conn);
    cmd.CommandType = CommandType.Text;

    OracleDataAdapter da = new OracleDataAdapter();
    da = new OracleDataAdapter(cmd);
    DataSet ds1 = new DataSet();
    da.Fill(ds1);

    return ds1;
}

ImportedExcelFileData code:

protected void ImportedExcelFileData(object sender, EventArgs e)
{
    // CHECK IF A FILE HAS BEEN SELECTED.
    if ((FileUpload.HasFile))
    {
        if (!Convert.IsDBNull(FileUpload.PostedFile) &
                FileUpload.PostedFile.ContentLength > 0)
        {
            // SAVE THE SELECTED FILE IN THE ROOT DIRECTORY.
            FileUpload.SaveAs(Server.MapPath(".") + "\\" + FileUpload.FileName);

            // SET A CONNECTION WITH THE EXCEL FILE.
            OleDbConnection myExcelConn = new OleDbConnection
                ("Provider=Microsoft.ACE.OLEDB.12.0; " +
                    "Data Source=" + Server.MapPath(".") + "\\" + FileUpload.FileName +
                    ";Extended Properties=Excel 12.0;");
            try
            {
                myExcelConn.Open();

                // GET DATA FROM EXCEL SHEET.

                // READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                OleDbDataAdapter oleDbDataAdapter;
                oleDbDataAdapter = new OleDbDataAdapter("select rtrim(ltrim(name)) as [Name],rtrim(ltrim(password)) as [Password] from [Sheet1$]", myExcelConn);
                oleDbDataAdapter.TableMappings.Add("Table", "ExcelTable");
                DataSet dataSet = new DataSet();
                oleDbDataAdapter.Fill(dataSet);

                // FINALLY, BIND THE EXTRACTED DATA TO THE GRIDVIEW.
                DisplayExcelData.DataSource = dataSet;
                DisplayExcelData.DataBind();

                lblConfirm.Text = "DATA IMPORTED TO THE GRID, SUCCESSFULLY.";
                lblConfirm.Attributes.Add("style", "color:green");
            }
            catch (Exception ex)
            {
                // SHOW ERROR MESSAGE, IF ANY.
                lblConfirm.Text = ex.Message;
                lblConfirm.Attributes.Add("style", "color:red");
            }
            finally
            {
                // CLEAR.
                myExcelConn.Close(); myExcelConn = null;
            }
        }
    }
}

Button to find the differences code:

protected void calculate(object sender, EventArgs e)
{
    //Get data from UserData Table from DB
    var ds = OracleDataImported();
    //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 = ImportedExcelFileData(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();
}

How to do it?

 

Please, assist ..

Regards,