title: Merged DataRows are not Updated in the database but Added DataRows are. Why?
body: <p>I'm currently trying to read a big bulk of excel files which are stored in a datatable called total and then i need to save this to a access database, it works as it is now but i'm afraid it will be very inefficient with the amount of excel files that needs to be read increase.</p>
  
  <p>I found this <a href="http://www.codeproject.com/Tips/810596/Csharp-VB-NET-Cplusplus-CLI-Create-read-and-write" rel="nofollow">article</a> and used that as a base for my writing function.</p>
  
  <pre><code>        private void writeAccdb()
      {
          if (File.Exists(saveFileDialog1.FileName))
              File.Delete(saveFileDialog1.FileName);
          OleDbConnection conn;
          OleDbDataAdapter adapter;
  
          ADOX.Catalog cat = new ADOX.Catalog();
          try
          {
              cat.Create("Provider=Microsoft.ACE.OLEDB." + verFound + ".0;Data Source=" + saveFileDialog1.FileName);
          }
          catch
          {
              return;
          }
          cat = null;
          try
          {
              conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB." + verFound + ".0;Data Source=" + saveFileDialog1.FileName);
              conn.Open();
          }
          catch
          {
              return;
          }
          try
          {
              using (OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Table_1] ([accdb_id] COUNTER PRIMARY KEY, [VALUE] DOUBLE, [TEXT] MEMO);", conn))
              {
                  cmd.ExecuteNonQuery();
              }
          }
          catch (Exception ex) { if (ex != null) ex = null;
          }
  
          adapter = new OleDbDataAdapter("SELECT * FROM [Table_1]", conn);
          OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
          DataTable dtMain = new DataTable();
          adapter.Fill(dtMain);
  
  
  
          //Slow process
          foreach (DataRow row in total.Rows)
          {
              dtMain.Rows.Add(row.ItemArray);
          }
          //Slow process
  
  
  
          adapter.Update(dtMain);
          conn.Close();
      }
  </code></pre>
  
  <p>I have tried using .Merge() and importrow but i always end up with a 180kb file only containing the column names and what type they hold but when i inspect dtmain it does hold the variables that i got from total, but when i itemarray it works properly.</p>
  
  <p>While I'm already asking about this matter, when a file is created, it says that the program is still using it until i close the program, but as far as i can see everything is closed that interacts with the file, i tried using dispose on all variables but it still remains, what could be the issue here?</p>
  
  <p><strong>edit</strong></p>
  
  <p>So my main question is why dtMain when merged with total doesn't work, but when each line is individually added it does?</p>
  
  <p><strong>edit 2</strong></p>
  
  <p>The user chooses a directory from which they want to import all excel files </p>
  
  <p>total is set as a new datatable and all columns are populated.</p>
  
  <p>Iteration over all excel files that were found in the directory are read using</p>
  
  <pre><code>        private OleDbConnection getExcelFile(string file)
      {
          OleDbConnection conn;
          if (verFound != "")
              return new OleDbConnection("Provider=Microsoft.ACE.OLEDB." + verFound + ".0;Data Source=" + file + ";Extended Properties='Excel " + verFound + ".0 XML;HDR=YES;';");
          foreach(string v in versions){
              try{
                  conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB." + v + ".0;Data Source=" + file + ";Extended Properties='Excel " + v + ".0 XML;HDR=YES;';");
                  conn.Open();
                  conn.Close();
                  verFound = v;
                  return conn;
              } catch {
                  log("Version " + v + " är ej korrekt.");
              }
          }
          return null;
      }
  </code></pre>
  
  <p>Then i try to merge the excel files data with total, but if the structure is incorrect it's ignored.</p>
  
  <p>Once all files have been merged a datagridview gets total as datasource.</p>
  
  <p>Now the user will have the option to remove rows depending on specific criterias.</p>
  
  <p>And at last the save process, so in whole it's just a straight import with a option to exclude rows.</p>
  
  <p>Any help would be much appreciated.</p>