Datasets join

Posted on 2004-11-21
Last Modified: 2010-04-15

Hi All,

I would truly appreciate help on the following matter:

I have two Datasets (each a result of a different database select):

1. Jobs: int jobid (primary key), string jobname, string jobtype.
2. UserJobs: userid, jobid (N-To-N relation).

I would like to create a dataset that will contain all the jobs of a certain user. For example, I want have a dataset with all the jobs of a user with userid=5.

How can I do that efficiently? (I have got about 100,000 records in the UserJobs table - for about 1000 users).



Question by:l_agmon
    1 Comment
    LVL 10

    Accepted Solution

    U have to first have a DataRelation between the two tables in the dataset. Then create a DataViewManager which does the same function to a Dataset as a Dataview does to a DataTable.


          // Create DataView settings for each Table
          // using the DataViewManager
          DataViewManager myDVM = new DataViewManager(custDS);

          // Loop through the DataViewSettings and set Sort
          // or Rowfilter for each Table individually
          String myTable;
          foreach (DataViewSetting myDVS in myDVM.DataViewSettings)
            // Set Default Sort Order = Primary Key for all Tables
            myDVS.ApplyDefaultSort = true;

            // Set individual Sort and Rowfilter ...
            myTable = myDVS.Table.ToString();
             if (myTable == "UserJobs")
              myDVS.RowFilter = "userid = '5'

    // Or u can do this directly like this
          myDVM.DataViewSettings["JobDetails"].RowFilter =
            "userid = 5";

           dataGrid.SetDataBinding(myDVM, "Jobs");

    For more details on the samples . Follow this link

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now