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:
You can manipulate the contents of the section by using
<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:
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:
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:
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);
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;
SqlConnectionStringBuilder conStrbuilder =
conStrbuilder.DataSource = serverName;
conStrbuilder.UserID = uid;
conStrbuilder.Password = pwd;
SqlConnection c = new SqlConnection (conStrbuilder.ConnectionString);
Happy coding with Visual Studio 2005!