Dear develpoers,
I am trying to find the differences between Imported Excel File and gridView data that 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=RCADEVC;USER ID=OUD;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,