none
How to get a list of all tables having different records in two database [on hold] RRS feed

  • Question

  • I  have two database test and live database having same schema and there are only tables in databases. There are same number of tables and same table in both database. I want to get list of all those tables which have different records in both database.

    Suppose there are three table Student, Course and Enrollment in both database. If same table have different records in both database. Then i want to list of all those tables.


    Ajit

    Friday, December 22, 2017 5:08 AM

Answers

  • Hi Risu Raj,

    1. write a static compare method like this:

    public class Utils
        {
            public static bool ComTable(DataTable dt1, DataTable dt2)
            {
                var result = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
                //DataTable dt3 = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default).
                //CopyToDataTable();
                if(result.Count() > 0)
                    return false;
                else
                    return true;
            }
        }

    2. store datatable into dataset, then compare them like this:

    string connstr = @"data source=(localdb)\MSSQLLocalDB;initial catalog=live;integrated security=True";
                string testconnstr = @"data source=(localdb)\MSSQLLocalDB;initial catalog=test;integrated security=True";
                List<string> tables = new List<string>();
                List<string> testtables = new List<string>();
                DataSet dstest = new DataSet();
    
                DataSet dslive = new DataSet();
                using (var conn = new SqlConnection(connstr))
                {
                    
                    conn.Open();
                    DataTable dt = conn.GetSchema("Tables");
                    foreach (DataRow row in dt.Rows)
                    {
                        string tablename = (string)row[2];
                        tables.Add(tablename);
                    }
    
                    foreach (var item in tables)
                    {
                        string sqlString = string.Format("select * from {0}", item);
                        DataTable dataTable = new DataTable();
                        dataTable.TableName = item;
                        using (var command = new SqlCommand(sqlString, conn))
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter(command);
                            adapter.Fill(dataTable);
                        }
                        dslive.Tables.Add(dataTable);
                    }
                }
    
                using (var conn = new SqlConnection(testconnstr))
                {
                    conn.Open();
                    DataTable dt = conn.GetSchema("Tables");
                    foreach (DataRow row in dt.Rows)
                    {
                        string tablename = (string)row[2];
                        testtables.Add(tablename);
                    }
    
                    foreach (var item in testtables)
                    {
                        string sqlString = string.Format("select * from {0}", item);
                        DataTable dataTable = new DataTable();
                        dataTable.TableName = item;
                        using (var command = new SqlCommand(sqlString, conn))
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter(command);
                            adapter.Fill(dataTable);
                        }
                        dstest.Tables.Add(dataTable);
                    }
                }
    
                List<string> listDiff = new List<string>();
    
                foreach (DataTable livetable in dslive.Tables)
                {
                    foreach (DataTable testTable in dstest.Tables)
                    {
                        if (livetable.TableName == testTable.TableName)
                        {
                            //compare two datatable.
                            if (!Utils.ComTable(livetable, testTable))
                            {
                                listDiff.Add(livetable.TableName);
                            }
                        }
                    }
                }

    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 22, 2017 3:46 PM
    • Marked as answer by Risu Raj Thursday, December 28, 2017 8:25 AM
    Friday, December 22, 2017 9:14 AM
    Moderator
  • Hi Risu Raj,

    We could also get all tables via SQL statement, like this:

     DataTable dt = new DataTable();
    List<string> tables = new List<string>();
    using (var conn = new SqlConnection(ConnUtils.ConnectionString))
    {
           using (SqlCommand com = new SqlCommand("SELECT* FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", conn))
          {
               conn.Open();
               using (SqlDataReader reader = com.ExecuteReader())
                 {
                            while (reader.Read())
                            {
                                tables.Add((string)reader["TABLE_NAME"]);
                            }
                        }
                    }
                }

    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 Risu Raj Thursday, December 28, 2017 8:25 AM
    Thursday, December 28, 2017 2:19 AM
    Moderator

All replies

  • Hi Risu Raj,

    1. write a static compare method like this:

    public class Utils
        {
            public static bool ComTable(DataTable dt1, DataTable dt2)
            {
                var result = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
                //DataTable dt3 = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default).
                //CopyToDataTable();
                if(result.Count() > 0)
                    return false;
                else
                    return true;
            }
        }

    2. store datatable into dataset, then compare them like this:

    string connstr = @"data source=(localdb)\MSSQLLocalDB;initial catalog=live;integrated security=True";
                string testconnstr = @"data source=(localdb)\MSSQLLocalDB;initial catalog=test;integrated security=True";
                List<string> tables = new List<string>();
                List<string> testtables = new List<string>();
                DataSet dstest = new DataSet();
    
                DataSet dslive = new DataSet();
                using (var conn = new SqlConnection(connstr))
                {
                    
                    conn.Open();
                    DataTable dt = conn.GetSchema("Tables");
                    foreach (DataRow row in dt.Rows)
                    {
                        string tablename = (string)row[2];
                        tables.Add(tablename);
                    }
    
                    foreach (var item in tables)
                    {
                        string sqlString = string.Format("select * from {0}", item);
                        DataTable dataTable = new DataTable();
                        dataTable.TableName = item;
                        using (var command = new SqlCommand(sqlString, conn))
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter(command);
                            adapter.Fill(dataTable);
                        }
                        dslive.Tables.Add(dataTable);
                    }
                }
    
                using (var conn = new SqlConnection(testconnstr))
                {
                    conn.Open();
                    DataTable dt = conn.GetSchema("Tables");
                    foreach (DataRow row in dt.Rows)
                    {
                        string tablename = (string)row[2];
                        testtables.Add(tablename);
                    }
    
                    foreach (var item in testtables)
                    {
                        string sqlString = string.Format("select * from {0}", item);
                        DataTable dataTable = new DataTable();
                        dataTable.TableName = item;
                        using (var command = new SqlCommand(sqlString, conn))
                        {
                            SqlDataAdapter adapter = new SqlDataAdapter(command);
                            adapter.Fill(dataTable);
                        }
                        dstest.Tables.Add(dataTable);
                    }
                }
    
                List<string> listDiff = new List<string>();
    
                foreach (DataTable livetable in dslive.Tables)
                {
                    foreach (DataTable testTable in dstest.Tables)
                    {
                        if (livetable.TableName == testTable.TableName)
                        {
                            //compare two datatable.
                            if (!Utils.ComTable(livetable, testTable))
                            {
                                listDiff.Add(livetable.TableName);
                            }
                        }
                    }
                }

    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 22, 2017 3:46 PM
    • Marked as answer by Risu Raj Thursday, December 28, 2017 8:25 AM
    Friday, December 22, 2017 9:14 AM
    Moderator

  • Hi Zhanglong Wu,

    That's really great explanation. 

    What to do if i have already created connection and connection on compare page is coming from other page.

    Then what to write in place of conn and connstr in your code.

    using(var conn = new SqlConnection(connstr)){

    conn.open();

    DataTable dt = conn.GetSchema("Table")

    }




    Ajit

    Friday, December 22, 2017 10:33 AM
  • Hi Risu,

    >>What to do if i have already created connection and connection on compare page is coming from other page.<<

    Show that part of your code, please ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, December 22, 2017 3:45 PM
  • Hi Zhanglong Wu,

    Is there any other way without using GetSchema("Tables") method because in my application GetSchema() method returning system table from system database.


    Ajit

    Wednesday, December 27, 2017 12:42 PM
  • I'm not sure exactly what all these parameters are anymore ... I'm sure I used to, but I wrote this code a long time ago. I can't test it, because I currently do not have any databases that have SystemTables. But, give it a try:

    DataTable dt = conn.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" });


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, December 27, 2017 3:14 PM
  • Hi Risu Raj,

    We could also get all tables via SQL statement, like this:

     DataTable dt = new DataTable();
    List<string> tables = new List<string>();
    using (var conn = new SqlConnection(ConnUtils.ConnectionString))
    {
           using (SqlCommand com = new SqlCommand("SELECT* FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", conn))
          {
               conn.Open();
               using (SqlDataReader reader = com.ExecuteReader())
                 {
                            while (reader.Read())
                            {
                                tables.Add((string)reader["TABLE_NAME"]);
                            }
                        }
                    }
                }

    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 Risu Raj Thursday, December 28, 2017 8:25 AM
    Thursday, December 28, 2017 2:19 AM
    Moderator
  • Hi Risu Raj,

    According to your description, it seems that it is new problem, I would suggest that you could post a new thread, it will be beneficial to other communities who have the similar issue.

    Thanks for your understanding and cooperation.

    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.

    Thursday, December 28, 2017 8:37 AM
    Moderator