Modifying connection string with variables

Question

so my goal is to have a "settings form" which allows users to edit the connection string (by changing the database name / server location.

The reason for this is that it it needs to be able to changed when the server locations changes shortly by someone who may not have any C# experience (Front-End GUI).

I've created the connection strings in app.config but cannot find a way to assign variables inside the conn string that can be changed? I've created some application wide settings using the project properties. This is my app.config

<connectionStrings>
    <add name="xxxx"
        connectionString="Data Source=ServerIP;Initial Catalog=DBName;Persist Security Info=True;User ID=user;Password=password"
        providerName="System.Data.SqlClient" />
</connectionStrings>
<applicationSettings>
    <xxx.Properties.Settings>
        <setting name="ServerIP" serializeAs="String">
            <value />
        </setting>
        <setting name="DBName" serializeAs="String">
            <value />
        </setting>
    </xxx.Properties.Settings>
</applicationSettings>

You might want to take a look on this: http://stackoverflow.com/questions/719928/how-do-you-modify-the-web-config-appsettings-at-runtime — Hatjhie, Oct 08 '14 at 01:07
hi, do you know if I can use that for a connection string though? Thanks — AlexP2014, Oct 08 '14 at 03:57

score 2 · Answer 1 · answered Aug 25 '21 at 20:47

Try the SqlConnectionStringBuilder class.

using System.Data;
using System.Data.SqlClient;

class Program
{
static void Main()
{
    // Create a new SqlConnectionStringBuilder and
    // initialize it with a few name/value pairs.
    SqlConnectionStringBuilder builder =
        new SqlConnectionStringBuilder(GetConnectionString());

    // The input connection string used the
    // Server key, but the new connection string uses
    // the well-known Data Source key instead.
    Console.WriteLine(builder.ConnectionString);

    // Pass the SqlConnectionStringBuilder an existing
    // connection string, and you can retrieve and
    // modify any of the elements.
    builder.ConnectionString = "server=(local);user id=ab;" +
        "password= a!Pass113;initial catalog=AdventureWorks";

    // Now that the connection string has been parsed,
    // you can work with individual items.
    Console.WriteLine(builder.Password);
    builder.Password = "new@1Password";
    builder.AsynchronousProcessing = true;

    // You can refer to connection keys using strings,
    // as well. When you use this technique (the default
    // Item property in Visual Basic, or the indexer in C#),
    // you can specify any synonym for the connection string key
    // name.
    builder["Server"] = ".";
    builder["Connect Timeout"] = 1000;
    builder["Trusted_Connection"] = true;
    Console.WriteLine(builder.ConnectionString);

    Console.WriteLine("Press Enter to finish.");
    Console.ReadLine();
}

private static string GetConnectionString()
{
    // To avoid storing the connection string in your code,
    // you can retrieve it from a configuration file.
    return "Server=(local);Integrated Security=SSPI;" +
        "Initial Catalog=AdventureWorks";
}

}

score 1 · Accepted Answer · answered Oct 08 '14 at 04:09

1

One way to accomplish this would be to use placeholders ({0} and {1}) for those parts of the connection string in the config file, like so:

Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID=user;Password=password

And then fill them in via string.Format when you read the connection string in your code, as in the following example. (Note: This assumes that you've added a reference to System.Configuration.dll, and that you've retrieved the application settings into two variables, serverIP and dbName.)

using System.Configuration;

...

string connectionString = string.Format(
    ConfigurationManager.ConnectionStrings["xxxx"].ConnectionString,
    serverIP,
    dbName);

answered Oct 08 '14 at 04:09

Troy Gizzi

2,480
1
14
15

okay great thanks very much, ill give that a shot. So basically if I wanted more variables, I'd just add more placeholders {3} and {4} etc in order of the variables created? – AlexP2014 Oct 08 '14 at 04:31
1

Yes, but don't skip `{2}`. :-) – Troy Gizzi Oct 08 '14 at 04:36
just quickly: do you know if theres a way to use that string.format with SqLConnection type? As I'm using that to then open the connection: i.e. SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AlexDatabaseConnection"].ConnectionString, AlexDBServerIP); connection.Open(); It doesnt recognise just adding the variable afterwards. Thanks – AlexP2014 Oct 08 '14 at 05:24
1

After setting `connectionString` from above, you should be able to do this: `SqlConnection connection = new SqlConnection(connectionString); connection.Open();` – Troy Gizzi Oct 08 '14 at 06:02
Hi Troy, where should "string connectionString = string.Format( ConfigurationManager.ConnectionStrings["xxxx"].ConnectionString, serverIP, dbName);" be placed? In Program.cs? Thank you. – taylorswiftfan Nov 06 '16 at 00:45
@taylorswiftfan You probably figured this out long ago, but... it can go pretty much anywhere you need to use the connection string. Program.cs is okay for a small app, but for larger applications you'd probably want that code in a separate [data access layer](https://en.wikipedia.org/wiki/Data_access_layer). – Troy Gizzi Jan 12 '17 at 17:05

Modifying connection string with variables

2 Answers2

Linked