So far in this book, we’ve used an ArrayList as the data source for data-bound controls. In most real-world applications, of course, the data source will be a database. ADO.NET provides a rich set of objects to manage database interaction.
ADO.NET looks, at first glance, very similar to ADO, its predecessor technology. The key difference, however, is that ADO.NET is modeled around a disconnected data architecture. Database connections are “expensive” (that is, they use a lot of resources), and it is difficult to have thousands (or tens of thousands) of simultaneous continuous connections. A disconnected architecture, on the other hand, is resource-frugal. Connections are used only briefly. Of course, ADO.NET does connect to the database to retrieve data, and connects again to update data when you’ve made changes. When not updating data to or from the database, the connection is broken. Most web applications spend most of their time simply reading through data and displaying it, and ADO.NET provides a disconnected subset of the data for your use while reading and displaying.
As you might imagine, disconnected datasets can have scale and performance problems of their own. There is overhead in creating and tearing down connections, and if you drop the connection each time you fill the database, and then must reestablish it each time you update the data, you will find that performance begins to degrade quickly. This problem is mitigated by the use of connection pooling. While it looks to your application like you are creating and destroying connections, you are actually borrowing and returning connections from a pool that ADO.NET manages on your behalf.
Imagine that you have been asked to create a tool to manage bugs for a large development effort. You will be supporting three developers who will work in C# and VB.NET, along with a user interface designer and a few quality control engineers. You would like your design to be reasonably flexible so that you can reapply your bug tracking application to future projects.
Your first decision is that you will create a web application. This has the great advantage that all the participants will be able to access the application from their home computers. Since the developers work off-site, this is almost a necessity. You will, of course, develop your web application in ASP.NET.
You imagine that there will be a web page for entering bugs, as well as a page for reviewing and editing bugs. To support this, you will need to design a relational database, and for a number of reasons beyond the scope of this book, you decide to create that database using SQL Server.
You begin by thinking about the kinds of information you want to capture in the database, and how that information will be used. You will want to allow any user of the system to create a bug report. You’ll also want certain users (e.g., developers and QA) to update the bug reports. Developers will want to be able to record progress in fixing a bug, or to mark a bug fixed. QA will want to check the fix and either close the bug or reopen it for further investigation. The original reporter of the bug will want to find out who is working on the bug, and track progress.
One requirement imposed early in the design process is that the bug database ought to provide an “audit trail.” If the bug is modified you’ll want to be able to say who modified it and when they did so. In fact, you’ll want to be able to track all the changes to the bug, so that you can generate a report like the excerpt shown in Example 11-1.
Example 11-1. Excerpt from a bug report
Bug 101 - System crashes on login 101.1 - Reporter: Osborn Date: 1/1/2002 Original bug filed Description: When I login I crash. Status: Open Owner: QA 101.2 - Modified by: Smith Date: 1/2/2002 Changed Status, Owner Action: Confirmed bug. Status: Assigned Owner: Hurwitz 101.3 - Modified by Hurwitz Date 1/2/2002 Changed Status Action: I'll look into this but I don't think it is my code. Status: Accepted Owner: Hurwitz 101.4 - Modified by Hurwitz Date 1/3/2002 Changed Status, Owner Action: Fault lies in login code. Reassigned to Liberty Status: Assigned Owner: Liberty 101.5 - Modified by Liberty Date: 1/3/2002 Changed Status Action: Yup, this is mine. Status: Accepted Owner: Liberty 101.6 - Modified by Liberty Date 1/4/2002 Changed Status, Owner Action: Added test for null loginID in DoLogin( ) Status: Fixed Owner: QA 101.7 - Modified by Smith Date: 1/4/2002 Changed Status Action: Tested and confirmed Status: Closed Owner: QA
To track this information you’ll need to know the date and time of each modification, as well as who made the modification and what they did. There will probably be other information you’ll want to capture as well, though this may become more obvious as you build the application (and as you use it!).
One way to meet these requirements is to create two tables to
represent each Bug. Each record in the Bugs table will represent a single bug,
but you’ll need an additional table to keep track of
the revisions. Call this second table
BugHistory
.
A Bug record will have a BugID and will include the information that is constant for the bug throughout its history. A BugHistory record will have the information specific to each revision.
The bug database design described in this chapter includes three
significant tables: Bugs, BugHistory, and People.
Bugs and BugHistory work
together to track the progress of a bug. For any given bug, a single
record is created in the Bugs table, and a record
is created in BugHistory each time the bug is
revised in any way. The People table tracks the
developers, QA, and other personnel who might be referred to in a Bug
report.
Tip
This is a simplified design that meets the detailed specifications but which focuses on the key technologies; a robust professional design would necessarily be more complex. The complete database design as used in this book is shown in Appendix B. A crash course on relational database design is provided in Appendix A.
Figure 11-1 shows a snapshot of the Bugs table, while Figure 11-2 shows a snapshot of the BugHistory table.
When a bug is first entered, a record is created in each of the Bugs and BugHistory tables. Each time the bug is updated a record is added to BugHistory. During the evolution of a bug, the status, severity, and owner of a bug may change, but the initial description and reporter will not. Those items that are consistent for the entire bug are in the Bugs table; those that are updated as the bug is corrected are in the BugHistory table.
The reporter, for example, is the ID of the person who reported the bug. This is unchanged for the life of the bug and so is recorded in the Bugs table. The owner may be adjusted from time to time, and so is recorded in the BugHistory table. In both cases, however, what is actually recorded is just a PersonID, which acts as a foreign key into the People table. An excerpt from the People table is shown in Figure 11-3.
In addition to these three primary tables, there are a number of
secondary tables which serve as look-up tables. For example,
lkStatus, serves as a look-up table for the
possible values of the status column in
BugHistory.
The format for all of the look-up tables (lkStatus, lkProduct, lkRoles and lkSeverity) is the same: the ID followed by a text field. Each table will hold one row for each possible value. As an example, Figure 11-4 shows the various look-up tables.
Figure 11-5 illustrates the tables in their various relationships graphically.
