Winforms – Change Connection String in App.config at runtime

c#-4.0connection-stringdataadaptersql-server-2008winforms

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.

Best Solution

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:

enter image description here

Related Question