locked
DataTable

    Question

  • How to get common records from two tables without checking each and every record in .net not sql server
    Thursday, December 10, 2009 6:32 AM

Answers

  • Hi,

    Please try the code bellow:
    Also please note we need to derive from IEqualityComparer<DataRow> to compare the datarows.

            private void Form1_Load(object sender, EventArgs e)
            {
                DataTable dt1 = new DataTable();
                dt1.Columns.Add("Location");
                DataTable dt2 = new DataTable();
                dt2.Columns.Add("Location");
                string locations = "chennai$madurai$palani$delhi$UP$MP";
                string[] locationsplit = locations.Split('$');
                for (int i = 0; i <= 5; i++)
                {
                    DataRow dr1 = dt1.NewRow();
                    if (i % 2 == 0)
                    {
                        dr1["Location"] = locationsplit[i];
                    }
                    else
                    {
                        dr1["Location"] = locationsplit[i];
                        DataRow dr2 = dt2.NewRow();
                        dr2["Location"] = locationsplit[i];
                        dt2.Rows.Add(dr2);
                    }
                    dt1.Rows.Add(dr1);
                }        
                DataSet ds = new DataSet();
                ds.Tables.Add(dt1);
                ds.Tables.Add(dt2);            
                IEnumerable<DataRow> ci = dt1.AsEnumerable().Intersect<DataRow>(dt2.AsEnumerable(),new LocationComparer());           
               DataTable commTable =ci.CopyToDataTable();
                 
            }
            public class LocationComparer : IEqualityComparer<DataRow>
            {
                public bool Equals(DataRow x, DataRow y)
                {
                    return (x.Field<string>("Location") == y.Field<string>("Location"));
                }
                public int GetHashCode(DataRow obj)
                {
                    return obj.ToString().GetHashCode();
                }
            }

    Harry


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Danijel MalikMVP Wednesday, December 16, 2009 2:48 PM
    • Marked as answer by Harry Zhu Thursday, December 17, 2009 1:29 AM
    Wednesday, December 16, 2009 4:06 AM
  • Hi,

    Sorry, it ain't possible. If you're using .NET Framework 3.5, you can use Intersect<T> extension method.
    http://msdn.microsoft.com/en-us/library/system.linq.enumerable.intersect(VS.95).aspx
    Regards, Danijel
    • Marked as answer by Harry Zhu Thursday, December 17, 2009 1:29 AM
    Thursday, December 10, 2009 7:58 PM

All replies

  • How to get common records from two tables without checking each and every record in .net not sql server
    • Merged by Harry Zhu Friday, December 11, 2009 7:57 AM
    Thursday, December 10, 2009 6:38 AM
  • You cannot compare two datatables without iterate through each row on each datatable to check if they are the same.

    You can try to use Linq  query To DataSet to join the two DataTables and get the common records.

    You can also try to use the Merge and GetChanges methods on the DataTables.

    For example,

    dataTableA.Merge(dataTableB); // This will add to dataTableA any records that are in dataTableB but not in dataTableA
    return dataTableA.GetChanges(); // This will returns records originally only in dataTableB

    Here

    DataTable.Merge Method - The Merge method is used to merge two DataTable objects that have largely similar schemas.

    DataTable.GetChanges Method - Gets a copy of the DataTable containing all changes made to it since it was last loaded, or since AcceptChanges was called.

     

    Thursday, December 10, 2009 9:00 AM
  • Hi,

    Sorry, it ain't possible. If you're using .NET Framework 3.5, you can use Intersect<T> extension method.
    http://msdn.microsoft.com/en-us/library/system.linq.enumerable.intersect(VS.95).aspx
    Regards, Danijel
    • Marked as answer by Harry Zhu Thursday, December 17, 2009 1:29 AM
    Thursday, December 10, 2009 7:58 PM
  • Hi Ramesh

          please send me a example code for use Linq query To DataSet to join the two DataTable and get the common records.


    Regards,
    Ravi.N
    Friday, December 11, 2009 10:13 AM
  • Hi,

    It's very simple.

    var rows = dataSet1.DataTable1.Intersect(dataSet2.DataTable1);

    Regards, Danijel http://arkcore.wordpress.com
    Friday, December 11, 2009 1:44 PM
  • Hi

     I am using visual studio 2008 but i didn't get intersect property not get in Table in server side,Please help me

    Regards,
    Ravi.N
    Monday, December 14, 2009 10:17 AM
  • Hi,

    Intersect is an extension method, that's why you need to add using directive to System.Linq:
    using System.Linq;

    HTH


    Regards, Danijel http://arkcore.wordpress.com
    Monday, December 14, 2009 10:49 AM
  • hi

      still i didn't get inersect property ,for example code
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Linq;

    public partial class Default6 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("Location");
            DataTable dt2 = new DataTable();
            dt2.Columns.Add("Location");
            string locations = "chennai$madurai$palani$delhi$UP$MP";
            string[] locationsplit = locations.Split('$');
            for (int i = 0; i <= 5; i++)
            {
                DataRow dr1 = dt1.NewRow();
                if (i % 2 == 0)
                {                
                    dr1["Location"] = locationsplit[i];
                }
                else
                {
                    dr1["Location"] = locationsplit[i];
                    DataRow dr2 = dt2.NewRow();
                    dr2["Location"] = locationsplit[i];
                    dt2.Rows.Add(dr2);
                }
                dt1.Rows.Add(dr1);
            }
            DataSet ds = new DataSet();
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);       

        }
    }

    i need to take common records for with out checking each and every records.
    Monday, December 14, 2009 11:08 AM
  • Hi,

    Intersect method works only with IEnumerable<T> classes, which DataTable isn't and that why you can't call Intersect on it.
    Create a typed DataSet (using a designer) instead.


    Regards, Danijel http://arkcore.wordpress.com
    Monday, December 14, 2009 12:51 PM
  • Hi,

    Please try the code bellow:
    Also please note we need to derive from IEqualityComparer<DataRow> to compare the datarows.

            private void Form1_Load(object sender, EventArgs e)
            {
                DataTable dt1 = new DataTable();
                dt1.Columns.Add("Location");
                DataTable dt2 = new DataTable();
                dt2.Columns.Add("Location");
                string locations = "chennai$madurai$palani$delhi$UP$MP";
                string[] locationsplit = locations.Split('$');
                for (int i = 0; i <= 5; i++)
                {
                    DataRow dr1 = dt1.NewRow();
                    if (i % 2 == 0)
                    {
                        dr1["Location"] = locationsplit[i];
                    }
                    else
                    {
                        dr1["Location"] = locationsplit[i];
                        DataRow dr2 = dt2.NewRow();
                        dr2["Location"] = locationsplit[i];
                        dt2.Rows.Add(dr2);
                    }
                    dt1.Rows.Add(dr1);
                }        
                DataSet ds = new DataSet();
                ds.Tables.Add(dt1);
                ds.Tables.Add(dt2);            
                IEnumerable<DataRow> ci = dt1.AsEnumerable().Intersect<DataRow>(dt2.AsEnumerable(),new LocationComparer());           
               DataTable commTable =ci.CopyToDataTable();
                 
            }
            public class LocationComparer : IEqualityComparer<DataRow>
            {
                public bool Equals(DataRow x, DataRow y)
                {
                    return (x.Field<string>("Location") == y.Field<string>("Location"));
                }
                public int GetHashCode(DataRow obj)
                {
                    return obj.ToString().GetHashCode();
                }
            }

    Harry


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Danijel MalikMVP Wednesday, December 16, 2009 2:48 PM
    • Marked as answer by Harry Zhu Thursday, December 17, 2009 1:29 AM
    Wednesday, December 16, 2009 4:06 AM
  • Hi

            Its really nice one.
            Thank u for ur response.


    Regards,
    Ravi.N
    Wednesday, December 16, 2009 5:00 AM
  • nice code
    Sunday, May 27, 2012 8:37 AM