Modify default connection string for SQL Server Compact Edition

Question

When I add a database to a program, Visual Studio automatically creates the following connection string:

 <connectionStrings>
    <add name="TeamSortingTool.Properties.Settings.PlayerTeamConnectionString" connectionString="Data Source=|DataDirectory|\PlayerTeam.sdf" providerName="Microsoft.SqlServerCe.Client.3.5" />
    <add name="PlayerTeamEntities" connectionString="metadata=res://*/edmPlayerTeam.csdl|res://*/edmPlayerTeam.ssdl|res://*/edmPlayerTeam.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=|DataDirectory|\PlayerTeam.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

I am trying to allow the user to select the database that they would like to open. How do I modify the connection string programmatically without losing my associated dataset and tableadapter?

Thanks in advance :)

Answer 1

you can use that method to call your connection string from your App.Config file to be used in the data connection or adapter

 public string GetConnectionStringByName()
        {
            string returnValue = null;
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Connection Name Here"];
            if (settings != null)
                returnValue = settings.ConnectionString;
            return returnValue;
        }

when you add a new database to your project it takes the same connection Name of the existing database plus a number by default. so you can make the connection string as a variable and add the number to it when you add a new database.

Edit

By considering you will add databases to your project grammatically, then if you added a new data base. it will take that connection string's Name:

name="TeamSortingTool.Properties.Settings.PlayerTeamConnectionString1" 

as it add a number at the end of the name of the existing database so, you can invest the method i provided, to add the specified connection string Something like that:

public string GetConnectionStringByName(int DB_Number)
        {
            string returnValue = null;
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Connection Name Here"];
            if (settings != null)
                returnValue = settings.ConnectionString+Convert.ToString(DB_Number);
            return returnValue;
        }

as the DB_Number variable could be saved in an XML file in order not to be loosed and its increased each time you add database.

So, if you add a new database to the existing 1 the connection Name should be:

name="TeamSortingTool.Properties.Settings.PlayerTeamConnectionString1" 

and if you added another database the DB_Number will be increased by 1 to change the connection name to be :

name="TeamSortingTool.Properties.Settings.PlayerTeamConnectionString2" 

and enable the user to choose with connection string he wants to use.

on the other hand . if your databases added manually before the application runs. it will be easier to Handle as what you just have to do is to save your connection string in a data structure as an array and call the wanted connection to use

that is what i figured out from your question