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.

Maybe I misread your question. I think you have three questions here: 1. how to retrieve the connection strings? 2. how to bind it to a combo box? 3. how to set the selected connection string? Please clarify what's the problem... — Yuval Peled
– Yuval Peled, Commented Dec 15, 2011 at 6:56
@YuvalPeled I can retrieve the connection strings. I can bind them to the combo box. So particularly, my problem lies in your question number 3. Thanks for the help. — Nathan
– Nathan, Commented Dec 15, 2011 at 7:02

Community · Accepted Answer · 2017-05-23 10:08:26Z

2

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

edited May 23, 2017 at 10:08

CommunityBot

11 silver badge

answered Dec 15, 2011 at 6:46

Adam

16.2k7 gold badges68 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adam Over a year ago

You can load your connection strings from anywhere, I assume you know how to load them in a combo box? Then it is just taking the selectedvalue and putting it in the connectionString variable as shown above.

Nathan Over a year ago

I am currently using DBML and Linq to SQL. So is there any code for that? I'm practically new to this, I'm sorry.

Adam Over a year ago

Thats a trickier one. Luckily someone answered it on StackOverflow already: stackoverflow.com/a/1188971/540339 . Does that work for you?

Nathan Over a year ago

I'm definitely gonna try that out. Thanks a lot.

user1088520 · Accepted Answer · 2011-12-15 06:48:50Z

0

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

answered Dec 15, 2011 at 6:48

user1088520

Comments

Yuval Peled · Accepted Answer · 2011-12-15 06:48:57Z

0

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} />

answered Dec 15, 2011 at 6:48

Yuval Peled

5,0388 gold badges33 silver badges36 bronze badges

Comments

Lucian · Accepted Answer · 2011-12-15 06:50:03Z

0

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);

}

answered Dec 15, 2011 at 6:50

Lucian

3,5642 gold badges23 silver badges21 bronze badges

2 Comments

Nathan Over a year ago

where do i return this? how do I put this in the Web.config file? sorry, I'm new to this.

Lucian Over a year ago

ah ok. I'm not that good at Silverlight and services to answer your question. I was just giving you an example to show you that it is possible to create a connection string on the fly. You don't have to use my code.

Sai Kalyan Kumar Akshinthala · Accepted Answer · 2011-12-15 07:19:06Z

0

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");

    }

edited Dec 15, 2011 at 7:19

answered Dec 15, 2011 at 6:44

Sai Kalyan Kumar Akshinthala

11.9k8 gold badges45 silver badges68 bronze badges

2 Comments

Nathan Over a year ago

does this change my connection string? I think this just changes ConnString's value.

Sai Kalyan Kumar Akshinthala Over a year ago

@Nathan, see the code now. Get the connection string variable value where ever you want.

Tadit Dash · Accepted Answer · 2011-12-15 08:03:20Z

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 .";
        }
    }

Collectives™ on Stack Overflow

Change connection string from C# code?

6 Answers 6

4 Comments

Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related