Chapter 07 : ADO.NET: Concepts and Architecture

This chapter formally introduces ADO.NET, the collection of classes that support data manipulation in the .NET Framework. Chapter 8 and Chapter 9 build on the foundation developed in this chapter. The coverage of ADO.NET in this chapter is distinctive for several reasons. First, this chapter focuses almost exclusively on using ADO.NET with Microsoft Access databases. All the sample code uses the Northwind database. Second, the discussion of ADO.NET selects core concepts that are easy to understand and that reinforce one another. The goal of this coverage is to help you understand why ADO.NET exists in the .NET Framework and how it contrasts with earlier data access technologies ” particularly, ActiveX Data Objects (ADO). For example, you ll learn how ADO.NET can substantially improve the scalability of Access databases. Third, this chapter emphasizes conceptual and architectural issues, rather than coding issues. Several pieces of sample code are presented, but their purpose is to reveal interesting design elements about ADO.NET classes. In addition, the sample code demonstrates ways of using ADO.NET class instances to support one another.

This chapter is organized into three sections. The first section starts by discussing platform requirements. Because ADO.NET has different platform requirements than Access, you might want to look this material over as you plan your deployment of ADO.NET solutions for Access databases. This first section dwells on design issues such as the major ADO.NET classes and how they contrast with ADO. The second section systematically examines the core classes for the .NET data provider that you will use with Access databases. This section discusses the important properties and methods for each class and demonstrates how to use a subset of these elements with sample code. The third section dwells on the ADO.NET DataSet object model. DataSet is a new and distinctive class introduced by ADO.NET. Learning to use this object model will enable you to put ADO.NET to work in your solutions.

ADO.NET Platform and Design Issues

ADO.NET is the set of data components for the .NET Framework. In Chapters 3 through 5 you saw that you can also use the ADODB library through the COM Interop feature to perform traditional data access tasks . However, ADO.NET is the preferred route to data in Microsoft Visual Basic .NET applications because it is built into the .NET Framework. The tight integration of ADO.NET means that it offers richer capabilities and faster performance than ADODB . To tap these benefits, you have to learn ADO.NET concepts and architecture. This chapter is where you start.

Overview of ADO.NET Platform Issues

ADO.NET platform requirements include specifications for the operating system, Microsoft Data Access Components (MDAC), and the .NET Framework. To run ADO.NET solutions in Microsoft Windows application projects, your operating system must be one of the following: Windows 98, Windows Me, Windows XP, Windows NT (Service Pack 6A required), Windows 2000 (Service Pack 2 recommended), or Windows .NET Server. This chapter focuses on Windows application projects only; the operating system requirements change for ASP.NET and XML Web services solutions. See Chapter 10 for a review of ASP.NET requirements and Chapter 12 for commentary on the XML Web services requirements.

You need MDAC version 2.6 or later on a workstation running ADO.NET. Visit the Microsoft Universal Data Access site ( http://www.microsoft.com/data ) to download the most recent MDAC version free of charge. Because ADO.NET is built into the .NET Framework, a workstation requires the .NET Framework be installed in order to run an ADO.NET solution in a Windows application project. For most developers, the preferred route is to use Microsoft Visual Studio .NET, which includes the .NET Framework, to meet the ADO.NET installation requirements. However, you can also download the .NET Framework in redistributable or SDK versions from http://msdn.microsoft.com/netframework/downloads/howtoget.asp . Neither free version includes the graphical development tools or the rich, intuitive debugging tools that ship with Visual Studio .NET.

Note  

Because Access 2002 installs MDAC 2.5 by default and Access 2000 installs MDAC 2.1 by default, you need to check workstations with Access installed to make sure their MDAC versions have been upgraded to run an ADO.NET solution.

Like ADO, ADO.NET offers multiple data providers. Two ADO.NET data providers install with the .NET Framework. In addition, Microsoft offers two other ADO.NET data providers, which are available for download from the MSDN Web site. When using an Access database (Jet 4.0), you can use the OLE DB .NET Data Provider, which is one of the two data providers that installs with the .NET Framework. This same data provider is appropriate for Microsoft SQL Server databases earlier than version 7.0 and can also be used with Oracle databases. When you use this data provider, your Visual Basic .NET projects will require a reference that gives access to the System.Data.OleDb namespace. Applications processing databases in SQL Server 7.0 or SQL Server 2000 should use the SQL Server .NET Data Provider, the second provider that installs with the .NET Framework. When you use SQL Server .NET Data Provider elements, your projects will require a reference that gives access to the System.Data.SqlClient namespace. Windows application projects make the appropriate namespace references available by default for the OLE DB .NET and SQL Server .NET data providers.

Note  

Many Access developers regularly use SQL Server databases. For those seeking more in-depth coverage of programming SQL Server solutions, I recommend another of my books: Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .NET (Microsoft Press, 2002). This book presents extensive detail on SQL Server data access issues with T-SQL, Web releases for SQL Server 2000, and special .NET features available for SQL Server databases.

In spite of its name , the OLE DB .NET Data Provider is not generally suitable for working with databases other than Access, Oracle, and versions of SQL Server prior to version 7.0. For example, if you have an application that requires the Microsoft OLE DB Provider for ODBC, MSDASQL, you should not use the OLE DB .NET driver. You should instead download a third driver, the ODBC .NET Data Provider, from the Microsoft downloads site. The ODBC .NET Data Provider is a .NET Framework add-on component. With this component, your applications can work with the native ODBC driver in the same way that the OLE DB .NET Data Provider permits your applications to work with Access, Oracle, and SQL Server databases prior to version 7.0. A workstation requires MDAC 2.7 or later to work with the ODBC .NET Data Provider. You can download the ODBC .NET Data Provider from this URL:

http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml

As with the OleDb and SqlClient namespaces, the ODBC namespace is a simple extension of the System.Data namespace after installation. A useful reference with sample code demonstrating how to use the ODBC .NET Data Provider with Visual Basic .NET is available here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;q310985

After the release of Visual Studio .NET, Microsoft made a fourth data provider available for download. This data provider is for developers to use with Oracle databases. You can find information about the .NET Data Provider for Oracle and download the provider from the following URL:

http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/940/msdncompositedoc.xml

Overview of ADO.NET Design Elements

Because ADO.NET is so critical, you will benefit from reviewing the design features that make ADO.NET particularly valuable for the next generation of computing. ADO.NET maintains backward compatibility while delivering performance enhancements. This combination of benefits permits Access developers to enjoy enhanced performance from their legacy Access databases as they migrate to ADO.NET.

ADO.NET has two major element types: data providers and datasets. Earlier, this section described four Microsoft-supplied .NET data providers and explained when to use each one. The sample code in Chapter 6 and the Jump Start sample in Chapter 2 used the OLE DB .NET Data Provider without discussing it conceptually or architecturally. Together, the data provider and dataset elements provide a wide scope for the applicability of ADO.NET solutions. The Jump Start sample application in Chapter 2 and all the samples in Chapter 6 demonstrate the broad scope of uses that ADO.NET can have in Windows application projects. However, you can also use ADO.NET to access and manipulate data with ASP.NET applications that are tailored for Web solutions (see Chapter 11), or you can use ADO.NET with XML Web services solutions that can access data from another computer over the Internet or an intranet (see Chapter 12). In addition, ADO.NET is suitable in even more exotic environments, such as those used by mobile devices.

You have probably read dozens of articles and possibly a book or two about the .NET Framework and perhaps even ADO.NET. However, you might still be wondering how the ADO.NET design elements interface with relational database objects ” especially tables and queries from Access databases. In addition, you might want to know how ADO.NET design elements relate to XML ” the emerging lingua franca for data exchange.

Figure 7-1 provides a high-level overview of key linkages and highlights some roles of data providers and datasets. .NET data providers can connect, read, and update database objects. The way that data providers accomplish these tasks varies from one provider to another, but all providers expose the same kind of objects to permit the same general range of functions. One component exposed by the .NET data providers is the DataAdapter class. Its role is to serve as a pipe between relational database objects (such as tables in an Access database) and a DataSet object. The DataSet class is a new data component. A DataSet instance can hold one or more tables that are populated by various data adapters. A dataset can represent its data much like a relational database, with relationships among tables or like an XML document. The capability to represent datasets natively as XML documents is important. This is because XML can pass through corporate firewalls, while COM objects such as ADO recordsets cannot pass through firewalls without special settings, which can expose a network to security breaches. See this book s Chapter Appendix for information on some of the important XML features that apply to Access developers creating solutions with Visual Basic .NET.

Figure 7-1: The ADO.NET Data Provider and dataset elements relative to relational database objects and XML documents

The .NET data providers expose functionality through four standard classes: Connection , Command , DataReader , and DataAdapter . The good news is that the ADO.NET Connection class functions similarly to the familiar ADO Connection class. In fact, this class s ADO.NET connection strings are remarkably similar to ADO connection strings. ADO.NET Connection objects link a .NET application to a database such as Northwind.

You use the Command class for executing SQL statements against a database. You can perform data access, data manipulation, and even data definition tasks with Command objects. The data access capability is a standard query capability, except you will typically need to deliver the result set to an object ” a data reader ”that can use a result set. Data manipulation includes insert, update, and delete tasks. Again, you have to specify the operation in SQL, but you can bypass the use of another object. A data definition task refers to the definition of a database object, such as a table or a query, in a database. Using the Command object this way requires an understanding of the Jet SQL data definition language (DDL). Unlike queries, Access has no graphical designer for data definition tasks.

The DataReader class provides read-only, forward-only access to a data source designated by a SQL SELECT statement for a Command object. Therefore, a DataReader class instance functions similarly to a read-only, forward- only cursor. A DataReader object does one thing: it reads data. The class provides no opportunity to scroll backward and forward through the data returned by a Command object. Nor does the DataReader class allow you to modify this data.

The DataAdapter object serves two main purposes. First, it populates a DataSet object from a database. Second, it can convey to a database the result of manipulating a local dataset. You typically associate a different data adapter with each table in a dataset so that a dataset with multiple tables can have multiple data adapters managing the flow of data between itself and a database. DataAdapter instances do not require an open connection to a database.

Both the DataAdapter and the DataReader objects provide access to database information. However, the DataReader object achieves superior performance to DataAdapter instances populating DataSet instances for a couple of reasons. First, no overhead is associated with managing the in-memory data cache for the dataset. Second, a DataReader must have a dedicated, open Connection object to a data source. As mentioned, the DataAdapter object interfaces with a disconnected dataset, meaning you can use the DataAdapter object without a dedicated, open connection to a data source. As a result, a DataAdapter instance has to manage its connection in a way that a DataReader instance does not. However, this same feature supports the scalability improvements of data adapters over data readers. By not requiring an open connection, the DataAdapter object permits a database to service more concurrent users. DataAdapter objects do not require stand-alone Command objects with SQL statements because you can define SQL statements as property settings for a DataAdapter object.

Data manipulation is an especially interesting topic because a data adapter performs this kind of task for a dataset that is disconnected from its database source, except during the actual update. In other words, ADO.NET performs data manipulation via dataset objects to a database with optimistic concurrency . As long as your application enables only one user at a time to update a row, data manipulation will occur without a hitch. However, if two users in a multiuser application can manipulate the same row at the same time, a concurrency violation is possible.

Imagine a scenario in which user 1 makes a change to a column value in a local dataset. If user 2 makes a change to the same column value and updates the database before user 1, when user 1 attempts to update the column value, a concurrency violation can occur. This occurs because the database is different from when user 1 initially invoked a command to populate its dataset from the database. The .NET Framework offers a couple of strategies for handling such a concurrency violation in your applications. You need to be aware of the potential problem, and you should design your database solutions to prohibit such concurrency violations or to handle them when they occur. See the Concurrency Violations section in Chapter 8 for more detailed commentary on concurrency violations along with a sample that demonstrates an approach for handling them if they occur.

Note  

The built-in procedures for detecting concurrency violations is at the level of individual rows. If you require more granular concurrency violation checks ”for example, for individual column values within a row ”the .NET Framework is flexible enough to accommodate your specialized requirements.

Comparison of ADO.NET and ADO Data Access

As you begin creating Access database solutions with Visual Basic .NET, one of your major decisions will be whether to use ADO.NET or ADO. As you know, ADO is available to Visual Basic .NET developers through the COM Interop feature. Although many Access developers are already familiar with the basics of ADO, ADO.NET has important advantages that make it the better choice for any application with a long life expectancy. The lifetime of the application is an important consideration because ADO.NET is at the beginning of its life cycle, while ADO is at the end of its lifetime. ADO.NET already enjoys more innovative design features than ADO, but the ADO.NET advantage naturally will grow over time as ADO.NET adds functionality consistent with the latest advances and standards in data access technology. ADO functionality, on the other hand, will remain fixed. In some ways, Access developers are at a turning point, just as they were when ADO was introduced as an alternative to Data Access Objects (DAO). Most Access developers who switched to ADO enjoyed significant advantages over those who stayed with DAO.

One major shift in ADO.NET is the elimination of the ADO recordset. In some ways, the ADO.NET DataSet object replaces the Recordset object, but important differences between a recordset and a dataset exist. A recordset is meant primarily for connected data access, but a dataset is a disconnected data cache that can be filled with data from a database file by a data adapter. Although you can program ADO recordsets for disconnected access, this is not their normal function and the disconnected mode does not take advantage of the full feature set built into the Recordset object. The dataset is built from the ground up for disconnected data access, and the only way to use the dataset is as a disconnected data store. Disconnected access vastly expands the scalability of a database. Thus, you can service more users of an Access database file with a dataset than you can with a recordset.

As mentioned earlier, ADO.NET also offers the DataReader object for forward-only, read-only access to data. Again, this is a pure play. You cannot use a data reader to enable data manipulation some of the time and as a read-only object at other times. The ADO recordset lets you switch between these and other modes based on your cursor settings.

The ADO Recordset object allows the representation of data from two or more record sources, but the representation always appears as a flat collection of rows and columns . A recordset displays one, two, three, or more tables and queries as a single collection of rows. The ADO.NET DataSet object can represent separate and distinct collections of rows in various tables. In addition, a dataset can designate a relationship between any pair of tables within it. Visual Studio .NET includes graphical tools for creating a typed dataset instead of the default, an untyped dataset . A typed dataset exposes data similarly to a class instance so that it is easier to program. Recordsets do not offer this functionality.

A dataset is natively an XML document, which offers important benefits for passing through firewalls and representing data types relative to a recordset. Because a saved XML document is a text file with tags, to a firewall an XML document looks just like an HTML document that the firewall allows to pass through. On the other hand, firewalls are designed to block the entry of programs that can perform malicious acts to the network. Because a Recordset object is a COM object, firewalls typically block them, even though a recordset contains only data. Furthermore, as a COM object, data in a Recordset must conform to one of the predefined COM data types. This typically requires conversion from native database data types to predefined COM data types. XML documents have a broader array of native data types, and XML allows programmers to create custom data types. Therefore, datasets offer more flexible data types than recordsets. Because a dataset is an XML document, it requires no conversion of its data types. Therefore, transfer between computers is faster with a dataset than with a recordset.