Working with Connection Strings

Connection strings are typically specific to the provider for which they are intended. This makes it difficult to create a truly provider-agnostic application, as you'll have to have multiple connection string formats to manage all of the different types of providers to which your application can connect.

This dilemma is partially solved by the DbConnectionStringBuilder class. Each provider has its own specific connection string builder class that provides the developer with a standardized interface for creating a connection string. When the ConnectionString property is accessed, the output is specific to the underlying provider. The DbConnectionStringBuilder class provides the basic mechanism for adding name/value pairs of information to a connection string. Classes like SqlConnectionStringBuilder provide additional methods and properties that allow you to configure settings specific to the provider.

When you add a key and value to a connection string builder, it takes the key and interprets it in a way that is specific to the underlying provider. When you examine the connection string property of the builder, the key name might have been changed to reflect the needs of the provider. For example, if you set the "server" key on the SQL client connection string builder, it won't include that key in the output. But the same key on an ODBC or an OLE DB connection string builder will be included in the connection string.

The code in Listing 19.3 illustrates using DbConnectionStringBuilder to use provider-agnostic keywords to create connection strings and output them.

Listing 19.3. Using the DbConnectionStringBuilder

using System; using System.Data; using System.Data.Common; using System.Collections.Generic; using System.Text; namespace ConnBuilder { class Program { static void Main(string[] args) {     DataTable factories = DbProviderFactories.GetFactoryClasses();     foreach (DataRow row in factories.Rows)     {         if ((string)row["InvariantName"] != "Microsoft.SqlServerCe.Client")         {             DbProviderFactory factory = DbProviderFactories.GetFactory((string)row["InvariantName"]);             DbConnectionStringBuilder connBuilder = factory.CreateConnectionStringBuilder();             connBuilder.Add("server", "localhost");             connBuilder.Add("user", "sa");             connBuilder.Add("password", "secret");             connBuilder.Add("data source", "SampleDB");             Console.WriteLine("[{0}] {1}",                 row["InvariantName"],                 connBuilder.ConnectionString);         }     }     Console.ReadLine(); } } } 

The output from the preceding code is as follows:

[System.Data.Odbc] server=localhost;user=sa;password=secret;data source=SampleDB [System.Data.OleDb] Data Source=SampleDB;server=localhost;user=sa;password=secret [System.Data.OracleClient] Data Source=SampleDB;User ID=sa;Password=secret [System.Data.SqlClient] Data Source=SampleDB;User ID=sa;Password=secret 

As you can see, the connection string builder for each data provider produced a connection string specific to the needs of that particular provider.