Enhancements done for storing connection strings in Visual Studio 2005

Introduction

This article contains some enhancements done for storing connection strings in Visual Studio 2005 and the usage of ConnectionStringBuilder. We are always curious about storing connection strings whenever we work on ADO.NET. "Where should I store my connection strings?" - might be a FAQ at least for a beginner.

Storing connection strings in .NET 1.x

In the .NET Framework 1.x, configuration files (such as the web.config file) support a section named <appSettings>, which is used to store name/value pairs. All these values populate the AppSettings collection and can be easily retrieved programmatically, as shown here:

string connString = ConfigurationSettings.AppSettings["MyNWind"];

This is a common scenario of storing the connection string in our day to day application. The advantage is that you can keep the connection string out of the compiled code. This way of storing the connection string also makes the information global to the application which can be easily called from any module. So, we are sure that there no issues related to the connection strings. You can store any data in the <appSettings> section that can be rendered with a flat name/value pair.

Enhancements done in Whidbey

In Whidbey, Microsoft has introduced a <connectionStrings> section that is created to contain the connection strings (such as SQL connection strings). The section is named <connectionStrings>. Its structure is shown here:

<connectionStrings>
    <add name="EmployeeDB"
      connectionString="SERVER=…;DATABASE=…;UID=…;PWD=…;" />
    <add name="OrdersDB"
      connectionString="~\DATA\CustDB.mdb" />
</connectionStrings>

You can manipulate the contents of the section by using <add>, <remove> and <clear> nodes. You use an <add> node to add a new connection string, <remove> to remove a previously defined connection and <clear> to reset all connections and create a new collection. By placing a web.config file in each of the application’s directories, you can customize the collection of connection strings that are visible to the pages in the directory. Each stored connection is identified with a name. This name references the actual connection parameters throughout the application. The <connectionStrings> section is a global and centralized repository of connection information. Retrieving connection strings programmatically All the connection strings defined in the web.config file are loaded into the new configuration - Settings.ConnectionStrings collection. If you are programmatically setting the connection string of an ADO.NET object, this feature makes your code flexible and easy to maintain. For example, consider the following web.config file:

<configuration>
    <connectionStrings>
        <add name="MyNWind"
            connectionString="SERVER=…;DATABASE=northwind;UID=…;PWD=…" />
    </connectionStrings>
</configuration>

It registers a connection that points to the Northwind database on a given server and with certain credentials. To physically open that connection, the following code is required:

string cnStr;
cnStr = 
  ConfigurationSettings.ConnectionStrings["MyNWind"].ConnectionString;
SqlConnection cnObj = new SqlConnection(cnStr);

Apart from this, we can also encrypt the connection strings. Encryption is optional, and you can enable it for any configuration section by referencing the name of the section in the web.config file. You can use the newest version of a popular system tool - aspnet_regiis.exe - or write your own tool using ASP.NET 2.0 configuration API.

aspnet_regiis.exe –pe connectionStrings –app /myapp

Creating connection strings in .NET Framework 1.x

In the .NET Framework 1.x, if we want to programmatically write the connectionstring, we do something like this:

//serverName,uid,pwd are provided from the user
public void GetData(string serverName, string uid, string pwd)
{
    String connectionString = String.Empty;
    connectionString += ("Data Source = " + serverName +";");
    connectionString += ("User ID = " + uid +";");
    connectionString += ("Password = " + pwd +";");
    SqlConnection c = new SqlConnection (connectionString);
    c.Open();
    //Do something with the connection
}

This has no compile time checking problem and can even raise various run time issues.

Enhacements in ADO.NET 2.0

ADO.NET 2.0 introduces the use of ConnectionStringBuilder for building connection strings that are less prone to errors and the best part is that it does compile time checking.

public void AccessData(string serverName, string uid, string pwd)
{
    String connectionString = String.Empty;
    //Get serverName from the user
    SqlConnectionStringBuilder conStrbuilder = 
                                        new SqlConnectionStringBuilder();
    conStrbuilder.DataSource = serverName;
    conStrbuilder.UserID = uid;
    conStrbuilder.Password = pwd;
    SqlConnection c = new SqlConnection (conStrbuilder.ConnectionString);
    c.Open();
    //Do something with the connection
}

Happy coding with Visual Studio 2005!