Chapter 11 : Implementing ADO.NET on ASP.NET Pages

This chapter builds on the introduction to ASP.NET given in Chapter 10. Chapter 10 provided general background information on ASP.NET without considering ADO.NET issues. This chapter completes the picture for Microsoft Access developers by adding ADO.NET to the mix. The aim of this chapter is to show you how to apply what you already know about the .NET Framework to creating Web applications for your Access databases. Extending Access beyond the confines of Microsoft Windows applications makes your Access databases available to a wider audience of users on a flexible basis. (For example, you can connect via an intranet, an extranet, or the Internet.)

After a brief overview that highlights special issues associated with data processing on Web pages, this chapter presents a series of four samples. These samples take a progressive approach to implementing Web-based data processing tasks . The first sample demonstrates how to create data access solutions with the Data Form Wizard. This is an important technology for those who want to build a solution without writing code or who need a quick and dirty way of prototyping a solution. The next sample demonstrates how to build data access solutions with graphically created ADO.NET objects. Using graphically created ADO.NET objects reduces ”but does not eliminate ”the coding requirements for a solution. You will have more flexibility in designing page layouts with this approach than with the Data Form Wizard. The third sample illustrates how to use programmatically created ADO.NET objects for data access while illustrating a flexible way of specifying search criteria. The final sample in the chapter presents a Web-based approach to the three classic data manipulation tasks: inserts , updates, and deletes.

Overview of Data Processing with ASP.NET

This section introduces several considerations that can improve your design of Web applications that use data. By this point in the book, you should already have a grasp of the tools that you need for creating data-based solutions on Web pages. In particular, Chapter 10 introduced the elements of ASP.NET application design. Chapters 7 through 9 drilled down on creating data-based solutions with the .NET Framework. Although Chapters 7 through 9 targeted Windows applications, the basic understanding of ADO.NET developed in these chapters applies equally well to Web applications.

At a conceptual level, this section describes special issues that can arise when creating Web applications that use databases. The section concludes with a brief introduction to validation controls that can improve the performance of your Web applications by improving the quality of data that browsers send to a Web server.

Three-Tiered Architecture

Using ASP.NET to manage databases via ADO.NET automatically creates a three-tiered solution, which consists of the following:

• The ASP.NET page in the Web browser

• The Web server

• A database file

The first tier, the ASP.NET page in the Web browser, provides a mechanism for displaying data deposited on a Web page created by the operation of the other two tiers. In addition, the first tier enables users to pass commands along to the Web server through control event procedures that generate a roundtrip of the ASP.NET page between the browser and the Web server.

The second tier, the Web server, processes a page during a roundtrip between itself and a browser. Note that the Web server is not a database or a database manager. Therefore, the Web server does not contain or process data. Typically, the Web server passes commands for data processing to the ADO.NET component of the .NET Framework. For example, the Web server can use ADO.NET connection, command, and data adapter objects to populate a dataset on a Web page. By designing SQL strings with variables based on control property values, you can create dynamic systems.

SQL strings representing data commands operate in the third tier ”the database. When working with Access databases, the third tier will be an Access database file. You should locate your Access database files on the Web server for processing via ASP.NET and ADO.NET. This location guideline for an Access database file also applies to Active Server Pages (ASP) and ActiveX Data Objects (ADO). The samples in this chapter use an OLE DB .NET Data Provider for the explicit purpose of working with Access database files. However, you can also adapt the samples to work with client/server database managers, such as Microsoft SQL Server. When working with client/server databases, the database can reside on a computer other than the Web server. In any event, you use the connection string argument for an ADO.NET Connection object to connect to a database.

ADO.NET and ASP.NET

The fact that a Web server connects to data via ADO.NET means that all the knowledge that you acquired for use with ADO.NET and Windows applications applies to Web applications. (See Chapters 7 through 9 for more on this topic.) You can drag and drop ADO.NET objects from the Toolbox onto a Web page just as you can with a form in a Windows application. In addition, you can use standard Microsoft Visual Basic .NET to program ASP.NET database solutions. Therefore, you have the flexibility to instantiate ADO.NET database objects at run time. Beyond that, you can use wizards such as the Data Form Wizard to expedite the creation of Web solutions that require data access.

Despite all these similarities between Windows applications and Web applications, some important differences exist. For example, the Data Form Wizard automatically senses when it is running in a Web application and adjusts its functionality. In particular, the wizard enables data access only via a DataGrid control. Beyond that, the wizard does not enable any kind of data manipulation. Therefore, if your Web application requires inserts, updates, or deletes, you must programmatically add this functionality.

When creating ADO.NET solutions for Web pages, you must consider the impact of roundtrips and control event procedures. During each roundtrip a Web page makes between a browser and a Web server, the Web server reconstructs the page and all objects on it, including any datasets. As a result, the number of datasets on a Web page and their size impact the responsiveness of the page. In addition, the number of roundtrips that an application engenders affects the overall responsiveness of an application. Minimizing roundtrips makes an application feel faster. For these reasons, you should use small datasets and Web controls that maximize the display of data without requiring roundtrips to view more data.

For example, a DataGrid control shows multiple rows without having to return to the server to show another row. Therefore, you can improve the performance of your Web applications by using a DataGrid control instead of using a collection of text boxes with Button controls that enable scrolling through data. Although you can use a DataGrid control for data manipulation, many developers will find using text boxes a more familiar approach. When using TextBox controls to facilitate data manipulation, consider using unbound controls. Using unbound controls speeds the performance of your application by eliminating the processing of a dataset for each update, insert, or delete task.

Built-In Data Validation Tools

The toolbar for Web applications features a collection of tools that help to validate data on a Web page. You can use these tools with or without programming. In addition, the programming can take place on the Web server or on the client. Five types of built-in data validation controls exist. These controls are built into .NET applications just like a text box. Therefore, the data validation controls can deliver some functionality without requiring a roundtrip to the Web server. A data validation control can block the uploading of a page until a user meets a certain requirement. The validation controls generate error messages that either appear next to a control they validate or in a central area for the whole Web page.

The five types of validation controls for Web pages enable you to specify a required field, a range, or a format for the value in another control on a Web page. If your needs are specialized, you can even build your own custom validation controls. For a validation control, you must specify a value for the ControlToValidate property. This property specifies the control to which a validation control applies. For example, you can use a RequiredFieldValidator control to necessitate the input of an e-mail address before permitting the roundtrip of a Web page between a browser and a Web server. These validation controls enable you to save roundtrips for incorrectly completed Web pages. Table 11-1 summarizes the five types of validation controls.

Table 11-1: Validation Controls for Web Pages

Validation Control Class Name	Description
RequiredFieldValidator	Detects a missing value for a validated control.
CompareValidator	Enables the comparison of a validated property via an operator (for example, a greater-than or less-than operator). This control also permits you to verify the data type of an entry in a validated control.
RangeValidator	Confirms whether a value for a validated control falls in a specified range.
RegularExpressionValidator	Permits you to specify the rejection of values that are not in a specified format, including prebuilt format specifications for items such as telephone numbers as well as your own custom specifications.
CustomValidator	Allows you to develop custom validity specifications not handled by any of the preceding built- in controls.

Validation controls are not an ADO.NET topic because they are controls for a Web page. Therefore, this chapter does not address them further because its aim is to focus on using ADO.NET with Web pages. You can find additional information on validation controls in the Microsoft Visual Studio .NET documentation under the Developing a Validator Control topic. In addition, my prior .NET book, Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .NET (Microsoft Press, 2002), demonstrates graphical and programmatic ways of using validation controls. Because validation controls apply to the other controls on a Web page, the samples in that book apply equally well to Access and SQL Server databases.