On DataTable, two databases

[Steve B.]

Hi,

I'm building an ASP.Net corportate web application.

One module of the application consists of a web service that manages users.
Due to the history of the organisation, there is several databases accross
the network and users data are unfortunatelly not stored in the same DB.

What I want to reach is to provides users informations (read only
informations) through the web service to hide the complexity of the data.

I've created a UsersDataTable (using the VS Designer) that have 8 columns.
Half of the columns are taken from the 1st DB (SQL Server) and the other
half columns are taken from the 2nd DB.

FYI, there is one nvarchar column in the two tables of the two sources that
identify uniquely the rows.

I want to keep the maximum of functionnalities of the VS designer and I'd
like (if possible) to avoid duplicating the data between the two sources.
I've thought about the linked server functionnalities between the two sql
server, but later, there's a probability that other platform will be
queried, in fact, I'd like a general neutral solution (pure ADO.Net).

What the best way to correctly handle the two sources ?


Thanks for your feedbacks.
Steve

 

[Kevin Spencer]

Use a Distributed Query.

See the following articles:

http://msdn2.microsoft.com/en-us/library/aa172738(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms188721.aspx

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

 

[Steve B.]

That applies only when I'm querying two databases. It requires not only that
the "main" sql server has access to the second sql server, but also that
both servers are on the same network.
My case applies to this conditions, but I do not want to be blocked if
tomorrow this requirements are not met.

Imagine if you work with extranet partners with only http access, or even a
"simple" xml file, etc...
I'd like to be able to handle such scenarios and the only way I found, is to
fill the datatable row by row, by querying the DBs as often as required...

Thanks,
Steve

 

[sloan]

DataSet.Merge works well.

---------------
IF you have 2 seperate tables (in your dataset) .
Like
ds.Employee
ds.Dept
-----------
It works well on one table, but you have different PK's (as mentioned)

like
ds.Employee
ds.Employee
(perhaps the first one has full time employees, and the second
one has part time employees)


EmployeeDS ds1 = new EmployeeDS();
//populate ds1 with full time emps.

EmployeeDS ds2 = new EmployeeDS();
//populate ds2 with parttime emps.

EmployeeDS dsmerged = ds1.Merge( ?? ds2 //multi overloads here) ;


...

Merging "the same rows based on the PK" is a totally different story, and
this permutation doesn't play nice.
HOWEVER, it is doable.


ds.Employee ( EmpID , LastName, FirstName , SSN , HireDate , DOB ,
ReleaseDate )

Lets say database1 has EmpID, LastName, FirstName
database2 has EmpID , HireDate , DOB , ReleaseDate

is it ~possible (but not as cleancut as above) to do this. but .Merge likes
different row, you'll have to play with it to get (same rows (based on
EmpID) different columns to work)

EmployeeDS ds1 = new EmployeeDS();
//populate ds1 EmpID, LastName, FirstName.

EmployeeDS ds2 = new EmployeeDS();
//populate ds2 EmpID , HireDate , DOB , ReleaseDate.

EmployeeDS dsmerged = ds1.Merge( ?? ds2 //multi overloads here) ;

The key is EmpID is the primary key defined in the DataSet.

.........

And the last permutation is

EmployeeDS ds1 = new EmployeeDS();
//populate ds1 EmpID, LastName, FirstName.

EmployeeDS ds2 = new EmployeeDS();
//populate ds2 EmpID , LASTNAME , HireDate , DOB , ReleaseDate

Notice lastname is on both db's. Lets say its out of sync (someone got
married??), you'll have issues here.



//I want to keep the maximum of functionnalities of the VS designer//

Dump the designer for populating the data. Write your own code to populate
ds1 and ds2.
You'll never win with the auto generated table adapters.


PS
ds.Employee ( EmpID , LastName, FirstName , SSN , HireDate , DOB ,
ReleaseDate )

Note, that everything in your ds will have to allows nulls, except for
EmpID........