Connection to Multiple Databases

[wasps]

Hi,

I have an application that connects to a SQL database, and based on the contents of one of the tables, then has to connect to other databases, and run various scripts.
However, until I connect to that first database, I have no idea what the other databases are called, or how many there are.


Currently, when I query the table in the first database, I write the values of the other databases to an array.
This then means that I know all the names, and the number of other SQL databases that exist.

I then loop through the array, and change the properties of my sqlconnection to reflect the database details in the current element of the array.
This works fine, and provides me with all the results that I require.


However, I now need to include a background worker process in my app so that I can display a progress bar while the result fetching takes place.
However, once I use a background worker thread, I have found that I am unable to change the sqlconnection properties to connect to a different database.


I was unable to find a way around this, so then tried to declare the connections to the databases individually as soon as the results from the first database table are returned.
However, that's not really working out too well.



So, basically, I need some ideas on how to connect to multiple databases, when I don't know the names of the databases or the number of databases.

Alternatively, if anyone can throw me some ideas on why my background worker thread won't let me change the sqlconnection properties, that would probably be ideal.


Thank you

 

[jmcilhinney]

There's nothing about a BackgroundWorker that would specifically prevent you from changing your connection string. If it didn't work it's because you didn't do it right. We'd need to see what you did to know what you did wrong.

 

[wasps]

Hi,

Thanks for the reply.

I wasn't expecting a background worker thread to have any problems changing sqlconnection properties.
However, when it fell over as soon as I used the background worker process I thought there must be some reason why a background worker thread can't change the properties as it was originally created by the UI thread.


I've backed out part of that code now, but I will reinstate it and post the code that is falling over.


Thank you

 

[jmcilhinney]

The issue with multi-threading and access applies only to controls. All data is accessible to all threads, including controls. The issue is that trying to perform an operation on a control that requires access to its handle on a thread that didn't create that handle can result in unpredictable behaviour, so it is disallowed by default. Other than that, there are no issues with multi-threading other than synchronisation, which is probably what you experienced. This basically means multiple threads updating and using the same data at the same time and interfering with each other.

 

[wasps]

Hi,

This was the first time I was using a background worker thread, and had assumed that I had a problem using a connection that had been created by my UI thread.


While I was aware that I couldn't write to any controls on a form in a background thread, I wasn't aware that I couldn't read from them either.
I think this is where my problem was originally.


Once you'd told me that there should be no reason why a background worker thread shouldn't be able to change a sqlconnection property, I attacked it again with confidence that it should work.
Armed with that confidence, I've now managed to sort this out.


So, thank you very much for your help.


Steve