Changing connection string dynamically

I have a scenario where i am getting dynamically generated databases. I want to create a connection string for each database. So only initial catalog parameter will be changing. Other parameters i ll get it from web config file. It is already having all entries of connection string. I want to change only initial catalog part dynamically based on database created.

Please suggest is there any way to easily to do this.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

use the SqlConnectionStringBuilder class

SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder(ConnectionStringFromConfig)
   { InitialCatalog = "your CatalogName" }; // you can add other parameters.

then use this connectionstring where required

conn.ConnectionString;

Method 2

Use SqlConnectionStringBuilder. Set it up from the config file and change the InitialCatalog for each database.

Method 3

Try with this line of code, it will change the connection string in web config file as you want. I did this with TextBox,

 bool isNew = false;
    string path = Server.MapPath("~/Web.Config");
    XmlDocument doc = new XmlDocument();
    doc.Load(path);
    XmlNodeList list = doc.DocumentElement.SelectNodes(string.Format("connectionStrings/add[@name='{0}']", "conn"));
    XmlNode node;
    isNew = list.Count == 0;
    if (isNew)
    {
        node = doc.CreateNode(XmlNodeType.Element, "add", null);
        XmlAttribute attribute = doc.CreateAttribute("name");
        attribute.Value = "conn";
        node.Attributes.Append(attribute);

        attribute = doc.CreateAttribute("connectionString");
        attribute.Value = "";
        node.Attributes.Append(attribute);

        attribute = doc.CreateAttribute("providerName");
        attribute.Value = "System.Data.SqlClient";
        node.Attributes.Append(attribute);
    }
    else
    {
        node = list[0];
    }
    string conString = node.Attributes["connectionString"].Value;
    SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder(conString);
    conStringBuilder.InitialCatalog = T1.Text;
    node.Attributes["connectionString"].Value = conStringBuilder.ConnectionString;
    if (isNew)
    {
        doc.DocumentElement.SelectNodes("connectionStrings")[0].AppendChild(node);
    }
    doc.Save(path);

This may be a hint for your hurdle.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0