title: compare 2 datatables and result missing data
body: <p>I have 2 Datatables that are quite similar but have other columnnames. The first Datatable has 4 columns and about 7000 records. The second one has also 4 columns and about 37000 records. Now i want to Display the records that are missing in the first Datatable in a third datagridview. How should I do this? I know there are a lot of code in the Internet about this but nothing has worked.</p>
  
  <pre><code>using Microsoft.Win32; 
  using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Data.Linq;
  using System.Data.OleDb;
  using System.Data.Sql;
  using System.Data.SqlClient;
  using System.Drawing;
  using System.IO;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Windows;
  using System.Windows.Forms;
  using System.Windows.Input;
  
  
  
  
  namespace Compare
   {
    public partial class Form1 : Form
     {
      public Form1()
      {
          InitializeComponent();
      }
  
  
      DataTable ds;
      DataTable dt;
  
      public void btn_Click(object sender, EventArgs e)
      {
          SqlDataAdapter adapter = new SqlDataAdapter("select a.FALLZAHL, m.CODE, m.ANZ, m.STDAT From test m with (nolock) inner join test2 a with (nolock) on a.id = m.aid where m.STDAT &gt;= '2016-01-01' and m.STALT = '6363' order by a.FALLZAHL", "Server = ada; Database = sd;Trusted_Connection = True");
          ds = new DataTable(" ");
          adapter.Fill(ds);
          dataGridView1.DataSource = ds;
  
  
  
      }
  
      private void Excelsheet_Click(object sender, EventArgs e)
      {
  
          openFileDialog1.ShowDialog();
  
  
  
  
      }
  
      private void choose_Click(object sender, EventArgs e)
      {
          OpenFileDialog OpenFileDialog = new OpenFileDialog();
  
  
  
      }
      private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
  
      private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={0}'";
  
  
  
  
  
  
  
      public void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
      {
          string filePath = openFileDialog1.FileName;
  
          string extension = Path.GetExtension(filePath);
  
          string header = radioButton1.Checked ? "YES" : "NO";
  
          string conStr, sheetName, cells;
  
  
  
  
          conStr = string.Empty;
  
          switch (extension)
          {
  
  
  
              case ".xls": //Excel 97-03
  
                  conStr = string.Format(Excel03ConString, filePath, header);
  
                  break;
  
  
  
              case ".xlsx": //Excel 07
  
                  conStr = string.Format(Excel07ConString, filePath, header);
  
                  break;
          }
  
  
  
  
          using (OleDbConnection con = new OleDbConnection(conStr))
          {
  
              using (OleDbCommand cmd = new OleDbCommand())
              {
  
                  cmd.Connection = con;
                  con.Open();
  
                  System.Data.DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  
                  sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
  
  
  
  
                  con.Close();
  
  
  
  
  
  
  
              }
  
          }
  
          using (OleDbConnection con = new OleDbConnection(conStr))
          {
  
              using (OleDbCommand cmd = new OleDbCommand())
              {
  
                  using (OleDbDataAdapter oda = new OleDbDataAdapter())
                  {
  
                      dt = new DataTable();
  
                      cmd.CommandText = "SELECT * From [" + sheetName + "]";
  
                      cmd.Connection = con;
  
                      con.Open();
  
                      oda.SelectCommand = cmd;
  
                      oda.Fill(dt);
  
                      con.Close();
  
  
                      dataGridView2.DataSource = dt; 
  
  
                  }
  
              }
  
          }
      }
  
  
  
     private void dataGridView3_CellContentClick(object sender, DataGridViewCellEventArgs e)
      {
  
  
      }
  
  
      private void Form1_Load(object sender, EventArgs e)
      {
          /* dataGridView3.DataSource = from table1 in ds.AsEnumerable()
                        join table2 in dt.AsEnumerable() on table1.Field&lt;int&gt;("ColumnA") equals table2.Field&lt;int&gt;("ColumnA")
                        where table1.Field&lt;int&gt;("ColumnB") == table2.Field&lt;int&gt;("ColumnB") || table1.Field&lt;string&gt;("ColumnC") == table2.Field&lt;string&gt;("ColumnC") || table1.Field&lt;object&gt;("ColumnD") == table2.Field&lt;object&gt;("ColumnD")
                        select table1;
           dataGridView3.DataSource = from table1 in ds.AsEnumerable()
                        where !dataGridView3.Contains(table1)
                        select table1;
           */
      }
  
      public void CompareData()
      {
  
      }
  
      private void button1_Click(object sender, EventArgs e)
      {
          var dsRowCount = ds.AsEnumerable().Count();
          var dtRowCount = dt.AsEnumerable().Count();
  
          if (dsRowCount &gt; dtRowCount)
          {
              //Set main table to be dt as this has the least number of rows.
              dataGridView3.DataSource = NoMatches(dt, ds);
          }
          else
          {
              //Set main table to ds as this has the least number of rows OR tables have the same number of rows.
              dataGridView3.DataSource = NoMatches(ds, dt);
          }
  
  
  
  
  
           }
  
      private IEnumerable&lt;DataRow&gt; NoMatches(DataTable MainTable, DataTable SecondaryTable)
      {
          var matched = from table1 in MainTable.AsEnumerable()
                        join table2 in SecondaryTable.AsEnumerable()
                        on table1.Field&lt;string&gt;("ISH_FALLZAHL") equals table2.Field&lt;string&gt;("FAL")
                        where (table1.Field&lt;string&gt;("ML_CODE").Equals(table2.Field&lt;string&gt;("LST")))
                       || Convert.ToInt32(table1.Field&lt;Int16&gt;("ML_ANZ")) == Convert.ToInt32(table2.Field&lt;double&gt;("ST"))
                        select table1;
  
          return MainTable.AsEnumerable().Except(matched);
      }
  
  
  
  
  
  
  
         // dataGridView3.DataSource = CompareTables();
  
  
      }
  
      /*        public bool DatatablesAreSame()
              {
  
  
  
                  if (ds.Rows.Count == dt.Rows.Count)
  
                      return true;
  
                  foreach (DataColumn dc in ds.Columns)
                  {
                      for (int i = 0; i &lt; dt.Rows.Count; i++)
                      {
                          if (ds.Rows[i][dc.ColumnName = "ISH_FALLZAHL"] != dt.Rows[i][dc.ColumnName = "FAL"])
                          {
  
  
                          }
                      }
                  }
  
                  return true;
              }
  
         */
  
  /*
      private void CompareTables()
      {
  
          try
          {
  
  
  
              var dt1Records = ds.AsEnumerable().Select(e1 =&gt; new { Id = e1["ISH_FALLZAHL"].ToString(), Name = e1["FAL"].ToString() });
  
              var dt2Records = dt.AsEnumerable().Select(e2 =&gt; new { Id = e2["ISH_FALLZAHL"].ToString(), Name = e2["FAL"].ToString() });
  
  
  
              var extraRecords = dt1Records.Except(dt2Records);
  
  
  
  
              dataGridView3.DataSource = extraRecords;
  
  
  
  
          }
  
          catch (Exception ex)
  
          { }
  
      }
  
    */ 
  
  
  
  
  
  }
  </code></pre>