Setting up a ConnectionStrings in .NET

ConnectionStringBuilder :

Tip for today is the ConnectionStringBuilder class. This is quite an unknown class but can result very useful in building your connectionstring. Normally, you'd take a connection string and pass it directly to your connection objects such as SqlConnection or DbConnection classes directly.

Quick example from msdn :

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

As you can note from this classical example above, the connectionstring is passed directly to the SqlConnection object's constructor. While this is fine, any error in the connection and it's quite easy to make a mistake, won't be known until the connection is opened.

Further more, if you want to pass some additional values, or modify existing values in the connectionstring it can be time consuming and again error prone and more effort than you want to invest.

Say hello to the ConnectionStringBuilder. What this class does for you is that it facilitates parsing a connection string and also provides named properties you can simply set in code or whose values you want retrieved. A classic code example from msdn itself :

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

Note: the same applies for the generic non SqlClient class DbConnection and you'd use the apposite DbConnectionStringBuilder class.

Lastly, retrieving the connectionString itself can be facilitated by using Server explorer, after having made the connection to the database visually in VS.NET you need to select your database in solution explorer and right click - Properties. From the property grid you can see the connectionstring VS.NET is using. That is the connectionstring you want.

Alternatively you can use create a DataLink and copy the autogenerated connectionstring created by the DataLink. Follow the url in the references section below.

Reference material :
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx
http://msdn.microsoft.com/en-us/library/ms718102(v=vs.85).aspx ( DataLink )
http://msdn.microsoft.com/en-us/library/33wwc2yw(v=VS.80).aspx (Server Explorer)