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 .

 

[Nathan Sokalski]

I think that you will have to use different DataAdapters with the If
statement like you mentioned. Because SQL Server and Access use different
DataAdapters, Connections, and other objects for accessing databases, you
would end up writing the code for each of them separately anyway. The
connection string can be changed programmatically using the ConnectionString
property of the connection (this property is inherited from the
System.Data.Common.DbConnection class, so all the connection types have it)
One thing that may come in useful at some point in your code (or maybe not,
I haven't seen your code and algorithms) is using the parent classes, such
as System.Data.Common.DbConnection, and assign the appropriate connection
type to it. This could help you organize your variables in some situations,
you decide what your preference is. The reason there is not an easier way to
do this is because applications are not expected to use multiple database
providers in the same application. Good Luck!

 

[Orit]

I think that you will have to use different DataAdapters with the If
statement like you mentioned. Because SQL Server and Access use different
DataAdapters, Connections, and other objects for accessing databases, you
would end up writing the code for each of them separately anyway. The
connection string can be changed programmatically using the ConnectionString
property of the connection (this property is inherited from the
System.Data.Common.DbConnection class, so all the connection types have it)
One thing that may come in useful at some point in your code (or maybe not,
I haven't seen your code and algorithms) is using the parent classes, such
as System.Data.Common.DbConnection, and assign the appropriate connection
type to it. This could help you organize your variables in some situations,
you decide what your preference is. The reason there is not an easier way to
do this is because applications are not expected to use multiple database
providers in the same application. Good Luck!
--
Nathan Sokalski
(e-mail address removed)://www.nathansokalski.com/












- Show quoted text -

Thanks a lot , Nathan .
That what I thought .
Orit,