none
How to compare two table having identical schema in two different database and display differences between both table. RRS feed

  • Question

  • I am creating a windows form application to compare tables of two database having same number of tables and same schema of tables. Only records may be different in same table of two database. 

    What i have already done:
    I am able to list all tables having different records in combobox on windows form. 

    Next i want that when i select a table from a list of table in combbox. Then it should display differences of selected table of both database. 

    Main problem i am facing is in combobox i am getting table name but for comparison the table type should be of DataTable type. 

    My compare table method like:

    puvlic DataTable compareTable(DataTable dt1, DataTable dt2){

    }// How i convert Table name into DataTable type to compare.

    • Edited by AJret Friday, December 29, 2017 4:42 AM
    Thursday, December 28, 2017 9:06 AM

Answers

  • Hi AJret,

    1. Create a class named ConnUtils, which could get DataTable object via table name, and storage connection string, like this:

    public class ConnUtils
        {
            public static string Conn1String { get; set; } = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=live;Integrated Security=True";
            public static string Conn2String { get; set; } = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=test;Integrated Security=True";
    
            public static DataTable GetTableFromTableName(string tableName, string connectionString)
            {
                DataTable dt = new DataTable();
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    string sqlText = string.Format("select * from {0}", tableName);
                    using (SqlCommand command = new SqlCommand(sqlText,conn))
                    {
                        conn.Open();
                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        adapter.Fill(dt);
                    }
                }
                return dt;
            }
        }

    2. Create a class named Utils, which could compare two DataTable object.

    public class Utils
        {
            public static DataTable compareTable(DataTable dt1, DataTable dt2)
            {
                var result = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
                DataTable dt3 = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default).
                CopyToDataTable();
                return dt3;
            }
        }

    #Usage in winform.

    private void Form2_Load(object sender, EventArgs e)
    {
       string connstring1 = ConnUtils.Conn1String;
       string connstring2 = ConnUtils.Conn2String;
       string tableName = "course";
    //get datatable object from the first database. 
       DataTable dt1 = ConnUtils.GetTableFromTableName(tableName, connstring1);
    //get datatable object from another database. 
       DataTable dt2 = ConnUtils.GetTableFromTableName(tableName, connstring2);
    //compare table and return different records
       DataTable dt3 = Utils.compareTable(dt1, dt2);
    //display the different records into winform datagridview contrtol.
                dataGridView1.DataSource = dt3.DefaultView;
    }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AJret Saturday, December 30, 2017 12:29 PM
    Friday, December 29, 2017 1:52 AM
    Moderator
  •  Hi Zhanglong Wu,

    This is my pseudocode where i am adding all table name which have different record in a list.

     List<string> listDiff = new List<string>();  

    if (!DifferentRecordTables.CompareTable(livetables, testTables))
    {
        listDiff.Add(livetables.TableName);//Here i am adding all table having different record in a list

         this.cboDatabase1.DataSource = listDiff;//Displaying all those tables in a combo box on windows form.

      } Now i want to select any table from a list of table in combo box and then display difference between selected table of source and destination database. 

    Again i will select another table name from combo box then difference of this selected table should be displayed and previous table difference should be disappear.   


    • Edited by AJret Friday, December 29, 2017 5:15 AM
    • Marked as answer by AJret Saturday, December 30, 2017 12:29 PM
    Friday, December 29, 2017 5:09 AM
  • Hi AJret,

    According to your description, it seems that you could get the table name from combo box control on winform form page. like this:

    private void button1_Click(object sender, EventArgs e)
    {
                string connstring1 = ConnUtils.Conn1String;
                string connstring2 = ConnUtils.Conn2String;
                string tableName = comboBox1.Text; //"course";
                string tableNam2 = comboBox2.Text;
                DataTable dt1 = ConnUtils.GetTableFromTableName(tableName, connstring1);
                DataTable dt2 = ConnUtils.GetTableFromTableName(tableNam2, connstring2);
                DataTable dt3 = Utils.compareTable(dt1, dt2);
                dataGridView1.DataSource = dt3.DefaultView;
    }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 29, 2017 5:34 AM
    Moderator
  • Hi AJret,

    Try modify it like this:

     public static DataTable compareTable(DataTable dt1, DataTable dt2)
            {
                DataTable dt3 = new DataTable();
                var result = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
                if (result.Count() > 0)
                {
                    dt3 = result.CopyToDataTable();
                }
                //dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default).
                return dt3;
            }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by BonnieBMVP Friday, December 29, 2017 3:13 PM
    • Marked as answer by AJret Saturday, December 30, 2017 12:30 PM
    Friday, December 29, 2017 9:53 AM
    Moderator

All replies

  • Hi AJret,

    1. Create a class named ConnUtils, which could get DataTable object via table name, and storage connection string, like this:

    public class ConnUtils
        {
            public static string Conn1String { get; set; } = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=live;Integrated Security=True";
            public static string Conn2String { get; set; } = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=test;Integrated Security=True";
    
            public static DataTable GetTableFromTableName(string tableName, string connectionString)
            {
                DataTable dt = new DataTable();
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    string sqlText = string.Format("select * from {0}", tableName);
                    using (SqlCommand command = new SqlCommand(sqlText,conn))
                    {
                        conn.Open();
                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        adapter.Fill(dt);
                    }
                }
                return dt;
            }
        }

    2. Create a class named Utils, which could compare two DataTable object.

    public class Utils
        {
            public static DataTable compareTable(DataTable dt1, DataTable dt2)
            {
                var result = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
                DataTable dt3 = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default).
                CopyToDataTable();
                return dt3;
            }
        }

    #Usage in winform.

    private void Form2_Load(object sender, EventArgs e)
    {
       string connstring1 = ConnUtils.Conn1String;
       string connstring2 = ConnUtils.Conn2String;
       string tableName = "course";
    //get datatable object from the first database. 
       DataTable dt1 = ConnUtils.GetTableFromTableName(tableName, connstring1);
    //get datatable object from another database. 
       DataTable dt2 = ConnUtils.GetTableFromTableName(tableName, connstring2);
    //compare table and return different records
       DataTable dt3 = Utils.compareTable(dt1, dt2);
    //display the different records into winform datagridview contrtol.
                dataGridView1.DataSource = dt3.DefaultView;
    }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AJret Saturday, December 30, 2017 12:29 PM
    Friday, December 29, 2017 1:52 AM
    Moderator
  •  Hi Zhanglong Wu,

    This is my pseudocode where i am adding all table name which have different record in a list.

     List<string> listDiff = new List<string>();  

    if (!DifferentRecordTables.CompareTable(livetables, testTables))
    {
        listDiff.Add(livetables.TableName);//Here i am adding all table having different record in a list

         this.cboDatabase1.DataSource = listDiff;//Displaying all those tables in a combo box on windows form.

      } Now i want to select any table from a list of table in combo box and then display difference between selected table of source and destination database. 

    Again i will select another table name from combo box then difference of this selected table should be displayed and previous table difference should be disappear.   


    • Edited by AJret Friday, December 29, 2017 5:15 AM
    • Marked as answer by AJret Saturday, December 30, 2017 12:29 PM
    Friday, December 29, 2017 5:09 AM
  • Hi AJret,

    According to your description, it seems that you could get the table name from combo box control on winform form page. like this:

    private void button1_Click(object sender, EventArgs e)
    {
                string connstring1 = ConnUtils.Conn1String;
                string connstring2 = ConnUtils.Conn2String;
                string tableName = comboBox1.Text; //"course";
                string tableNam2 = comboBox2.Text;
                DataTable dt1 = ConnUtils.GetTableFromTableName(tableName, connstring1);
                DataTable dt2 = ConnUtils.GetTableFromTableName(tableNam2, connstring2);
                DataTable dt3 = Utils.compareTable(dt1, dt2);
                dataGridView1.DataSource = dt3.DefaultView;
    }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 29, 2017 5:34 AM
    Moderator
  • Hi Zhanglong Wu,

    It's working fine when there is different records in test and live table. But it's generating exception when there is no different record in test and live table.

    Exception like : System.InvalidOperationException , {"The source contains no DataRows."}

    DataTable dt3 = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default).
                    CopyToDataTable();//Exception generated in this line.

    Friday, December 29, 2017 9:45 AM
  • Hi AJret,

    Try modify it like this:

     public static DataTable compareTable(DataTable dt1, DataTable dt2)
            {
                DataTable dt3 = new DataTable();
                var result = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
                if (result.Count() > 0)
                {
                    dt3 = result.CopyToDataTable();
                }
                //dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default).
                return dt3;
            }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by BonnieBMVP Friday, December 29, 2017 3:13 PM
    • Marked as answer by AJret Saturday, December 30, 2017 12:30 PM
    Friday, December 29, 2017 9:53 AM
    Moderator