Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

Custom DataView for one-to-one relationship

I've got a DataSet with two relatated DataTables that contain Employer Data.  Their relationship is keyed off the EmpID.  There is strictly a one-to-one relationship between the two tables, although they contain different data.  The two tables are from two entirely different in-house systems.

I'm wanting a way to view the records as if they are on the same row instead of the parent-child relationship created by the DataRelationship in the DataSet.  Basically I want to see the data in one DataTable or DataView that would could be created by the simple SQL:

Select e1.*, e2.*
From myEmployerTable e1, otherEmployerTable e2
Where e1.EmpID = e2.EmpID

Unfortunately, I can do that.  They come from two different systems with their different object models.

Any ideas?

Thanks in advance,

Tim Shults
Thursday, June 23, 2005

Since, at this point, no one has responded I guess I will. No doubt somebody will come back with a third party library or something open source that will slice this thing up for you and deliver it on a plate.

But if not, and just in that off chance you didn't think of it, just create a seperate datatable to hold the results of both tables and populate it one row at a time from both.  Obviously the real evil thing here is if you present this to a user and they change it you need to reflect the change in one or the other table (or possibly both) before you can save it.

If you go this route my advice to you is that it works better to immediately change the base tables rather than wait until some event and do them all.  My basic reason for this is that I like what is reflected to the user to match the data that is represented.  That is what they are going to get if a save occurs.

But really I suspect what will happen is somebody will come along with really good advice on how to do this.  I am helping you though by giving them a reason to do this (which will be to point out that I am an idiot).

Mark Flory
Friday, June 24, 2005

Create three DataSets, all with a single table Employees which has a primary key EmpID.
- BothDataSet has a table Employees with columns from both tables myEmployeeTable and otherEmployeeTable.
- MyDataSet has a table Employees with columns from table myEmployeeTable
- OtherDataSet has a table Employees with columns from table otherEmployeeTable
- run two queries to fill MyDataSet and OtherDataSet
- merge the results into BothDataSet:

Friday, June 24, 2005

Thanks for the advice, Mark.  That's my solution if there isn't a more elegant way to do it.  I just figured there just might be a way to do it.  Obviously not.

Joe, unfortunately the Merge method on adds the records as more rows to the table, not sharing the same row as the "parent".

Thanks again.

Tim Shults
Sunday, June 26, 2005

> unfortunately the Merge method on adds the records as more rows to the table, not sharing the same row as the "parent".

If that's the case, you probably missed out a step:

all with a single table Employees *which has a primary key EmpID*

There needs to be a primary key to tell the Merge method which rows to merge.

Monday, June 27, 2005

You are very welcome.

There may still be an elegant solution out there for you I just don't know it. 

Mark Flory
Monday, June 27, 2005

*  Recent Topics

*  Fog Creek Home