locked
compare columns in a datatable and if equal remove row RRS feed

  • Question

  • I have a datatble and want to compare column (a) to column(z) if they are equal I want to remove them from the table.

    Can anyone suggest/ have a snippet that can do this. Many thanks in advance.

    Wednesday, July 30, 2014 6:25 PM

Answers

  • Hi CanAnn,

    here a small Console-App that shows you how to do this:

       class Program
        {
            static void Main(string[] args)
            {
                // 1. Create table
                var table = new DataTable();
                table.Columns.Add("FirstName", typeof(string));
                table.Columns.Add("LastName", typeof(string));
    
                // 2. Fill table with 4 rows
                var row = table.NewRow();
                row["FirstName"] = "Satya";
                row["LastName"] = "Nadella";
                table.Rows.Add(row);
    
                row = table.NewRow();
                row["FirstName"] = "Bill";
                row["LastName"] = "Gates";
                table.Rows.Add(row);
    
                row = table.NewRow();
                row["FirstName"] = "Steve";
                row["LastName"] = "Ballmer";
                table.Rows.Add(row);
    
                // Note that this has equal values
                row = table.NewRow();
                row["FirstName"] = "Huber";
                row["LastName"] = "Huber";
                table.Rows.Add(row);
    
                // Output
                PrintRecords(table);
    
                // 3. Find rows with equal columns via LINQ
                var equalRows = table.Rows.Cast<DataRow>().Where(dataRow => dataRow["FirstName"] == dataRow["LastName"]).ToList();
    
                foreach (var equalRow in equalRows)
                {
                    table.Rows.Remove(equalRow);
                }
    
    
                PrintRecords(table);
    
                Console.ReadLine();
    
            }
    
            private static void PrintRecords(DataTable table)
            {
                foreach (DataRow dataRow in table.Rows)
                {
                    Console.WriteLine("{0} {1}", dataRow["FirstName"], dataRow["LastName"]);
                }
                Console.WriteLine();
            }
        }


    Thomas Claudius Huber

    "If you can't make your app run faster, make it at least look & feel extremly fast"

    My latest Pluralsight-course: Windows Store Apps - Data Binding in Depth

    twitter: @thomasclaudiush
    homepage: www.thomasclaudiushuber.com
    author of: ultimate Windows Store Apps handbook | ultimate WPF handbook | ultimate Silverlight handbook

    • Marked as answer by CanAnn Wednesday, July 30, 2014 7:10 PM
    Wednesday, July 30, 2014 6:54 PM

All replies

  • Hi CanAnn,

    here a small Console-App that shows you how to do this:

       class Program
        {
            static void Main(string[] args)
            {
                // 1. Create table
                var table = new DataTable();
                table.Columns.Add("FirstName", typeof(string));
                table.Columns.Add("LastName", typeof(string));
    
                // 2. Fill table with 4 rows
                var row = table.NewRow();
                row["FirstName"] = "Satya";
                row["LastName"] = "Nadella";
                table.Rows.Add(row);
    
                row = table.NewRow();
                row["FirstName"] = "Bill";
                row["LastName"] = "Gates";
                table.Rows.Add(row);
    
                row = table.NewRow();
                row["FirstName"] = "Steve";
                row["LastName"] = "Ballmer";
                table.Rows.Add(row);
    
                // Note that this has equal values
                row = table.NewRow();
                row["FirstName"] = "Huber";
                row["LastName"] = "Huber";
                table.Rows.Add(row);
    
                // Output
                PrintRecords(table);
    
                // 3. Find rows with equal columns via LINQ
                var equalRows = table.Rows.Cast<DataRow>().Where(dataRow => dataRow["FirstName"] == dataRow["LastName"]).ToList();
    
                foreach (var equalRow in equalRows)
                {
                    table.Rows.Remove(equalRow);
                }
    
    
                PrintRecords(table);
    
                Console.ReadLine();
    
            }
    
            private static void PrintRecords(DataTable table)
            {
                foreach (DataRow dataRow in table.Rows)
                {
                    Console.WriteLine("{0} {1}", dataRow["FirstName"], dataRow["LastName"]);
                }
                Console.WriteLine();
            }
        }


    Thomas Claudius Huber

    "If you can't make your app run faster, make it at least look & feel extremly fast"

    My latest Pluralsight-course: Windows Store Apps - Data Binding in Depth

    twitter: @thomasclaudiush
    homepage: www.thomasclaudiushuber.com
    author of: ultimate Windows Store Apps handbook | ultimate WPF handbook | ultimate Silverlight handbook

    • Marked as answer by CanAnn Wednesday, July 30, 2014 7:10 PM
    Wednesday, July 30, 2014 6:54 PM
  • this worked for me:

      public static DataTable compare_remove(DataTable table, string column1, string column2)
            {
    
                var equalRows = table.Rows.Cast<DataRow>().Where(dataRow => dataRow[column1].ToString () == dataRow[column2].ToString ()).ToList();
    
                foreach (var equalRow in equalRows)
                {
                    table.Rows.Remove(equalRow);
                }
    
                return table;
            }
        

    Wednesday, July 30, 2014 7:10 PM