grouping data from two dataset

Question

hi, i've got a problem with dataset:

I've got two dataset from two different server but they have the same columns.

so it's like that:

First DataSet :

asset description make jobtype jan feb ... dec
 0001  mine        ik     Acc   0   0       10
 0002  yours        ic    Over  0   0       10

Second dataset :

asset description make jobtype jan feb ... dec
 0001  mine        ik     Acc   10   0       10
 0002  yours       ic     Gen   0    0       0

But i would like to merge the 2 dataset into one like that:

asset description make jobtype lhjan imjan lhfeb lhfeb ... lhdec imdec
 0001  mine        ik     Acc     0    10      0     0        10    10
 0002  yours       ic    Over     0     0      0     0        10     0

so all the data from one is combine with the second with the same asset and the same jobtype.

I try linq but i can't arrive to make it like i want. I'm working on vb.net framework 3.5.

could you help me?

Julien

Answer 1

i just want to make sure that data sources 1 and 2 are actually data tables. this is how my answer will describe this. ill also be describing it in C#, though the syntax doesn't differ much. you need to alternate your selections for the intermingled fields. you'll also need to adjust your datatypes as necessary

from dt1 in ds["datatable1"].AsEnumerable()
join dt2 in ds["datatable2"].AsEnumerable() on
new { asset = dt1.Field<string>("asset"), jobtype = dt1.Field<string>("jobtype") } equals
new { asset = dt2.Field<string>("asset"), jobtype = dt2.Field<string>("jobtype") }
select new
{
    asset = dt1.Field<string>("asset"),
    description = dt1.Field<string>("description"),
    make = dt1.Field<string>("make"),
    ...
    lhjan = dt1.Field<int>("jan"),
    imjan = dt2.Field<int>("jan"),
    lhfeb = dt1.Field<int>("feb"),
    imfeb = dt2.Field<int>("feb"),
    ....
};

here is the approximate vb syntax:

Dim query = _
    From dt1 In dataset.Tables["datatable1"].AsEnumerable() _
    Join dt2 In dataset.Tables["datatable2"].AsEnumerable()_
        On  new { asset = dt1.Field(Of String)("asset"), jobtype = dt1.Field(Of String)("jobtype") } _
        Equals new { asset = dt2.Field(Of String)("asset"), jobtype = dt2.Field(Of String)("jobtype") } _
    Select New With _
        { _
            // see members from c# example _
        }

Answer 2

If you literally just want to join the two, you can use the same DATASET in 2 different Adaptor.fill().

If you do not use a NEW DATASET before the second FILL, the 2 will get merged.