This book couldn't have been written without the help of many individuals at O'Reilly, including Robert Denn, Nancy Kotary, John Osborn, and Brian Jepson, who kept the book on track through its revision process. We also owe heartfelt thanks to the technical reviewers, including Shawn Wildermuth, who offered valuable comments throughout.

Without the support of these people and many more at O'Reilly, the book would never have been written.


I would like to thank Molly for her encouragement and support. I love you Mollyyou're the best! I would also like to thank my friends and family who not only put up with me, but cheer me on.


I'd like to thank my parents (all four of them) and my endlessly supportive wife Faria, whom I love dearly. Without them all, this book would never have been written!

CD-ROM Acknowledgments

ADO.NET in a Nutshell for Visual Studio .NET is the work of many individuals. Mike Sierra of O'Reilly converted the ADO.NET namespace API references to Microsoft Help 2.0 format and added the XML tags needed to integrate their content with the Visual Studio .NET Dynamic Help system. He was assisted by Lenny Muellner and Erik Ray. Greg Dickerson and the O'Reilly Tech Support group tested each prerelease build of the software. Kipper York and Shane McRoberts of the Microsoft Help team provided invaluable technical assistance at critical moments, and Erik Promislow of Active State built the install package that makes our Help files an integral part of the Visual Studio .NET developer environment. John Osborn managed the development and release of the product. Frank Gocinski of the Visual Studio .NET third-party integration program was instrumental in making us full VSIP partners . A special tip of the hat as well to Rob Howard who understood our original vision and helped us make the right connections to get this project off the ground

Part I: ADO.NET Tutorial

Chapter 1. Introduction

ADO.NET is a new programming model built upon the .NET Framework, sharing a common type system, design patterns and naming conventions. The stated goals of ADO.NET are to:

  • Provide a disconnected (offline) data architecture in addition to supporting connected operation

  • Integrate tightly with XML

  • Interact with a variety of data sources through a common data representation

  • Optimize data source access

ADO.NET is designed to provide consistent access to data sources. This is accomplished through ADO.NET data providers that provide methods for connecting to data sources as well as retrieving, manipulating, and updating data in both connected and disconnected environments.

1.1 ADO.NET Data Providers

An ADO.NET data provider connects to a data source such as SQL Server, Oracle, or an OLE DB data source, and provides a way to execute commands against that data source in a consistent manner that is independent of the data source and data source-specific functionality. However, aside from a core set of similar capabilities, there is no guarantee that identical functionality will be available in each data provider. This is due to differences between data sources (for example, SQL Server provides many more capabilities than Access) and provider implementations (for example, both Microsoft and Oracle offer ADO.NET providers for Oracle's data server with slight implementation differences).

A complete .NET data provider includes the following classes:

Connects to the data source.

Executes commands against the data source.


A forward-only, read-only connected result set.


Stores all parameters related to a Command and the mappings of both table and column names to the DataSet columns .


Defines parameters for parameterized SQL statements and stored procedures.


Groups statements modifying data into work units that are either committed in their entirety or cancelled.


Bridges the connected components to the disconnected components , allowing a DataSet and DataTable to be filled from the data source and later reconciled with the data source.

The classes for the different providers inherit from a common set of classes and implement a common set of interfaces to provide consistent functionality regardless of the provider. Each data provider uses a unique namespace to logically name and group the classes in the data provider and prevent collisions in the assemblies.

The .NET Framework Version 1.0 ships with SQL Server and OLE DB data providers. The .NET Framework Version 1.1 also ships with both ODBC and Oracle data providers; these providers must be downloaded and installed separately with .NET Framework Version 1.0. Other .NET data providers can be downloaded and installed separately with either version of the .NET Framework. Specific data providers are discussed in more detail in Chapter 2.

Because all .NET data providers present a consistent interface, porting an ADO.NET application from one provider to another is a straightforward task. The examples in this book use the .NET SQL Server data provider except when discussing OLE DB specific functionality (e.g., schema views). Any significant differences between the SQL Server and OLE DB data providers are also discussed throughout the book.