How to store the database connection in webconfig file in ASP.NET

ASP.NET provides a configuration system we can use to keep our applications flexible at runtime. In this article we will examine some tips and best practices for using the configuration system for the best results.

The element of a web.config file is a place to store connection strings, server names, file paths, and other miscellaneous settings needed by an application to perform work. The items inside appSettings are items that need to be configurable depending upon the environment, for instance, any database connection strings will change as you move your application from a testing and staging server into production.

As an example, let’s examine this appSetting entry in the web.config file to hold our connection string:

<appsettings>
<add key="ConnectionInfo" value="Data Source=Annapurna;Initial Catalog=ISClass;User Id=isclass;Password=isclass">
<appsettings>

To read the key we use the ConfigurationSettings class from the System.Configuration namespace, as shown below.

12 private void Page_Load(object sender, EventArgs e)

13 {

14 string connectionInfo = ConfigurationSettings.AppSettings["ConnectionInfo"];

15 using(SqlConnection connection = new SqlConnection(connectionInfo))

16 {

17 connection.Open();

18

19 // perform work with connection

20

21 }

22 }

Some applications will duplicate these lines of code in many places. If we think about how the above code will evolve over time, we might see a handful of weaknesses. First, we have a hard coded string in place to fetch connection information from the web.config. Hard coded strings can be easy to mistype and difficult to track down if the key ever changes. Secondly, the code will tie us forever to the appSettings section of the web.config file. Although web.config is designed for just such a setting, we might find in the future that we need to pull configuration settings from a database, or change a setting from being application-wide to being a per-user configuration item kept in the Session or in a cookie.

Encapsulation

Let’s abstract away the source of the connection string using a class with a static property.

1 using System.Configuration;

2

3 namespace aspnet.config

4 {

5 public class Configuration

6 {

7 public static string ConnectionInfo

8 {

9 get

10 {

11 return ConfigurationSettings.AppSettings["ConnectionInfo"];

12 }

13

14 }

15 }

16 }

Now our Page_Load method looks like the following.

11 private void Page_Load(object sender, EventArgs e)

12 {

13 using(SqlConnection connection = new SqlConnection(Configuration.ConnectionInfo))

14 {

15 connection.Open();

16

17 // perform work with connection

18

19 }

20 }

The changes we made to the above code were relatively small - but powerful. Now the Page_Load function doesn’t know where the connection string comes from. We could easily change the ConnectionInfo property to retrieve the string from a different source without modifying any other code in the application. We also no longer have to remember the key string and hard code the string at various points in the application. Instead, we can utilize Visual Studio Intellisense when accessing the Configuration class to find the setting we need. The key value only appears once – inside the ConnectionInfo property – so we could again change the key name in the web.config and have only one line of code to update.

Of course this approach only works if all of the code in the application goes to the Configuration class instead of directly to web.config to retrieve settings. For each appSetting entry added to web.config we would add a corresponding property to the Configuration class.

Another advantage to this approach becomes apparent if we have an application setting that is not a string, but a date, integer, or other primitive type. In this case we could have the corresponding Configuration property parse the string from the web.config file and return the appropriate type.

You’ll notice we provided a read-only property for ConnectionInfo. The web.config file is not designed for constant updates. When an ASP.NET application launches a watch is put on the web.config file. ASP.NET will detect if the web.config changes while the application is running. When ASP.NET detects a change it will spin up a new version of the application with the new settings in effect. Any in process information, such as data kept in Session, Application, and Cache will be lost (assuming session state is InProc and not using a state server or database).