Change Sql Connection String Dynamically

Published on Monday, January 10, 2011

We can easily to read Sql Connection String from config file such as app.config and web.config. But how can we change string items in run time ?

We can use DbConnectionStringBuilder class to implement it:

Firstly, we can get Connection String:

String connStr = ConfigurationManager.ConnectionStrings[�WinTerminalDBConnString�].ConnectionString;

then, transfer the connection string to DBConnectionStringBuilder;

dbConnBuilder.ConnectionString = connStr;

then you can follow the following code to use dbConnBuilder.Add or _dbConnBuilder.Remove methods to change Connection string:

The following code copied from Microsoft MSDN:

static void Main()
    DbConnectionStringBuilder builder =
        new DbConnectionStringBuilder();
    builder.ConnectionString = @�Data Source=c:\MyData\MyDb.mdb�;
    builder.Add(�Provider�, �Microsoft.Jet.Oledb.4.0�);
    builder.Add(�Jet OLEDB:Database Password�, �*******�);
    builder.Add(�Jet OLEDB:System Database�,
    // Set up row-level locking.
    builder.Add(�Jet OLEDB:Database Locking Mode�, 1);
// The DbConnectionStringBuilder class 
    // is database agnostic, so it�s possible to 
    // build any type of connection string using 
    // this class.

    // The ConnectionString property may have been 
    // formatted by the DbConnectionStringBuilder class.
    OleDbConnection oledbConnect = new

    // Use the same DbConnectionStringBuilder to create 
    // a SqlConnection object.
    builder.Add(�integrated security�, true);
    builder.Add(�Initial Catalog�, �AdventureWorks�);
    builder.Add(�Data Source�, �(local)�);

    SqlConnection sqlConnect = new

    // Pass the DbConnectionStringBuilder an existing 
    // connection string, and you can retrieve and
    // modify any of the elements.
    builder.ConnectionString = �server=(local);user id=*******;� +
        �password=*******;initial catalog=AdventureWorks�;
    builder[�Server�] = �.�;
    builder.Remove(�User ID�);

    // Note that calling Remove on a nonexistent item doesn�t
    // throw an exception.

    // Setting the indexer adds the value if 
    // necessary.
    builder[�Integrated Security�] = true;
    builder[�User ID�] = �Hello�;

    Console.WriteLine(�Press Enter to finish.�);