Can I change a connection string of the object data source programmatically ?

[Orit]

Hello .

Please find below my questions - I hope some of the ASP.NET experts of
this forum will answer to those beginner's questions :

My Web site should be able to work with either SQL Server or Access
database as following :

It will connect first to the Master database (SQL or Access,
connection String of this database should be read from the C:\WINDOWS
\XXX.INI file).
It will have one or more sub-databases ( SQL Or Access , list of sub-
databases is saved in one of the Master tables .)
The schema of all sub-databases is the same , no matter if this is
Access or SQL .

My site should present sub-databases drop-down list ( which will be
retrieved from the Master database ) and then will connect to
specified sub-database ( either Access or SQL ) and will display data
from this database .
I would like to be able to switch database type ( database provider )
programmatically on the data source level so it is transparent for the
application code .
Can I do it ?
Can I change a connection string of the object data source
programmatically ? If so , how can I do so ?
Can I then call the same methods which will call Access or SQL
database as needed ?
Or . should I create different table adapters , specify SQL connection
string for one of them , OLEDB connection string to another ,
duplicate all methods and have a if/else block each time I call a
methods of those adapters ?
If so , would it be easier then NOT to use data objects and use just
different providers ?
Do I miss something here ?

Thanks in advance for your help ,
Orit Chanukov .

[Rich P]

Hi Orit,

Your connection string can be based on criteria. If the user selects
something, then the user will invoke whatever connection meets the
criteria for the selection. If the sql server is the mirror for the
Access mdb, then you can do something like checking if the connection to
the Access mdb works. If the connection state is invalid (error) then
create a sql connection. Or if the User selects from one dropdown box
on your webpage the User invokes one connection - if not -then the other
connection.

Once a process is completed you need to close the connection. You
should open the connection for each interaction and then close the
connection right away. There is no need to keep a connection open -
especially for ASP.Net since you can create in memory datasets at the
client side (for dropdowns, datagridviews, etc).

Rich

*** Sent via Developersdex http://www.developersdex.com ***