Change Connection String in App.config at runtime

Question

The code below serves to change connection string in App.config at runtime, I found it here but this code did not work for me on Visual Studio 2010 and SQL Server 2008, I could not open the connection to the Northwind database.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.Xml;

namespace MyNameSpace
{
    public partial class FrmConnectionTest : Form
    {
        public FrmConnectionTest()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                //Constructing connection string from the inputs
                StringBuilder Con = new StringBuilder("Data Source=");
                Con.Append(TxtServer.Text);
                Con.Append(";Initial Catalog=");
                Con.Append(TxtDatabase.Text);
                Con.Append(";Integrated Security=SSPI;");
                string strCon = Con.ToString();
                updateConfigFile(strCon);
                //Create new sql connection
                SqlConnection Db = new SqlConnection();
                //to refresh connection string each time else it will use             previous connection string
                ConfigurationManager.RefreshSection("connectionStrings");
                Db.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ToString();
                //To check new connection string is working or not
                SqlDataAdapter da = new SqlDataAdapter("select * from employee");
                DataTable dt = new DataTable();
                da.Fill(dt);
                CmbTestValue.DataSource = dt;
                CmbTestValue.DisplayMember = "EmployeeID";
            }
            catch (Exception E)
            {
                MessageBox.Show(ConfigurationManager.ConnectionStrings["con"].ToString() + ".This is invalid connection", "Incorrect server/Database");
            }
        }
        public void updateConfigFile(string con)
        {
            //updating config file
            XmlDocument XmlDoc = new XmlDocument();
            //Loading the Config file
            XmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
            foreach (XmlElement xElement in XmlDoc.DocumentElement)
            {
                if (xElement.Name == "connectionStrings")
                {
                    //setting the coonection string
                    xElement.FirstChild.Attributes[2].Value = con;
                }
            }
            //writing the connection string in config file
            XmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
        }
    }
}

Using Visual Studio 2010 and SQL Server2008, I got 2 errors for the next line:

            SqlDataAdapter da = new SqlDataAdapter("select * from employee");

• Error 1 The best overloaded method match for 'System.Data.SqlClient.SqlDataAdapter.SqlDataAdapter(System.Data.SqlClient.SqlCommand)' has some invalid arguments

• Error 2 Argument 1: cannot convert from 'string' to 'System.Data.SqlClient.SqlCommand'

Is there any solution to this issue? Thank you.

Answer 1

The error is telling you that you are passing incorrect parameters to your SqlDataAdapter. I think the proper call would be:

SqlDataAdapter da = new SqlDataAdapter("select * from employee", Db); 

Edit

It looks like you're creating your connection string from within your program, saving it to your config file, then reading it out of our config file right before you create your SqlDataAdapter. So, when you debug this line:

Db.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ToString();  

Double check that Db.ConnectionString actually contains a connection string.

The other thing to do is open up your SQL Server Management Studio and confirm you can connect to the Northwind database from there. Including/alternatively, in Visual Studio, open your "Server Explorer" window and confirm you can create a Data Connection to Northwind by clicking Add Connection and then setting the connection property window to your server and dropping down the combobox to see if it populates with your databases:

Answer 2

Take a look at the available constructors of the SqlDataAdapter class.

There is no constructor overload that accepts just an SQL String.
You need to use one of the other overloads.
For example, there is one that needs an SQL String and a SqlConnection object. To use it, change your code like this:

SqlDataAdapter da = new SqlDataAdapter("select * from employee", Db);

---

EDIT:

As BradRem already mentioned in his comment, try a different connection string.
If his example doesn't work for you, you can find more possible examples at http://connectionstrings.com/sql-server-2008.

Do you really have a database called Northwind on your server?
Does the Windows user on your current machine have permissions on the server to access the database? (that's what Integrated Security=SSPI means - your current Windows user is used to access the database!)