CODEWITHSUNDEEP
c#connection-stringappsettings
AlexP2014
AlexP2014

Reputation: 211

Modifying connection string with variables

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>

Upvotes: 4

Views: 5732

Answers (2)

Chris Weber
Chris Weber

Reputation: 5836

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";
}

}

Upvotes: 2

Troy Gizzi
Troy Gizzi

Reputation: 2520

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);

Upvotes: 1

Related Questions