getting data from two datatables

[Guest]

I have a dataset which contains two datatables one called current, the other previous. They both consist of a single column called CompanyID. I want to retreive (eventually in to a comma separated list) all the companies from current, which do not exist in the previous table. Can anyone suggest a way of doing this?

 

[Mohamoss]

Hi
Here is a way to do that
1) make a copy of current in another table - lets name it temp
2) make your column the primaty key of the table temp (so we can use the
find metod)
this is a property of the datatable that take a value of array of
datacolumns , it will only have in your case
temp.PrimaryKey = new
DataColumn[] { temp.Columns[0]} // i am using a word editor but i think
this i the exact //syntax
3) then you iterate on the old table get a company id and try to find a
match of it in temp , using the find method which return a datarow
DataRow to_delete =
temp.find(old.Rows.coulmn[0].tosring()) // again i think this is the
correct syntax but just in case , the idea is fo find the old id in the
temp table
4) once you find the matching row , you deleted
temp.r.Remove(to_delete)
5) by donig that for all values of old you will delete all matches on temp
, then what will be left in temp is those companies that didn't exist on
the old table , which is what you want


Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

 

[William Ryan eMVP]

Two methods come to mind:

1) Use a DataRelation
http://www.knowdotnet.com/articles/datarelation.html
Set the Current as the parent table and set the former as the child. That
way the Parent will always have all of the rows in the child, and perhaps
more but not necessarily so. You can loop through each of the parent rows
and then then use the GetChildRows. If there are child rows, then DON'T add
that row to the differences colleciton (you can use an arraylist, datatable
or whatever else). You can use this exampel from DotNetJohn, except do the
opposite. If there are child rows, then exit the loop, otherwise add that
row to the collection. When you are done you'll have your rows. Not sure if
you want the whole row of just the pk but that's easy to distinguish
http://www.dotnetjohn.com/articles.aspx?articleid=63

2) Use the RowFilter of the DataView
http://www.knowdotnet.com/articles/adopartiii.html
http://www.knowdotnet.com/articles/advancedrowfilter.html

You'll need a unique field in the current table but I'm assuming you have
one. Create a DataView on the second table (it's simple and the above links
show you how to do this if you aren't familiar with it). Create another
DataView on the second table. Loop through the first table's rows collection
and set the RowFilter of the Former/DataView to whatever the key field is of
the current row. If there are matching records inthe former, the .Count
property of the dataview will be > 0. If the .Count property is 0 after
applying the DataView's Rowfilter, then that means they don't exist in the
child table. If you have one column in each table same type, same size
(basically if you can create a relation on them) then setting the rowfilter
to each value in the parent table as you loop through it will cause only
matching records to show.

ArrayList DifferenceHolder = new ArrayList();
DataView dvFormer = FormerDataTable.DefaultView;
foreach(DataRow dro in CurrentDataTable.Rows){
dvFormer.RowFilter = "CompanyID = '" + (integer)dro["CompanyID"] +
"'"
//Should use the index of CompanyID instead for performance reasons but
i wanted
// to be clear. Also, I'm assuming CompanyID is of type Integer, if not
change it to
//its corresponding type
if(dv.Count==0){
DifferenceHolder.Add(dro);
}

}
At the end, you'll have the datarows that exist in the parent but not the
child. If you just want the PK value, then just add the integer (or wahtever
type) representation instead of the whole row).

Let me know if you have any questions.

Bill


--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

I have a dataset which contains two datatables one called current, the

other previous. They both consist of a single column called CompanyID. I
want to retreive (eventually in to a comma separated list) all the companies
from current, which do not exist in the previous table. Can anyone suggest
a way of doing this?