Choice Between Connection DataSource C#

I've got a dataAdapter, dataSet and BindingSource on my forms to connect to an SQL database to show the data in the form, and it all works fine. However, I need to have 2 options of what server to connect to. e.g ISSP\SQLEXPRESS and MY-WEB. I don't know ho开发者_JS百科w to do this and wondered whether anyone could give me some help on where to start? Thanks :) But when using the variables it says that it can't connect.

I'm using the code below but it says that it can't connect, so i'm wondering whether i'm refering to the variable wrong?

sqlConnectionNW.ConnectionString = "Data Source=@server;Initial Catalog=Northwind;Integrated Security=True";

When I change the code to the following, it works perfectly.

sqlConnectionNW.ConnectionString = "Data Source=ISSP\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

Firstly add both connection settings to your app config file:

<connectionStrings>
    <add name="Test"
     connectionString="Data Source=ISSP\SQLEXPRESS;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
     providerName="System.Data.SqlClient" />
    <add name="Production"
     connectionString="Data Source=MY-WEB;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
     providerName="System.Data.SqlClient" />
</connectionStrings>

In your application add a ComboBox to the form and (called uiConnection in this example) and add the following code to populate it:

uiConnection.ValueMember = "Name";
foreach (ConnectionStringSettings con in ConfigurationManager.ConnectionStrings)
    {
        uiConnection.Items.Add(con);
    }

You can now use the drop down to specify which database to connect to. When you fetch your data do the following to get the correct connection string:

ConnectionStringSettings connection = uiConnection.SelectedItem as ConnectionStringSettings
string queryString = "SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");

If the data is the same on both then I think the easiest way is to just change the connection string and switch it when the other option is selected.

Example: Data Source="Server\DBInstance";Initial Catalog="DatabaseName";User ID="user";Password="password";

There should be an overload on the DataAdapter which takes an SqlConnection. You can create this manually and pass it into your data-adapter when you connect to choose different connections.

If you look into your dataset.designer.cs and find the constructor, you will see that this is what the default constructor is doing from your appsettings.