Looking for a good implementation that will access multiple databases simultaneously

Question

I just want to find out if there's a good implementation (in C#/ASP.NET) on how to access my records from my different databases simultaneously. I have this simple application which suppose to search on these databases, then, display the record if a certain keyword is matched.

I have like 5 databases (currently using MySQL) all in all and will be expanding it on the future (might be changing to SQL Server). I am fully aware on how to search on a single database, while I'm still learning how do it in multiple databases.

Any tips, comments or suggestions?

Answer 1

Have multiple instances of XXXConnection (MysqlConnection or whatever) and iterate through them.

Answer 2

You would use the same methodology used to search a single database against each of the other databases using a different connection string. This would be a good candidate for the parallel extensions in .NET 4.0 or the 3.5 version of the library that was put out some time ago. If the resultsets are going to be somewhat identical, I would also investigate the use of the DataTable.Merge method.

Answer 3

I can't say for MySQL, but in SQL server you can select from multiple databases (so long as the user you are connecting to has the correct permissions). If the tables are similar across all databases then you could perhaps do this in one query by UNIONING the results together. So, if you had 3 databases (called DB1, DB2 and DB3) and each had a table called Records then you could do this:

SELECT * FROM DB1.dbo.Records WHERE Name Like '%searchterm%'
UNION
SELECT * FROM DB2.dbo.Records WHERE Name Like '%searchterm%'
UNION
SELECT * FROM DB3.dbo.Records WHERE Name Like '%searchterm%'

Answer 4

Depending on whether you perform your queries on the server or client side of the application, you might go the following way on the server side:

[Server].[Database].[Schema].[Table]

In SQL Server, the table name may be prefixed by the server name, the database name and the schema name. If you'd rather not use the schema name, but you need to specify the database which is on the same server, you could write the following:

[Database]..[Table]

If you're more preferably want to do your search from the client-side, you might wish to use Enterprise Library for the ease of configurable named connection strings and the abstraction it offers.

You could simply use a BackgroundWorker if you wish to gain in performance. Than again, that is on the client-side.