multible DB Connection Strings based on login.

Question

 

Scenario is as Such :

I am developing a WPF application which has seperate DB Servers for Enviornments Test, Non Client, Production.

Each Enviornment will have multiple DB Server (Test Server 1, Test Server 2, Production Server1, Productin Sever2....etc)

I need to provide the selection dropdown in the login as in which enviornment user need to connect (Test Sybase, Test SEQUEL, NC Sybase and so on.) Note : I wont be providing different servers(Test1, Test2...) in the dropdown.

Based on the selection i need to keep handy all the Connection Strings Handy to the code. for eg: If user selects Test Sybase, the test enviornment will have different DB servers Test1, Test 2 etc. During the user session the application might retrieve data from different Test DB Servers.

 

Approach :

   I have added all the connection strings in the Appconfig file as below, and i am loading the appsettings in the Drop down based on selection i have the connection strings loaded from the ConnectionStrings section.

<configuration>
  <appSettings>
    <add key="DB-TSRV-SYBASE" value="Non-Client Test(Sybase)"/>
    <add key="DB-PSRV-SYBASE" value="Non-Client Production(Sybase)"/>
    <add key="DB-EAGAN-SYBASE" value="Production Eagan(Sybase)"/>
    <add key="DB-TSRV-MSSQL" value="Non-Client Test(SQL Server)"/>
    <add key="DB-PSRV-MSSQL" value="Non-Client Production(SQL Server)"/>
    <add key="DB-EAGAN-MSSQL" value="Production Eagan(SQL Server)"/>
  </appSettings>
  <connectionStrings>
    <add name="PSRV1-SYBASE" connectionString="" />
    <add name="PSRV2-SYBASE" connectionString="" />
    <add name="TSRV1-SYBASE" connectionString="" />
    <add name="TSRV2-SYBASE" connectionString="" />
    <add name="EAGAN-SYBASE" connectionString="" />


    <add name="PSRV1-MSSQL" connectionString="" />
    <add name="PSRV2-MSSQL" connectionString="" />
    <add name="TSRV1-MSSQL" connectionString="" />
    <add name="TSRV2-MSSQL" connectionString="" />
    <add name="EAGAN-MSSQL" connectionString="" />
  </connectionStrings>
</configuration>

Question:

For each Successful login there will be 2 or more connectionstrings that will be used, so should i keep the set of connection string in memory using some static object? or is there any better approach?

 

Any response appreciated.

 

Thanks,

Daniel.