Multiple SQL Server connection strings in app.config file

Question

Hi guys,

I'm interested in displaying in a Windows Forms app a list of N radio buttons for the user to choose a target database server. I would like to add the SQL Server connection strings in the app.config file, so they are read by the app at runtime and rendered in the windows form as radio buttons.

At first I thought of using a delimiter to separate the connections

  <appSettings>
    <add key="ConnectionString" value="connection1|user id=user;password=123;server=10.0.0.1;database=myDatabase;connection timeout=30|connection2|user id=user;password=123;server=10.0.0.2;database=myDatabase;connection timeout=30"/>
</appSettings>

And then split the key value pairs.

Is it possible to do this in a different way?

Answer 1

Yes, it is possible to do this in another way. Check the connectionStrings section that you can make in the app.config file.

<configuration>
   <connectionStrings>
       <add name="" connectionString=""/>
        <add name="" connectionString=""/>
    </connectionStrings>
</configuration>

Answer 2

Use the connectionStrings section to define your connection strings.

<connectionStrings>
    <add name="connection1" connectionString="user id=user;password=123;server=10.0.0.1;database=myDatabase;connection timeout=30"/>
    <add name="connection2" connectionString="user id=user;password=123;server=10.0.0.2;database=myDatabase;connection timeout=30"/>
</connectionStrings>

Answer 3

You can use the AppSettings class, get a list of all keys that start with ConnectionString and display them.

Your config file will look like this:

<appSettings>
  <add key="ConnectionString_Name1" value="..."/>
  <add key="ConnectionString_Name2" value="..."/>
  <add key="ConnectionString_Name3" value="..."/>
</appSettings>

You can get the name, by splitting the key name (using "_" in this example).

BTW: You should also use the ConnectionStrings section, you are only interrested in connection strings.

Answer 4

To find all defined connection strings from your app.config, use the ConfigurationManager (from System.Configuration).

It has an enumeration: ConfigurationManager.ConnectionStrings which contains all entries in your <connectionStrings>.

You can loop over it with this code:

foreach(ConnectionStringSettings css in ConfigurationManager.ConnectionStrings)
{
   string name = css.Name;
   string connString = css.ConnectionString;
   string provider = css.ProviderName;
}

The Name is just the symbolic name you give your connection string - it can be anything, really.

The ConnectionString is the connection string itself.

The ProviderName is the name of the provider for the connection, e.g. System.Data.SqlClient for SQL Server (and others for other database system). If you omit the providerName= attribute from your connection string in config, it defaults to SQL Server (System.Data.SqlClient).

Marc