1. Chapter 1 Connecting to Data

   1. Introduction

   2. Connecting to an ODBC Data Source

   3. Connecting to a Microsoft Excel Workbook

   4. Connecting to a Password-Protected Access Database

   5. Connecting to a Secured Access Database

   6. Connecting to an Access Databasefrom ASP.NET

   7. Using an IP Address to Connect to SQL Server

   8. Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)

   9. Connecting to SQL Server Using Integrated Security from ASP.NET

   10. Connecting to an Oracle Database

   11. Connecting to Exchange or Outlook

   12. Writing Database-Independent Code

   13. Storing Connection Strings

   14. Using the Data Link Properties Dialog Box

   15. Monitoring Connections

   16. Taking Advantage of Connection Pooling

   17. Setting Connection Pooling Options

   18. Using Transactions with Pooled Connections

   19. Changing the Database for an Open Connection

   20. Connecting to a Text File

2. Chapter 2 Retrieving and Managing Data

   1. Introduction

   2. Retrieving Hierarchical Data into a DataSet

   3. Building a DataSet Programmatically

   4. Creating a Strongly Typed DataSet

   5. Processing a Batch SQL Statement

   6. Using a Web Service as a Data Source

   7. Accessing Deleted Rows in a DataTable

   8. Counting Records in a DataReader

   9. Mapping .NET Data Provider Data Types to .NET Framework Data Types

   10. Returning an Output Parameter Using a DataReader

   11. Raising and Handling Stored Procedure Errors

   12. Testing for No Records

   13. Retrieving Stored Procedure Return Values Using a DataReader

   14. Executing SQL Server User-Defined Scalar Functions

   15. Passing Null Values to Parameters

   16. Retrieving Update Errors

   17. Mapping Table and Column Names Between the Data Source and DataSet

   18. Displaying Columns from a Related DataTable

   19. Controlling the Names Used in a Strongly Typed DataSet

   20. Replacing Null Values in a Strongly Typed DataSet

   21. Retrieving Data from an Oracle Package

   22. Using Parameterized SQL Statements

   23. Querying Data Asynchronously with Message Queuing

3. Chapter 3 Searching and Analyzing Data

   1. Introduction

   2. Filtering and Sorting Data

   3. Using Expression Columns to Display Calculated Values

   4. Determining the Differences in Data Between Two DataSet Objects

   5. Navigating Between Parent and Child Records Using a DataRelation

   6. Localizing Client-Side Data in a Web Forms Application

   7. Combining Data in Tables from Heterogeneous Data Sources

   8. Using Expression Columns to Display Aggregate Values

   9. Finding Rows in a DataTable

   10. Finding Rows in a DataView

   11. Selecting the Top n Rows in a DataTable

   12. Getting Typed DataRows from DataViews

   13. Filtering for Null Values

   14. Executing Queries That Use COMPUTE BY

   15. Using the Shape Language to Retrieve Hierarchical Data

4. Chapter 4 Adding and Modifying Data

   1. Introduction

   2. Using Auto-Incrementing Columns Without Causing Conflicts

   3. Getting an Identity Column Value from SQL Server

   4. Getting an AutoNumber Value from Microsoft Access

   5. Getting a Sequence Value from Oracle

   6. Adding Parent/Child Rows with Auto-Incrementing Keys

   7. Adding Records with a GUID Primary Key

   8. Updating a Data Source with Data from a Different Data Source

   9. Updating a Primary Key Value

   10. Getting Stored Procedure Parameter Information at Runtime

   11. Updating a DataSet with a Many-to-Many Relationship

   12. Updating Server Data Using a Web Service

   13. Updating Server Data Using .NET Remoting

   14. Updating Data Asynchronously Using Message Queuing

   15. Overcoming Keyword Conflicts When Using CommandBuilders

5. Chapter 5 Copying and Transferring Data

   1. Introduction

   2. Copying Rows from One DataTable to Another

   3. Copying Tables from One DataSet to Another

   4. Converting a DataReader to a DataSet

   5. Serializing Data

   6. Deserializing Data

   7. Merging Data

   8. Transmitting a DataSet Securely

   9. Transferring Login Credentials Securely

   10. Loading an ADO Recordset into a DataSet

   11. Converting a DataSet to an ADO Recordset

   12. Exporting the Results of a Query as a String

   13. Exporting the Results of a Query to an Array

6. Chapter 6 Maintaining Database Integrity

   1. Introduction

   2. Creating a Class That Participates in an Automatic Transaction

   3. Using Manual Transactions

   4. Nesting Manual Transactions with the SQL Server .NET Data Provider

   5. Using ADO.NET and SQL Server DBMS Transactions Together

   6. Using a Transaction with a DataAdapter

   7. Avoiding Referential Integrity Problems When Updating the Data Source

   8. Enforcing Business Rules with Column Expressions

   9. Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columns

   10. Retrieving Constraints from a SQL Server Database

   11. Checking for Concurrency Violations

   12. Resolving Data Conflicts

   13. Using Transaction Isolation Levels to Protect Data

   14. Implementing Pessimistic Concurrency Without Using Database Locks

   15. Specifying Locking Hints in a SQL Server Database

7. Chapter 7 Binding Data to .NET User Interfaces

   1. Introduction

   2. Binding Simple Data to Web Forms Controls

   3. Binding Complex Data to Web Forms Controls

   4. Binding Data to a Web Forms DataList

   5. Binding Data to a Web Forms DataGrid

   6. Editing and Updating Data in a Web Forms DataGrid

   7. Synchronizing Master-Detail Web Forms DataGrids

   8. Displaying an Image from a Database in a Web Forms Control

   9. Displaying an Image from a Database in a Windows Forms Control

   10. Binding a Group of Radio Buttons in a Windows Form

   11. Creating Custom Columns in a Windows Forms DataGrid

   12. Populating a Windows Forms ComboBox

   13. Binding a Windows DataGrid to Master-Detail Data

   14. Loading a Windows PictureBox with Images Stored by Access as OLE Objects

   15. Using a DataView to Control Edits, Deletions, or Additions in Windows Forms

   16. Adding Search Capabilities to Windows Forms

   17. Dynamically Creating Crystal Reports

   18. Using ADO.NET Design-Time Features in Classes Without a GUI

8. Chapter 8 Working with XML

   1. Introduction

   2. Using XSD Schema Files to Load and Save a DataSet Structure

   3. Saving and Loading a DataSet from XML

   4. Synchronizing a DataSet with an XML Document

   5. Storing XML to a Database Field

   6. Reading XML Data Directly from SQL Server

   7. Using XPath to Query Data in a DataSet

   8. Transforming a DataSet Using XSLT

   9. Creating an XML File That Shows Changes Made to a DataSet

   10. Formatting Column Values When Outputting Data as XML

   11. Filling a DataSet Using an XML Template Query

   12. Using a Single Stored Procedure to Update Multiple Changes to a SQL Server Database

9. Chapter 9 Optimizing .NET Data Access

   1. Introduction

   2. Filling a DataSet Asynchronously

   3. Canceling an Asynchronous Query

   4. Caching Data

   5. Improving Paging Performance

   6. Performing a Bulk Insert with SQL Server

   7. Improving DataReader Performance with Typed Accessors

   8. Improving DataReader Performance with Column Ordinals

   9. Debugging a SQL Server Stored Procedure

   10. Improving Performance While Filling a DataSet

   11. Retrieving a Single Value from a Query

   12. Reading and Writing Binary Data with SQL Server

   13. Reading and Writing Binary Data with Oracle

   14. Performing Batch Updates with a DataAdapter

   15. Refreshing a DataSet Automatically Using Extended Properties

10. Chapter 10 Enumerating and Maintaining Database Objects

    1. Introduction

    2. Listing SQL Servers

    3. Retrieving Database Schema Information from SQL Server

    4. Retrieving Column Default Values from SQL Server

    5. Determining the Length of Columns in a SQL Server Table

    6. Counting Records

    7. Creating a New Access Database

    8. Creating a New SQL Server Database

    9. Adding Tables to a Database

    10. Getting a SQL Server Query Plan

    11. Compacting an Access Database

    12. Creating DataSet Relationships from SQL Server Relationships

    13. Getting SQL Server Column Metadata Without Returning Data

    14. Listing Installed OLE DB Providers

    15. Listing Tables in an Access Database

    16. Creating a Table in the Database from a DataTable Schema

    17. Listing Installed ODBC Drivers

1. Appendix A Converting from C# to VB Syntax

   1. General Considerations

   2. Classes

   3. Structures

   4. Interfaces

   5. Class, Structure, and Interface Members

   6. Delegates

   7. Enumerations

2. Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of ADO.NET Cookbook is a white spoonbill (Platelea leucorodia), also called the common or Eurasian white spoonbill, named for its large, spatulate bill. Spoonbills feed by wading through the shallow waters of their marshy habitats, moving their partly opened bills from side to side to filter out mud and water. When the sensitive nerve endings inside their bills detect an edible morsel, they snap them shut. A spoonbill's typical diet includes insects, larvae, small crustaceans, and tiny fish.

Mature white spoonbills are about 85 centimeters long from the tips of their tails to the tips of their bills, and their wingspans average 125 centimeters. As the name suggests, white spoonbill feathers are a creamy white. During breeding season, however, adults develop yellow patches on their breasts, faces, and bills.

White spoonbills are found in northeast Africa and much of Europe and Asia. They nest in trees and reed beds, typically in large colonies and sometimes with other bird species in the Threskiornithidae family, such as herons and storks. Males gather nesting materials, and females weave these sticks and reeds into shallow, bowl-shaped nests. Females generally lay a clutch of 3 to 4 eggs per year and share incubation duties with their mates.

Although the white spoonbill is an endangered species, conservation efforts have led to a slow increase in population in some areas, particularly in northwestern Europe. Loss of breeding sites due to land clearance and pesticide use are the main threats to the white spoonbill's survival. Matt Hutchinson was the production editor for ADO.NET Cookbook. Argosy Publishing, Inc. provided production services. Mary Brady, Sarah Sherman, and Claire Cloutier provided quality control.

Emma Colby designed the cover of this book, based on a series design by Edie Freedman. The cover image is from Bewick's British Birds. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.

David Futato designed the interior layout. This book was converted by Julie Hawks to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Genevieve d'Entremont.