Compare two SqlDataSources? Can It be done?

[Manthis]

Hi!

I have two SqlDataSources (say SQLDS_1 and SQLDS_2). SQLDS_1 has a ConnectionString to an Oracle DB and SQLDS_2 to a MSSQL DB and I have a DDL and a Detailsview both using SQLDS_1 as its DataSource.

Now, what I want to do is to filter some field that is common to a table in both Databases (e.g field 'userID' in a table called 'users' on both DBs).

I'm doing this because the Oracle DB has more than 5,000 rows while the MSSQL DB has only about 200 and I need info for those users that is present only on the Oracle DB.

How can I accomplish this? Can I call different Databases (with different ProvideNames) in one single SqlDataSource? Or is there a way to compare the queried data from both SqlDatasources?

By the way, I ‘m using VS2005 and VB language (but you can post C# code if you want).

I would really appreciate any help or advice provided.
Regards.

 

[jbenson001]

First, I would stop using the SQL data source controls. Although they make creating a page easier, they cause problems because they make debugging and maintenance difficult.

I am not exactly following what you want to do. Do you want to filter both result sets by a user ID?

 

[jmeckley]

of course you can query multiple databases in one application.
1st you need to get rid of the datasource controls. they are meant to link GUI controls directly to the database.

you need to load a datatable/set from database 1 and 2. then compare the 2 tables to create a 3rd table. then bind table 3 to your control of choice.

google ADO.Net to learn how to manage database connectivity via code.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Manthis]

Thanks for your fast replies!
What I want to do is simple, in my MSSQL database I´m storing user information that comes from my Oracle database.

My problem is that I want to filter my ddl (that displays user IDs from Oracle) so it doesn't display the records that are already in the MSSQL DB. I´m doing this to prevent the user from trying to store duplicate records. That is why I want to compare the fields form both DBs.

I'm somewhat new to ASP.NET and wanted to go the "easy road" using SQL DataSource controls (It's a pretty light intranet app). But I'll follow your suggestions and look into ADO.NET.

Thanks again.

 

[jmeckley]

I'm somewhat new to ASP.NET and wanted to go the "easy road" using SQL DataSource controls (It's a pretty light intranet app). But I'll follow your suggestions and look into ADO.NET.

Learn the tools you use. If you're going to create an asp.net app then understand the asp.net framework and basic http protocol. If you blindly drag/drop controls and use the wizards, you will get the simple things done, but anything complex (this problem for example) you will be stuck. maintenance will also become a nightmare because changing 1 requirement may cause you to recode the entire page.

there are differences between html and desktop enviornments, but the domain model (application core, etc.) doesn't change, only the GUI. webforms has made it very easy for developers to forget this.



Jason Meckley
Programmer
Specialty Bakers, Inc.