A Better Connection String

Chapter 3 - Connecting to a Data Source
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Before we leave this chapter behind, we can take a step that will make things a great deal easier as we proceed. The trouble with specifying connection strings as we've been doing it so far is that if we were ever to change the location of our data sources, we'd have to change every ASPX file as a result. A more durable alternative is to define all our connection strings in a single location, and then to refer to them from our ASPX code. That location is the web.config file, and we can see how the technique works with the help of a quick demonstration.

Try It Out - Connection Strings and Application Settings

For this example, we're going to go back to the first ASPX file we wrote in this chapter, and improve it a little. First, though, we need to add some lines to web.config.

1. Open up your webroot\web.config file, and make the following changes:

    <?xml version="1.0" encoding="utf-8" ?> <configuration>   <system.web>     <customErrors mode="Off" />     <compilation debug="true"/>   </system.web>   <appSettings>     <add key="NWind"       value="server=(local)\NetSDK; database=Northwind; integrated security=true;"/>   </appSettings> </configuration> 

2. Then, having set up this key-value pair, you can refer to it from the ASPX file (copied and named SQLServer_connection_2.aspx) like this:

    <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim objConnection As New SqlConnection(strConnection) 

3. With these changes in place, you should be able to browse to the new file and see exactly the same results we had before. The difference is that you can now add or make changes to the connection strings in the web.config file, giving you a single file to administrate.

How It Works

The key-value pairs that you define in an <appSettings> element of the web.config file are available to all of the ASPX files in webroot, and in any subfolders. In our code, we can use the AppSettings indexed property of the System.Configuration.ConfigurationSettings class to get hold of the values for use in our ASP.NET pages. There is no limit to the number of strings that it's possible to define in this way, and the <appSettings> element clearly has utility beyond the means for which we've used it here. Any time you have potentially volatile data that's required by a number of files on your web server, storing it in web.config like this is a good idea.

We'll be using this technique for specifying connection strings as our preferred option throughout the rest of the book.