1
Answer

Find Difference of Data Between imported Excel & oracle Database

Real Milan

Real Milan
3y
459
1
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:
  1. private static DataSet OracleDataImported()    
  2. {    
  3.     String strConnString;    
  4.     strConnString = "DATA SOURCE=DatabaseName;USER ID=UserName;PASSWORD=123;";    
  5.     var conn = new OracleConnection(strConnString);    
  6.     String strSQL;    
  7.     
  8.     OracleCommand cmd = conn.CreateCommand();    
  9.     strSQL = "select Name,Password from OUD.USERDATA";    
  10.     cmd = new OracleCommand(strSQL, conn);    
  11.     cmd.CommandType = CommandType.Text;    
  12.     
  13.     OracleDataAdapter da = new OracleDataAdapter();    
  14.     da = new OracleDataAdapter(cmd);    
  15.     DataSet ds1 = new DataSet();    
  16.     da.Fill(ds1);    
  17.     
  18.     return ds1;    
  19. }  
ImportedExcelFileData code:
  1. protected void ImportedExcelFileData(object sender, EventArgs e)    
  2. {    
  3.     // CHECK IF A FILE HAS BEEN SELECTED.    
  4.     if ((FileUpload.HasFile))    
  5.     {    
  6.         if (!Convert.IsDBNull(FileUpload.PostedFile) &    
  7.                 FileUpload.PostedFile.ContentLength > 0)    
  8.         {    
  9.             // SAVE THE SELECTED FILE IN THE ROOT DIRECTORY.    
  10.             FileUpload.SaveAs(Server.MapPath(".") + "\\" + FileUpload.FileName);    
  11.     
  12.             // SET A CONNECTION WITH THE EXCEL FILE.    
  13.             OleDbConnection myExcelConn = new OleDbConnection    
  14.                 ("Provider=Microsoft.ACE.OLEDB.12.0; " +    
  15.                     "Data Source=" + Server.MapPath(".") + "\\" + FileUpload.FileName +    
  16.                     ";Extended Properties=Excel 12.0;");    
  17.             try    
  18.             {    
  19.                 myExcelConn.Open();    
  20.     
  21.                 // GET DATA FROM EXCEL SHEET.    
  22.     
  23.                 // READ THE DATA EXTRACTED FROM THE EXCEL FILE.    
  24.                 OleDbDataAdapter oleDbDataAdapter;    
  25.                 oleDbDataAdapter = new OleDbDataAdapter("select rtrim(ltrim(name)) as [Name],rtrim(ltrim(password)) as [Password] from [Sheet1$]", myExcelConn);    
  26.                 oleDbDataAdapter.TableMappings.Add("Table", "ExcelTable");    
  27.                 DataSet dataSet = new DataSet();    
  28.                 oleDbDataAdapter.Fill(dataSet);    
  29.     
  30.                 // FINALLY, BIND THE EXTRACTED DATA TO THE GRIDVIEW.    
  31.                 DisplayExcelData.DataSource = dataSet;    
  32.                 DisplayExcelData.DataBind();    
  33.     
  34.                 lblConfirm.Text = "DATA IMPORTED TO THE GRID, SUCCESSFULLY.";    
  35.                 lblConfirm.Attributes.Add("style", "color:green");    
  36.             }    
  37.             catch (Exception ex)    
  38.             {    
  39.                 // SHOW ERROR MESSAGE, IF ANY.    
  40.                 lblConfirm.Text = ex.Message;    
  41.                 lblConfirm.Attributes.Add("style", "color:red");    
  42.             }    
  43.             finally    
  44.             {    
  45.                 // CLEAR.    
  46.                 myExcelConn.Close(); myExcelConn = null;    
  47.             }    
  48.         }    
  49.     }    
  50. }    
Button to find the differences code:
  1. protected void calculate(object sender, EventArgs e)    
  2. {    
  3.     //Get data from UserData Table from DB    
  4.     var ds = OracleDataImported();    
  5.     //Assign the data to the first gridview    
  6.     DisplayDBData.DataSource = ds;    
  7.     DisplayDBData.DataBind();    
  8.     
  9.     DataSet dataSet;    
  10.     //Get data from Excel data source and bind it to second gridview    
  11.     var oleDbConnection = ImportedExcelFileData(out dataSet);    
  12.     DisplayExcelData.DataSource = dataSet.Tables[0];    
  13.     DisplayExcelData.DataBind();    
  14.     oleDbConnection.Close();    
  15.     
  16.     //Find the difference of data and bind the data to third gridview    
  17.     var dtAll = GetDataDifference(ds, dataSet);    
  18.     DisplayDifferenceData.DataSource = dtAll;    
  19.     DisplayDifferenceData.DataBind();    
  20. }  
How to do it?
 
Please, assist ..
Regards,
Answers (1)
Next Recommended Forum