Connecting to a Database

Connecting to a data source is the same whether you are connecting on behalf of a Windows application or a Web application. Where Web applications are concerned , how you use the data will differ . Before we get started I do want to take a minute to show you how you can externalize the connection string. This step will allow you to modify a connection string and even change providers ”if you program with interfaces ”without recompiling your application.

In this section I will demonstrate how to define your database connection string in a Web application's Web.config file. I will also show you how to read those externalized settings, define a generic Database class by using ADO.NET interfaces, and how to write test code.

Defining the Connection String in the Web.config File

The idea of software code convergence is that any specific code occurs only one time in your application. The task of maintaining an application grows in complexity if there are several instances of the same code. This applies to connection string information and connections too. You can provide one externalized connection string that your entire application can use by defining a connection string as a key on the <appSettings> tag of the Web.config file. Listing 16.1 shows you how to define this tag, which makes application settings accessible to all programmers.

Listing 16.1 Externalizing a Connection String

 <?xml version="1.0" encoding="utf-8" ?> <configuration>  <appSettings>   <add key="ConnectionString"   value="Provider=Microsoft.Jet.OLEDB.4.0;Password=;User   ID=Admin;Data Source=C:\Program Files\Microsoft Office\   Office10\Samples\Northwind.mdb;" />   </appSettings>  

Listing 16.1 is an excerpt from my Web.config file for ConnectionDemo.sln . I added the text set in bold. There is more to a Web.config file than appears in this listing; I chose not to reproduce those parts unrelated to the current discussion.

The first statement indicates that the Web.config file is an XML file. The <appSettings> tag can have a combination of <add> , <remove> , and <clear> tags. The <add> tag works like a hard-coded dictionary of key and value pairs. The key attribute refers to the entry, and the value attribute defines the value. Together they form key-value pairs. (Due to the limitations of page size, we have to wrap the value attribute in Listing 16.1. In your .config file the value attribute should be set as all one line.)

This demonstration uses the Northwind sample database. You might store this database in a different physical location than the one I entered in the value attribute. When experimenting with this code, make sure the value attribute you enter reflects the location of the Northwind.mdb database on your PC.

Reading Configuration Settings

I want to take a moment to add a bit of an editorial. Some pundits have said Microsoft has had some difficulty in describing and selling .NET to business. As technologists you and I understand what .NET is: it is the framework. Unfortunately "It's the framework" is not a catchy sales pitch like the one used in Microsoft's "Start Me Up" commercials for Windows 95. Catchy or not, the real benefit is that the framework is coherent , cohesive, consistent, and convergent. It has approachable object-oriented code that takes the tedium out of programming and slogging through the chaotic API, giving developers an opportunity to be hyperproductive. (Now if I could just come up with a great slogan .)

One of the tedious tasks we don't have to do, thanks to .NET, is write our own externalized settings code or read .ini files. Although this isn't rocket science, the cumulative effect of tedious tasks leads to increased time-to-market . The System.Configuration.ConfigurationSettings class contains a shared AppSettings property. AppSettings contains an overloaded indexer that permits you to read configuration settings in the <appSettings> tag using an integer or string index. Thus we can read our connection string (from Listing 16.1) by indexing System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"] .

You can shorten the AppSettings statement by adding a statement for the System.Configuration namespace. It is also important to note that the AppSettings property is a shared instance of NameValueCollection , which is defined in the System.Collections.Specialized namespace and is not case-sensitive. Hence, you could index the connection string using any case, but this is a bad habit to develop.

Defining a Database Class by Using Interfaces

The next thing we can do to promote consistency and convergence is to implement a basic Database class. We can use this class as a convenient , single place to request things like connections and connection strings. This is a better alternative to permitting each developer to repeat the steps for reading connection strings for ConfigurationSettings , creating connection objects, or implementing testing code, like NUnit. Additionally, I like the centralized approach to database access because it provides a convenient place to implement a strategy. For example, we could elect to use ADO.NET interfaces, which would help us write applications that support multiple database vendors . We might also elect to add code access security to this centralized database access point, or, as my colleagues and I did on a project in Oregon, we could begin the project with literal SQL and switch to stored procedures after we got rolling.

NOTE

NUnit is an interface-based testing approach for .NET. The basic concept is that you implement a specific interface for your classes. Then an NUnit tool can invoke operations on your classes through these interfaces, automating testing and test results. Several of these NUnit testing tools are available as free downloads. (Check out http://www.nunit.org for an excellent implementation of NUnit.)

Listing 16.2 demonstrates a Database class stub that centralizes my connection information. I can interact with this Database class very simply, which frees me from the task of retrieving connection strings and creating connection objects (so far). I also used ADO.NET interfaces. I will talk about these more in a minute.

Listing 16.2 A Simple Database Class for Centralizing Data Access

 Public Class Database   Public Shared ReadOnly Property ConnectionString()   Get     Return ConfigurationSettings.AppSettings("ConnectionString")   End Get   End Property   Public Shared Function GetConnection() As IDbConnection     Return New OleDbConnection(ConnectionString)   End Function End Class 

As it exists in Listing 16.2, my Database class contains a public, shared ConnectionString property and a shared GetConnection method. Very simple code, but it is code I will have to write only one time. Notice that I used the IDbConnection interface as my return type rather than an OleDbConnection object. Both OleDb and SqlClient classes implement generic interfaces. The benefit is that I can use an interface-based strategy and support more than one category of provider with the same code. This is helpful if we are switching between a production database and a workstation database, and it is also helpful if I am writing a product like StarTeam. StarTeam is a debug tracking, source management, and intrateam communication extravaganza. When you install it, you can switch between Microsoft Access and Microsoft SQL Server as your back-end database. If you wanted to support a client-electable back-end database ”with a bit more work and SQL scripts for each vendor supported ”you could allow the customer to choose the database provider. You could support this with the same body of code if you wrote your implementation with interfaces. This approach in general is referred to as the abstract factory pattern . (Read Design Patterns: Elements of Reusable Object-Oriented Software by Erich Gamma et al. [1995] for more information on patterns.)

Writing Test Code as You Go

The next thing I like to do is write self-testable code for unit testing. For example, if I want to make sure my connection string is correct and I can connect to the database, I will implement a simple test method. I can use such a test method in a console application, making it easy to test. That is, I don't need a whole application to test just my connection. I added the code in Listing 16.3 to my Database class from Listing 16.2.

Listing 16.3 A Scaffold for Unit Testing a Connection Class

 <Conditional("DEBUG")> _ Public Shared Sub TestConnection()   Dim Connection As IDbConnection = GetConnection()   Try     Connection.Open()     Debug.WriteLine(Connection.State.ToString())   Finally     Connection.Close()   End Try End Sub 

TestConnection declares an IDbConnection object and invokes GetConnection . Because TestConnection is in the Database class we don't need to use the class name when invoking GetConnection . In TestConnection I request an instance of a connection using the interface. The connection is opened in the Try part of a Try . . . Finally block. The connection state is written to the Output window, and finally the connection is closed. If everything is working correctly, I will see the word "Open" in the Output window.

Using the ConditionalAttribute

I prefer to write test code as I go instead of coming back later and trying to figure out my code, but I don't want to drag the test code around when I deploy my application. It is likely that you will have a better understanding of your code when you write it the first time than you are likely to have at a later date. It is also easier to turn test code off and on than it is to write it from scratch after things are going wrong.

Notice that I used the ConditionalAttribute ”<Conditional()> ” on the first line of Listing 16.3. The ConditionalAttribute accepts a string. If the preprocessor token is defined, calls to code that uses the ConditionalAttribute are output by the compiler. If the preprocessor token is not defined, calls to conditional code are not output to the assembly. Figure 16.1 shows the IL code when the DEBUG token is defined, and Figure 16.2 shows the IL code when we configure the same code for release. (The Release configuration undefines the DEBUG token.)

Figure 16.1. The IL code when the DEBUG token is defined, showing the call to Database.TestConnection.

Figure 16.2. The IL code after undefining the DEBUG preprocessor symbol by changing the configuration to Release mode.

Use the ConditionalAttribute anytime you have code that is for testing purposes only. This is a nice improvement over simply wrapping that code in preprocessor #IF conditional statements.