LEFT JOIN in a Dataset between two tables

[bernies]

Hi,
I have two databases on two different servers and I want to make sure
the contents of the table on one server is the same as the other.

I have read the two tables into a single dataset. I was then hoping to
do a left join between the two DataTables to show the records added to
one table that don't exist in the other. Then I would send only the
rows that needed to be added back to one of the servers.

I don't think I can use a Datarelation because they seem to only
support inner joins.

Is there another way? Thanks in advance.

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

If the servers can be linked (like a server link in SQL Server), I would use
the database server to compare. In .NET, you will have to loop through to
find values.

A better option is a select across both databases. For example, you could
create a comma separated list of values and compare against the database you
want to ensure is synced. This is much like linking servers. You will still
have to loop through some values.

With two SQL Servers, you can use replication to sync them up.

There is no built in tool in .NET to do what you wish, however.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

Hi,
I have two databases on two different servers and I want to make sure
the contents of the table on one server is the same as the other.

I have read the two tables into a single dataset. I was then hoping to
do a left join between the two DataTables to show the records added to
one table that don't exist in the other. Then I would send only the
rows that needed to be added back to one of the servers.

I don't think I can use a Datarelation because they seem to only
support inner joins.

Is there another way? Thanks in advance.

 

[William Ryan eMVP]

Hi,
I have two databases on two different servers and I want to make sure
the contents of the table on one server is the same as the other.

I have read the two tables into a single dataset. I was then hoping to
do a left join between the two DataTables to show the records added to
one table that don't exist in the other. Then I would send only the
rows that needed to be added back to one of the servers.

A datarelation will in fact support what you are asking about. If I have
200 records in the left table, call it customers, and 50 in customer details
(which related on PK/FK CustID), then I have access to all the 200 rows in
Customers and I also can access the 50 details records. You can iterate
through the left hand table....
DataRow[] childRows;
foreach(DataRow dro in CustomerDataSet.Tables[CustomersIndex].Rows){
childRows = dro.GetChildRows();

}

You can also nest this within a foreach with the child relation or one of a
bunch of other techniques.

I don't think I can use a Datarelation because they seem to only
support inner joins.

Not necessarily the case. IT all comes down to the usage.

Is there another way? Thanks in advance.

This should help
http://msdn.microsoft.com/library/d...rfsystemdatadatarowclassgetchildrowstopic.asp
but the main thing is that you can use a Join if you don't need to update,
but if you do, then you won't be able to autogen your update logic and
writing your own will be a real pain. Furthermore, you will pull over
redundant data which is a waste of resources and should be used judiciously
..

HTH,

BIll

----------------------------------------------------------
color]


--

W.G. Ryan, eMVP

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

 

[bernies]

Sorry, the point of my inquirey was a that I believed that the dataset
was supposed to be an in memory database. I thought that if this were
the case that you should be able to create a new datatable or
dataview from two tables already loaded into the dataset by using
SQL. Some sort of command like:

oDataSet.Tables.Add(bUseSQL,sSQL)

I am pretty sure the relationship object is as close as I'll get what
I was hoping. Thanks for your replies