ASP.NET - dynamically creating database connection in datasets

Question

I am currently using datasets for my data access layer. I currently store the connection string in the web.config file. I need the ability to change the connection to another database before any queries are processed. Is there an event that is triggered or a base class that can be modified that intercepts the process of retrieving the connection string from the web.config file? Is there any way to handle multiple database connections using the same code base and also take advantage of the connection pooling? I understand the best method is to get rid of datasets and use custom data objects. Any ideas?

Answer 1

Connection Pooling is based on the connection string so changing it dynamically for each SqlConnection you are creating will defeat its purpose.

Answer 2

If you use the dataadapters you can just say :

fAdapter.Connection = new SqlConnection("connectionstring");

Pooling is automatic (if same connectionstring is used).