Change connection string from C# code?

Question

Is there any way to change a connection string programatically? I mean user can pick which site he wants to use using a combobox, and load users on that particular site?

code for config as follows

<add key ="sampleconnectionstring" value="Server=sampleserver;Database=sampledb;User ID=sampleid;Password=samplepassword;Trusted_Connection=False;Encrypt=True;"/>
<add key ="sampleconnectionstring1" value="Server=sampleserver1;Database=sampledb1;User ID=sampleid1;Password=samplepassword;Trusted_Connection=False;Encrypt=True;"/>
<add key ="sampleconnectionstring2" value="Server=sampleserver2;Database=sampledb2;User ID=sampleid2;Password=;Trusted_Connection=False;Encrypt=True;"/>
<add key ="sampleconnectionstring3" value="Server=sampleserver3;Database=sampledb3;User ID=sampleid3;Password=samplepasswrd;Trusted_Connection=False;Encrypt=True;"/>

is there a programmatical(if that's a word) way of changing between this connection strings depending on the selected item in the combo box? Any help would be appreciated. Thanks.

Answer 1

Depending on what you are using for the database you can load any connection string you want when loading.

For entity framework for example

Entities model = new Entities(connectionString);

For ADO.NET

  using (SqlConnection connection =
        new SqlConnection(connectionString))
    {

    }

For Linq To SQL

Use:

MyDataClassesDataContext db = new MyDataClassesDataContext(dynamicConnString);

For a LinqDataSource, intercept the ContextCreating event and create the DataContext manually as above:

protected void LinqDataSource_ContextCreating(object sender, LinqDataSourceContextEventArgs e)
{
    e.ObjectInstance = new MyDataClassesDataContext (dynamicConnString);
}

(Linq to SQL example taken from: Linq to Sql - Set connection string dynamically based on environment variable

Answer 2

Its there a way, just set the connection string on Combobox selectedindex changed event. See the below code. First set the default connection string as the first string in appconfig, because at form load the selectedindexchanged event will not fire. Get the ConnString for the connection where ever you want, because its a public property that can be accessable.

    public string ConnString { get; set; }
    private void ComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        if (CBox.SelectedIndex == 0)
        {
            ConnString = getAppSetting("sampleconnectionstring");
        }
        else if (CBox.SelectedIndex == 1)
        {
            ConnString = getAppSetting("sampleconnectionstring1");
        }
        else if (CBox.SelectedIndex == 2)
        {
            ConnString = getAppSetting("sampleconnectionstring2");
        }
        else if (CBox.SelectedIndex == 3)
        {
            ConnString = getAppSetting("sampleconnectionstring3");
        }
    }
    private string getAppSetting(string strKey)
    {
        string strValue = string.Empty;
        XmlReaderSettings settings = new XmlReaderSettings();
        settings.XmlResolver = new XmlXapResolver();
        XmlReader reader = XmlReader.Create("ServiceReferences.ClientConfig");
        reader.MoveToContent();
        while (reader.Read())
        {
            if (reader.NodeType == XmlNodeType.Element && reader.Name == "add")
            {
                if (reader.HasAttributes)
                {
                    strValue = reader.GetAttribute("key");
                    if (!string.IsNullOrEmpty(strValue) && strValue == strKey)
                    {
                        strValue = reader.GetAttribute("value");
                        return strValue;
                    }
                }
            }
        }
        return strValue;
    }

    private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
    {
        ConnString = getAppSetting("sampleconnectionstring");

    }

Answer 3

Your can select connection strings by key directly if each the key is equal with the value of one of your combobox items.

Answer 4

You can get the list by using the configurationmanager connectionstring collection. Then you can bind the combo box to it. something like:

<ComboBox ItemsSource={Binding ConnectionStrings} DisplayMemberPath="Name" SelectedItem={Binding Path=SelectedConnectionString, Mode=TwoWay} /> 

Answer 5

Remember that the connection string is just a string. You can concatenate other string to it:

string CreateConnectionString(string server, string sampledb,  string userid, string password)
{
   return string.Format("Server={0};Database={1};User ID={2};Password={3}Trusted_Connection=False;Encrypt=True;",server,sampledb,userid,password);

}

Answer 6

If I am not wrong, u must be doing like this in web.config file.

<appSettings>
     <add key = "sampleconnectionstring" ..../>
     <add key = "sampleconnectionstring1" ..../>
     <add key = "sampleconnectionstring2" ..../>
     <add key = "sampleconnectionstring3" ..../>
</appSettings>

and u want to change the strings programmatically.

So, u can do it like this---- first of all u can keep the key names in the value field of the combo box. Then, in dropdown/combo box selected index changed write like below...

  protected void ddlUrDropdown_SelectedIndexChanged(object sender, EventArgs e)
  {
        // Checking whether first Item "Select......." (index = 0) is selected or not.
        if (ddlUrDropdown.SelectedIndex != 0)
        {
            // Getting the Selected "Country_Id".
            string keyName = ddlUrDropdown.SelectedValue;
            string connectionString = ConfigurationSettings.AppSettings[keyName];
            // Where keyName can take values like- "sampleconnectionstring" or "sampleconnectionstring1" or "sampleconnectionstring2" or "sampleconnectionstring3"
            // After getting the connection string according to selected item of combo box, u can create connection and do whatever u want, like below...
            using(SqlConnection connection = new SqlConnection(connectionString))
            {
                  connection.Open();
                  // perform work with connection
             }         
        }
        // Error Message displayed when first Item "Select......." (index = 0) is selected.
        else
        {
            lblError.Text = "Select any item .";
        }
    }