• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Datasets join


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).

Thanks!

Agmon,

0
l_agmon
Asked:
l_agmon
1 Solution
 
123654789987Commented:
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.

custDS.Relations.Add("JobDetails",
        custDS.Tables["Jobs"].Columns["JobId"],
        custDS.Tables["UserJobs"].Columns["JobId"]);

      // 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
http://www.akadia.com/services/dotnet_filter_sort.html
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now