C# .NET - how to compare 2 different database table compare and display alert

Asked By Eram naaz on 03-Dec-11 07:38 AM

Hi all,
How to compare 2 different database tables and display the alert message using console application in C#

Using Union ALL you can compare between 2 table... in your c# application you can right the query like below sample one

SELECT   MIN(customer) AS customer, customer_id, customer_fname,    customer_lname, customer_email, customer_phoneFROM (

  SELECT 'customer_table_first_db' AS customer,     first_table.customer_id,first_table.customer_fname,   first_table.customer_lname,first_table.customer_email,    first_table.customer_phone  FROM first_db.customer AS first_table  UNION ALL

  SELECT 'customer_table_second_db' AS customer,

    second_table.customer_id, second_table.customer_fname,

    second_table.customer_lname, second_table.customer_email,

      second_table.customer_phone  FROM second_db.customer AS second_table) AS temp_tableGROUP BY customer_id, customer_fname, customer_lname,   customer_email, customer_phoneHAVING COUNT(*) > 1ORDER BY customer,customer_id;

vidya shankar replied to Suchit shah on 03-Dec-11 08:04 AM

Thanks for your answer to my query.Here we are checking one database table from sql server and another one in My sql we want to match table values.if it equal then display alter message like "match data"

my code follows here:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CompareDB_Alert
{
    class Program
    {


      static void Main(string[] args)
      {
      

        string SqlConnstr = @"Data Source=SECURESERVER;Initial Catalog=MasterDB;User ID=sa;Password=welcome";
        string SqlConn = @"Data Source=SECURESERVER;Initial Catalog=CilentDB;User ID=sa;Password=welcome";
        string sql = @"select lenName,Propuse,Propstre,Propcity,Statelet,Propzip,Country from search";
        string sql1 = @"select prop_addr1 , prop_addr2 , city , state, zip, country_id, prop_desc  from file_lst where create_date= convert(varchar,getdate(),112)";
        SqlConnection conn = new SqlConnection(SqlConnstr);
        SqlConnection conn1 = new SqlConnection(SqlConn);

        try
        {
          conn.Open();
          SqlDataAdapter da = new SqlDataAdapter(sql, conn);
          DataTable dt = new DataTable();
          //da.Fill(dt);

          conn1.Open();
          SqlDataAdapter da1 = new SqlDataAdapter(sql1, conn1);
          DataTable dt1 = new DataTable();
          //da1.Fill(dt1);
          //Fill the data table with select statement's query results:
          int recordsAffectedSubscriber = 0;
          int recordsAffectedMaster = 0;
          int count = 0;
          recordsAffectedSubscriber = da1.Fill(dt1);
          recordsAffectedMaster = da.Fill(dt);

          if (recordsAffectedSubscriber != 0)
          {
            foreach (DataRow drSubscriber in dt1.Rows)
            {
              foreach (DataRow drMaster in dt.Rows)
              {
                if (drSubscriber[0].Equals(drMaster[0]))
                {
                  Console.WriteLine(drSubscriber[0]);
                  break;
                }
                else
                {
                  count++;
                }
              }
            }
          }
          if (count > 0)
          {
            Console.WriteLine("Tables being compared are not equal. " + count + " rows differ.");
          }
          else
          {
            Console.WriteLine("Tables being compared are equal.");
            Console.ReadLine();
          }
        }
        catch (Exception e)
        {
          Console.WriteLine("Error: " + e);
          Console.Read();
        }

        finally
        {
          conn.Close();
          conn1.Close();
        }
      }
    }
}


    

 
  


Here match is not happening in proper manner

dipa ahuja replied to vidya shankar on 03-Dec-11 09:16 AM

public static DataTable CompareTables(DataTable first, DataTable second)
{
  first.TableName = "FirstTable";
  second.TableName = "SecondTable";
 
  //Create Empty Table
  DataTable table = new DataTable("Difference");
 
  try
  {
    //Must use a Dataset to make use of a DataRelation object
    using (DataSet ds = new DataSet())
    {
      //Add tables
      ds.Tables.AddRange(new DataTable[] { first.Copy(), second.Copy() });
 
      //Get Columns for DataRelation
      //DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];
      DataColumn[] firstcolumns = new DataColumn[2];
      firstcolumns[0] = ds.Tables[0].Columns[1];
      firstcolumns[1] = ds.Tables[0].Columns[7];
 
        
      DataColumn[] secondcolumns = new DataColumn[2];
      secondcolumns[0] = ds.Tables[1].Columns[1];
      secondcolumns[1] = ds.Tables[1].Columns[7];
 
      DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
 
      ds.Relations.Add(r);
 
      //Create columns for return table
      for (int i = 0; i < first.Columns.Count; i++)
      {
        table.Columns.Add(first.Columns[i].ColumnName, first.Columns[i].DataType);
      }
 
      //If First Row not in Second, Add to return table.
      table.BeginLoadData();
 
      foreach (DataRow parentrow in ds.Tables[0].Rows)
      {
        DataRow[] childrows = parentrow.GetChildRows(r);
        if (childrows == null || childrows.Length == 0)
          table.LoadDataRow(parentrow.ItemArray, true);
      }
 
      table.EndLoadData();
 
    }
  }
  catch (Exception ex)
  {
    throw ex;
  }
 
  return table;
}

Anoop S replied to vidya shankar on 03-Dec-11 10:09 AM

The UNION operator in SQL can help you to compare data of two tables of two different databases. The following query unions the queries for matching column names and their values from two tables and keeps just those rows which occur once in the each table. Those are the rows unique to one table or the other. In your SELECT you would customize your columns something like { customer_id, customer_fname, customer_lname, customer_email, customer_phone, ...} or as desired:

SELECT 
  MIN(customer) AS customer, customer_id, customer_fname, customer_lname,
     customer_email, customer_phone
FROM (

  SELECT 'customer_table_first_db' AS customer, first_table.customer_id,
   first_table.customer_fname, first_table.customer_lname, 
   first_table.customer_email,first_table.customer_phone
  FROM first_db.customer AS first_table

  UNION ALL

  SELECT 'customer_table_second_db' AS customer,second_table.customer_id, 
   second_table.customer_fname, second_table.customer_lname,
   second_table.customer_email, second_table.customer_phone
  FROM second_db.customer AS second_table

) AS temp_table
GROUP BY customer_id, customer_fname, customer_lname, 
  customer_email, customer_phone
HAVING COUNT(*) = 1
ORDER BY customer,customer_id; 

To get similar records from both the tables, you can change the above query as below:

SELECT 
  MIN(customer) AS customer, customer_id, customer_fname,
      customer_lname, customer_email, customer_phone
FROM (

  SELECT 'customer_table_first_db' AS customer, 
   first_table.customer_id,first_table.customer_fname, 
   first_table.customer_lname,first_table.customer_email,
   first_table.customer_phone
  FROM first_db.customer AS first_table

  UNION ALL

  SELECT 'customer_table_second_db' AS customer,
   second_table.customer_id, second_table.customer_fname,
   second_table.customer_lname, second_table.customer_email,
          second_table.customer_phone
  FROM second_db.customer AS second_table
) AS temp_table
GROUP BY customer_id, customer_fname, customer_lname,
  customer_email, customer_phone
HAVING COUNT(*) > 1
ORDER BY customer,customer_id;