Change Connection String at runtime.

[david.leedom]

I am at my witts end, which is not saying very much....

I am building a commerical VB.NET applicaiton. The assumptions are:

*The Server IP number is different for every deployment.
*Possibly the database engine is different for every deployment (SQL
Server, Oracle, Firebird, Postgresql)
*And finallly the database Login and password may be different for each
deployment.

I am trying to use the built in Visual Studio 2005 ado.net code
generation. So I don't want to change the generated code and have to
worry about it getting lost.

It appears that the generated code uses the application settings to
store the connection string I am using at design time. It also appears
that VS 2005 goes to a lot of trouble to not allow me to change the
connection string at run time.

My issue seems to be a common for commerical applications. I want to
have a settings screen were the user enteres the basic connection
information during the install process and have the VB.net application
remember these settings forever.

The best way to do this in my mind is to have my custom settings form
change the application settings (app.config) file. But (connect
string) settings can not have a user scope.

So how to others solve this problem?

 

[Cor Ligthert [MVP]]

David,

Nothing prevents you to do it in another way: in your program, in the
registry in an Ini file, in an XML file, as long as you supply the
connection string to the connectionobject before the connection opens.

Cor

 

[Miha Markic [MVP C#]]

One way would be to let the installer generate (or just inject proper data
into) the application config file with correct data.

 

[William \(Bill\) Vaughn]

First, there are ways to change the system addressed by the connectionstring
at runtime, but an easier approach would be to create a virtual address
that's assigned at the client. This can be implemented with an alias or
changing the hosts file. However, this only addresses one small factor.

Next, while it's an interesting challenge to have a single application
access a user-selectable backend it's not particularly practical. It would
be like writing an exercise book that everyone is expected to be able to
use. This ignores the subtle and significant differences in the abilities
and protocols each data source exposes. Each engine you mention uses
different low-level protocols (which means your connection string would be
different for each), and ADO.NET exposes a specific namespace for many of
these as well as classes that can be used to access the databases in a
generic way. That said, you'll also find that the objects exposed and the
methods on those object are different for each provider--sometimes in pretty
radical ways.

This has been tried before but it's a big challenge that really needs
in-depth knowledge of the interfaces as well as the basic functional
differences between the targeted data sources.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[david.leedom]

What you are saying about the Data Provider makes sense.

I would really like to use the tools provided by VS 2005. Maybe
another way to look at this is to create an application that can be
easily moved to another database, or have versions for each database.
The main issue here is that I would want to keep my front-end logic
independent of the back end data access.

Could I create an assembly for each database (say SQL Server, Firebird
and Oracle) with all the same data sources defined. Then create a
separate project for each database and build them all separately?

For the more mundane things like host and user name stuff I think
editing the application.exe.config file makes the most sense right now.


Does this make sense? May biggest struggle with the whole .NET thing
is getting a grasp on how to approach the application design. I don't
want to get to the deployment part and relize that I am programmed
myself into a corner.

 

[William \(Bill\) Vaughn]

As I said, this has been attempted many times. One approach to which many
developers evolve places the data access logic in a separate class and
exposes custom objects that are implemented to use the custom data access
logic layer. Rocky Lhotka and others have written quite a bit about this
approach.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Jeff Dillon]

Of course they can have user scope. Set it in the .config file