locked
How to filter Two column of DataTable to DataTable in c# winform RRS feed

  • Question

  • How to filter Two column of DataTable to DataTable in c# winform.

    I want to filter only which value does not exists in the datatable2.

    example  "C8", "Tok"
     "C9", "Harry"

    DataTable dt1 = new DataTable();
                dt1.Columns.Add("Code", typeof(string));
                dt1.Columns.Add("Name", typeof(string));
                dt1.Rows.Add(new Object[] { "C1", "Smith" });
                dt1.Rows.Add(new Object[] { "C2", "Dimitry" });
                dt1.Rows.Add(new Object[] { "C3", "John" });
                dt1.Rows.Add(new Object[] { "C4", "Mac" });
                dt1.Rows.Add(new Object[] { "C5", "Smith1" });
                dt1.Rows.Add(new Object[] { "C6", "Smith2" });
                dt1.Rows.Add(new Object[] { "C7", "Smith3" });
                dt1.Rows.Add(new Object[] { "C8", "Tok" });
                dt1.Rows.Add(new Object[] { "C9", "Harry" });
                dt1.Rows.Add(new Object[] { "C10", "Runy" });
    
    
                DataTable dt2 = new DataTable();
                dt2.Columns.Add("Code", typeof(string));
                dt2.Columns.Add("Name", typeof(string));
                dt2.Columns.Add("Address", typeof(string));
                dt2.Columns.Add("MobileNo", typeof(string));
                dt2.Rows.Add(new Object[] { "C1", "Smith","","" });
                dt2.Rows.Add(new Object[] { "C2", "Dimitry", "", "" });
                dt2.Rows.Add(new Object[] { "C3", "John", "", "" });
                dt2.Rows.Add(new Object[] { "C4", "Mac", "New York", "12547825" });
                dt2.Rows.Add(new Object[] { "C5", "Smith1", "", "" });
                dt2.Rows.Add(new Object[] { "C6", "Smith2", "", "" });
                dt2.Rows.Add(new Object[] { "C7", "Smith3", "London", "5781452454" });
                dt2.Rows.Add(new Object[] { "C15", "Runy", "Mexico", "8952452454" });
    
                DataTable dtdiffrence = new DataTable();
                var differences = dt1.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
                dtdiffrence = differences.Any() ? differences.CopyToDataTable() : new DataTable();


    vkp

    Wednesday, October 5, 2016 2:01 PM

Answers

  • In your original post you said you want the values that weren't in table 2 and your example data demonstrated that. Are you now wanting data that is missing from either? If so then except is no longer enough as it is one way.

    There are a few different ways to do this, depending upon your data size. Probably the most database like would be to use except on one list and then the other and then union the results. Another approach might be to try a join against both tables and where out the ones that don't have a match. Yet another approach might be to use intersect against the lists to produce a list of the matches and then use except against each list to remove them.

    Friday, October 7, 2016 1:12 PM

All replies

  • DataRowComparer.Default compares the rows by value. Since the schemas are different none of them would match. Hence your first call would return back all the rows in dt1. You need to use a custom compare if you want to compare only certain columns.

    class CodeComparer : IEqualityComparer<DataRow>
    {
        public bool Equals ( DataRow x, DataRow y )
        {
            return String.Compare(x["Code"]?.ToString(), y["Code"]?.ToString()) == 0;
        }
    
        public int GetHashCode ( DataRow obj )
        {
            return obj["Code"]?.GetHashCode() ?? 0;
        }
    }
    
    //Returns 3 differences based upon missing codes
    var differences = dt1.AsEnumerable().Except(dt2.AsEnumerable(), new CodeComparer());

    Michael Taylor
    http://www.michaeltaylorp3.net

    Wednesday, October 5, 2016 2:54 PM
  • Thanks

    but i want to compare code and name both not only code.

    Result reutrn 2 row


    vkp

    Thursday, October 6, 2016 6:48 AM
  • "but i want to compare code and name both not only code. "

    It doesn't matter what the condition is. The code I gave you works for any condition. Simply change the Equals and GetHashCode implementations to reflect the additional checks you want.

    class CodeAndNameComparer : IEqualityComparer<DataRow>
    {
        public bool Equals ( DataRow x, DataRow y )
        {
            return String.Compare(x["Code"]?.ToString(), y["Code"]?.ToString()) == 0 && String.Compare(x["Name"]?.ToString(), y["Name"]?.ToString()) == 0;
        }
    
        public int GetHashCode ( DataRow obj )
        {
            //Return some mangled value
            var temp = obj["Code"] ?? "" + "_" + obj["Name"] ?? "";
            return temp.GetHashCode();
        }
    }

    Thursday, October 6, 2016 1:50 PM
  • Please again look my code and output with image

    in this image i want unique code and name from both table

     DataTable dt1 = new DataTable();
                dt1.Columns.Add("Code", typeof(string));
                dt1.Columns.Add("Name", typeof(string));
                dt1.Rows.Add(new Object[] { "C1", "Smith" });
                dt1.Rows.Add(new Object[] { "C2", "Dimitry" });
                dt1.Rows.Add(new Object[] { "C3", "John" });
                dt1.Rows.Add(new Object[] { "C4", "Mac" });
                dt1.Rows.Add(new Object[] { "C5", "Smith1" });
                dt1.Rows.Add(new Object[] { "C6", "Smith2" });
                dt1.Rows.Add(new Object[] { "C7", "Smith3" });
                dt1.Rows.Add(new Object[] { "C8", "Tok" });
                dt1.Rows.Add(new Object[] { "C9", "Harry" });
                dt1.Rows.Add(new Object[] { "C10", "Runy" });
                dt1.Rows.Add(new Object[] { "C30", "Rinku" });
    
    
                DataTable dt2 = new DataTable();
                dt2.Columns.Add("Code", typeof(string));
                dt2.Columns.Add("Name", typeof(string));
                dt2.Columns.Add("Address", typeof(string));
                dt2.Columns.Add("MobileNo", typeof(string));
                dt2.Rows.Add(new Object[] { "C1", "Smith","","" });
                dt2.Rows.Add(new Object[] { "C2", "Dimitry", "", "" });
                dt2.Rows.Add(new Object[] { "C3", "John", "", "" });
                dt2.Rows.Add(new Object[] { "C4", "Mac", "New York", "12547825" });
                dt2.Rows.Add(new Object[] { "C5", "Smith1", "", "" });
                dt2.Rows.Add(new Object[] { "C6", "Smith2", "", "" });
                dt2.Rows.Add(new Object[] { "C7", "Smith3", "London", "5781452454" });
                dt2.Rows.Add(new Object[] { "C15", "Runy", "Mexico", "8952452454" });
                dt1.Rows.Add(new Object[] { "C30", "Rony" });
    
                DataTable dtdiffrence = new DataTable();
                var differences = dt1.AsEnumerable().Except(dt2.AsEnumerable(), new CodeComparer());
                dtdiffrence = differences.Any() ? differences.CopyToDataTable() : new DataTable();


    class CodeComparer : IEqualityComparer<DataRow>
            {
                public bool Equals(DataRow x, DataRow y)
                {
                    return  String.Compare(x["Code"]?.ToString(), y["Code"]?.ToString()) == 0 || string.Compare(x["Name"]?.ToString(), y["Name"]?.ToString()) == 0;
                }
    
                public int GetHashCode(DataRow obj)
                {
                    var temp = obj["Code"] ?? "" + "_" + obj["Name"] ?? "";
                    return temp.GetHashCode();
                    //return obj["Code"]?.GetHashCode() ?? 0;
                }
            }


    Compare.


    vkp

    Friday, October 7, 2016 5:30 AM
  • In your original post you said you want the values that weren't in table 2 and your example data demonstrated that. Are you now wanting data that is missing from either? If so then except is no longer enough as it is one way.

    There are a few different ways to do this, depending upon your data size. Probably the most database like would be to use except on one list and then the other and then union the results. Another approach might be to try a join against both tables and where out the ones that don't have a match. Yet another approach might be to use intersect against the lists to produce a list of the matches and then use except against each list to remove them.

    Friday, October 7, 2016 1:12 PM