Find Difference of Data Between imported Excel & oracle Database

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,

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke5y ago
Hello,

While there many ways in which a problem can be solved, in scenarios like these, I prefer the database solution over others as it has native syntax to find differences.

For example, if you could manage to upload your Excel file data to the Oracle database of yours, you can just very well use full outer join as demonstrated below pseudo code:
select o.*, e.*, case when o.name is null then 'Missing in Oracle' when e.name is null then 'Missing in Excel' else 'Present in both' end Result
from OracleTable o
full outer join ExcelTable e on o.name = e.name
0