Database Access with Visual Basic .NET (3rd Edition)

If you've been developing database applications with Visual Basic for a while, you have gotten used to the fact that every several years Microsoft comes out with a new and improved data access model. In addition to a new TLA (three-letter acronym), there is a new API/Object model to learn and master. In recent years developers have been through ODBC, DAO, RDO, and ADO, before getting to today's ADO.NET. With the release of each new technology, you need to study its goals and design and then ask the question: Does it make sense for me and my team to move to this new technology? In most cases, the answer has been yes, unless you were working on a project whose current and future requirements were unaffected by the new features and capabilities offered. More often than not, that wasn't the case, although the availability of RDO (Remote Data Objects) was indeed irrelevant to projects that were committed to using the JET (MDB) database engine (DAO is still the better choice for MDB access).

Motivation and Philosophy

So, let's ask the $64,000 question: Why do we need another new data access object model? The simple answer is the one given by an old car commercial in the late 1970s: "You asked for it you got it (Toyota)!" That is, ADO.NET brings together many of the things that developers have been clamoring for since the release of ADO several years ago. Yes, some things, such as disconnected record sets and XML support, were added to ADO over the years. The problem is that they are just that add-ons. They are afterthoughts to the original design and therefore often incomplete and awkward to use.

Classic (COM-based) ADO has a single object, the recordset, that can be used for myriad purposes. Depending on various configuration properties such as cursor type, cursor location, and locking type the recordset will behave differently and fulfill different purposes.

In ADO.NET, the different functionality and purpose are divided into separate objects, which can then be used individually or in conjunction with each other. This capability allows developers to use objects that are optimized for specific purposes and that do not carry any additional distracting "baggage." At the same time, the various objects are designed to work seamlessly with each other, in order to easily provide more advanced functionality.

Support for Distributed Applications and Disconnected Programming Model

ADO.NET provides excellent and flexible support for developing applications that are distributed across multiple computers (database servers, application servers, and client workstations). In particular, super support is provided for disconnected (or three- or n-Tier) applications, where the concurrent load and locking of resources on the database server is minimized. The result is greater scalability the ability to support a greater number of concurrent users by incrementally adding additional hardware. This advantage is particularly crucial when developing Web applications.

Extensive XML Support

Although classic ADO is able to save and read data in XML, the actual format is somewhat unusual and not easy to work with. In addition, as XML support was added to ADO rather late in its evolution, the degree of support is somewhat limited and inflexible. In contrast, for ADO.NET, XML support is an essential design feature put in from the beginning. The ADO.NET philosophy is that "Data is Data" it doesn't matter where data comes from, it can be accessed and processed as relational data or hierarchical data as desired, depending on a particular need or desired tool.

Moreover, XML is used as the transmission format to pass data between tiers and/or computers. That not only eliminates the problem of having to allow COM calls through firewalls, but it further allows data to be shared with applications running on non-Windows platforms (because everybody can process text-based XML).

Integration with .NET Framework

ADO.NET isn't simply the next version of ADO. It was specifically designed and implemented to be part of the .NET Framework. That is, it runs as Managed Code, and all its objects are designed and work as you would expect a .NET object to work. It is also part of the standard .NET Framework package, thereby avoiding the versioning issues that experienced ADO developers have faced in the past.

ADO Look and Feel

Although ADO.NET has been designed and implemented specifically to be part of the .NET Framework, much of it should still seem very familiar to developers experienced in classic ADO. Even for features that are new or implemented differently in ADO.NET, the experienced ADO developer will be able to use to leverage his current knowledge to quickly understand and take advantage of the ADO.NET objects.

ADO.NET Versus Classic ADO (ADO 2.X)

When trying to get a handle on the differences between ADO.NET and classic ADO, bear in mind the following.

• Classic ADO is designed for connected access and is tied to the physical data model. In contrast, ADO.NET is designed for disconnected access and can model data logically.

• In ADO.NET there is a clear distinction and separation between the connected data access model and the disconnected programming model.

• There are no CursorType, CursorLocation, or LockType properties in ADO.NET. It contains only static, client-side cursors and optimistic locking.

• Rather than having a single, multipurpose object, ADO.NET splits the functionality of the classic ADO recordset into smaller, specific objects such as DataReader, DataSet, and DataTable.

• ADO.NET allows manipulation of XML data, rather than just using XML as an I/O format.

• ADO.NET provides for strongly typed DataSets, rather than having all fields being of type Variant. This feature allows greater design-type error detection and greater run-time performance.

ADO.NET Objects Within the .NET Framework

Figure 4.1 shows how the ADO.NET classes fit into the overall .NET Framework. At the bottom is the Common Language Runtime (CLR), which is the run-time infrastructure for all .NET applications (regardless of the language they are written in). Typical functionality provided by the CLR includes a common type system, memory management, and object lifetime management.

The next logical layer, which builds upon the support of the CLR, is the set of system base classes. These are the classes that provide rich functionality to be utilized by .NET applications. Figure 4.1 shows some, but not all, of the classes in the .NET Framework library. In effect, it is the new Windows API (Application Programming Interface). In the past, the way to access the functionality of the Windows operating system was through the Windows API, which consisted of a very large set of inconsistently designed function calls. With .NET, the way to access this functionality (along with new functionality) is by using the properties and methods exposed by the system base classes. This approach is a more object-oriented, consistent, and comfortable way to develop Windows programs, regardless of whether they are desktop, browser, or Web Service applications.

This layer contains several of the namespaces (groups of classes and other definitions) related to data access: System.Data, System.Data.OleDb, and System.Data.SqlClient. In the remainder of this chapter and in Chapters 5, 6 and 7 we take a closer look at many of the classes and definitions in these namespaces.

Application Interfaces

The top level is where a split, or differentiation, exists between different types of applications that developers can build. There are classes and controls for building (classical) forms-based Windows applications (Windows Forms), other classes and controls for building browser-based Web applications (Web Forms), and classes for building Web Services applications. However, all involve the use of a common library of classes for the application logic the system base classes.

Now that you have a sense of where the ADO.NET classes fit into the overall scheme of the .NET Framework, let's take a closer look at the main ADO.NET objects.