none
How to join 3 datatables using C# RRS feed

  • Question

  • I have a situation where I am getting data from 3 sharepoint lists.  I am getting each list into 4 datatables.  Now I need to provide a join between each of them and get data based on some filters. Can anyone tell me how to join 3 datatables.  I am on .net 2.0 so I can not use LINQ.
    • Moved by Jackie-SunModerator Wednesday, July 13, 2011 6:25 AM (From:ADO.NET Managed Providers)
    Friday, June 11, 2010 6:26 AM

Answers

  • If the amount of data is small, nested loops will work.  If too slow, you can go with a hashing (Dictionary) approach to speed it up.

    If the DataTables are in the same DataSet, you can add DataRelations corresponding to the joins between the tables.  Then methods like GetParentRow and GetChildRows can be used to get the related (joined) rows to any given row, probably with some nested processing.  These DataRelation methods also tend to perform well because DataTable indexes the data to support the mentioned methods.

    // DataTables are t1, t2, t3.
    // "c1" is the name of the column in common between t1 and t2.
    // "c2" is the name of the column in common between t2 and t3.
    
    foreach (DataRow r1 in t1.Rows)
    {
     foreach (DataRow r2 in t2.Rows)
     {
      if (r1.RowState != DataRowState.Deleted && (int)r1["c1"] == (int)r2["c1"])
      {
       foreach (DataRow r3 in t3.Rows)
       {
        if (r3.RowState != DataRowState.Deleted && (int)r2["c2"] == (int)r3["c2"])
        {
         // Use data from r1, r2, and r3 here.
        }
       }
      }
     }
    }
    • Marked as answer by swatijain Thursday, June 17, 2010 4:56 AM
    Saturday, June 12, 2010 9:47 PM

All replies

  • If the amount of data is small, nested loops will work.  If too slow, you can go with a hashing (Dictionary) approach to speed it up.

    If the DataTables are in the same DataSet, you can add DataRelations corresponding to the joins between the tables.  Then methods like GetParentRow and GetChildRows can be used to get the related (joined) rows to any given row, probably with some nested processing.  These DataRelation methods also tend to perform well because DataTable indexes the data to support the mentioned methods.

    // DataTables are t1, t2, t3.
    // "c1" is the name of the column in common between t1 and t2.
    // "c2" is the name of the column in common between t2 and t3.
    
    foreach (DataRow r1 in t1.Rows)
    {
     foreach (DataRow r2 in t2.Rows)
     {
      if (r1.RowState != DataRowState.Deleted && (int)r1["c1"] == (int)r2["c1"])
      {
       foreach (DataRow r3 in t3.Rows)
       {
        if (r3.RowState != DataRowState.Deleted && (int)r2["c2"] == (int)r3["c2"])
        {
         // Use data from r1, r2, and r3 here.
        }
       }
      }
     }
    }
    • Marked as answer by swatijain Thursday, June 17, 2010 4:56 AM
    Saturday, June 12, 2010 9:47 PM
  • You can add it in DataSet and add relationship between the datatables using Ds.Relations.Add() method

     


    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Sunday, June 13, 2010 12:17 PM