Choosing connectionstring in VB.NET application at startup

Question

I have a VB.NET application with a connection to an SQL Server 2003. On the server there are two databases, MyDatabase and MyDatabase_Test. What I would like to do is to show a dialog when the program starts that let's the user choose which database to use. My idea is to create a new form as the starup form that sets this property and then launches the main form.

Currently the connectionstring is specified in the application config file. Best would be if I can specify two different connection strings in that file to choose from, but for now it is also acceptable with other solutions like hardcoding the two connectionstrings into the startup form.

EDIT: In the dataset.xsd file there seems to be the relevant part

<Connections>
          <Connection AppSettingsObjectName="MySettings" AppSettingsPropertyName="MyDatabase_ConnectionString" ConnectionStringObject="" IsAppSettingsProperty="true" Modifier="Assembly" Name="MyDatabase_ConnectionString(MySettings)" ParameterPrefix="@" PropertyReference="ApplicationSettings.MyProgram.My.MySettings.GlobalReference.Default.MyDatabase_ConnectionString" Provider="System.Data.SqlClient" />
</Connections>

But how do I change it at runtime? The closest i could find is changing which connection is used for every single TableAdapter but that doesn't seem very optimal.

EDIT2: I agree that a modal dialog at startup would be better, but where would i launch it so that it is done before the database connection is initiated?

EDIT3: Eventually I "solved" it by removing the ReadOnly from the settings file. This will be removed each time the file is auto-generated though, so it's not optimal.

EDIT4: A better solution seemed to be using a user scoped string instead of a connection string to link the dataset and fetched the value for that string from the two application scoped ConnectionStrings.

Answer 1

I don't understand the question, or rather I can't see any question.
Are you having any specific problems with doing this or just wondering if it's an ok design?

Having multiple connection strings in the config file and then choosing between them at startup should work fine. The only thing I might do different from how you describe it that I'd probably keep the main form as the startup form and then do something like pop up a modal dialog box straight away where the user selects the connection.

Answer 2

You could just set the TableAdapter.Connection.ConnectionString property right before you use it every time. Find the section in your app.config which defines the connection strings and add another:

<connectionStrings>
      <add name="Live"
        connectionString="Data Source=svr;Initial Catalog=Live;..."
        providerName="System.Data.SqlClient" />
        <add name="Dev"
        connectionString="Data Source=svr;Initial Catalog=Dev;..."
        providerName="System.Data.SqlClient" />
   </connectionStrings>

on startup, populate a global variable that reads out of one setting or the other based on the users choice

Answer 3

After some more consideration hacking the settings file to remove the ReadOnly property seems like the best solution.

It is not possible to define a ConnectionString at the user scope, only at the application scope. It is possible to use a string instead of a ConnectionString, the program will run fine, but it causes a lot of IDE issues and Visual Studio exceptions during the auto-compiling.