1. Chapter 1 Connecting to Data

   1. Introduction

   2. Storing Connection Strings

   3. Building a Connection String

   4. Connecting to SQL Server

   5. Connecting to a Named Instance of SQL Server

   6. Connecting to SQL Server Using an IP Address

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

   8. Connecting to an Oracle Database

   9. Connecting to an OLE DB Data Source

   10. Connecting to an ODBC Data Source

   11. Connecting to a Microsoft Access Database

   12. Connecting to a Password-Protected Microsoft Access Database

   13. Connecting to a Microsoft Access Database from ASP.NET

   14. Connecting to a Microsoft Excel Workbook

   15. Connecting to a Text File

   16. Changing the Database for an Open Connection

   17. Setting Connection Pooling Options

   18. Taking Advantage of Connection Pooling

   19. Using Transactions with Pooled Connections

   20. Displaying a Connection Property Dialog Box

   21. Displaying the Data Link Properties Dialog Box

   22. Monitoring Connections

2. Chapter 2 Working with Disconnected Data Objects

   1. Introduction

   2. Creating a DataColumn and Adding It to a DataTable

   3. Creating a DataTable and Adding It to a DataSet

   4. Mapping Table and Column Names Between a Data Source and DataSet

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

   6. Adding a Calculated Column to a DataTable

   7. Creating a Unique Constraint

   8. Creating Single- and Multi-Column Primary Keys

   9. Creating an Autoincrementing Primary Key

   10. Creating a Foreign Key Constraint

   11. Creating a Data Relation

   12. Building a DataSet Programmatically

   13. Adding a Column to a Child DataTable That Displays Data from the Parent Table

   14. Adding a Column to a Parent DataTable That Aggregates a Child Table's Column Values

   15. Converting Between a DataTable and a DataRow Array

   16. Accessing Data Values in a DataRow Array

   17. Creating a Strongly Typed DataSet

   18. Controlling the Names Used in a Strongly Typed DataSet

   19. Replacing Null Values in a Strongly Typed DataSet

3. Chapter 3 Querying and Retrieving Data

   1. Introduction

   2. Executing a Query That Does Not Return a Result Set

   3. Executing a Query That Returns a Single Value

   4. Retrieving a Result Set Stream Using a DataReader

   5. Accessing Data Values in a DataReader

   6. Retrieving a Result Set Using a DataTable or a DataSet

   7. Accessing Data Values in a DataTable or DataSet

   8. Working with Data in a Strongly Typed DataSet

   9. Working with Parent-Child Relations in a Strongly Typed DataSet

   10. Using a DataView with a Strongly Typed DataSet

   11. Testing Whether a Query Returns an Empty Result Set

   12. Counting Records Meeting Criteria

   13. Determining the Number of Records Returned in a DataReader

   14. Executing a Query That Returns Multiple Result Sets

   15. Retrieving Schema and Constraints for a DataSet

   16. Retrieving Hierarchical Data

   17. Navigating Between Parent and Child Tables in an Untyped DataSet

   18. Executing a Parameterized Query

   19. Retrieving Data Using a SQL Server Stored Procedure

   20. Retrieving Multiple Result Sets Using the Oracle Provider

   21. Passing a Null Value to a Query Parameter

   22. Using Table-Valued Types As Parameters

   23. Retrieving a Return Value from a Stored Procedure

   24. Retrieving a Stored Procedure Output Parameter

   25. Raising and Handling Stored Procedure Errors

   26. Executing a SQL Server Scalar-Valued Function

   27. Executing a SQL Server Table-Valued Function

   28. Querying a DataSet Using LINQ

   29. Querying a SQL Server Database Using LINQ

   30. Retrieving Data from a Text File

   31. Retrieving Data from a Microsoft Excel Workbook

   32. Querying Data Asynchronously with Message Queuing

4. Chapter 4 Searching and Analyzing Data

   1. Introduction

   2. Determining the Differences in Data Between Two DataSet Objects

   3. Combining Data from Heterogeneous Data Sources

   4. Filtering Rows in a DataTable or DataView

   5. Finding Rows in a DataTable or DataView

   6. Filtering and Sorting Data in a DataTable

   7. Filtering Null Field Values in a DataTable

   8. Accessing Deleted Rows in a DataTable

   9. Selecting the Top N Rows in a DataTable

   10. Executing Queries That Use COMPUTE BY

   11. Using the Shape Language to Retrieve Hierarchical Data

   12. Retrieving a Random Sample of Records

   13. Using a Common Table Expression (CTE)

   14. Executing a Recursive Query

   15. Retrieving a Ranked Result Set

   16. Retrieving a Pivot and Unpivot Table

   17. Invoking a Function for Each Row in a Result Set

5. Chapter 5 Adding and Modifying Data

   1. Introduction

   2. Using Autoincrementing 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. Modifying Data in a Microsoft Excel Workbook

   7. Modifying Data in a Text File

   8. Retrieving Update Errors

   9. Adding Parent/Child Rows with Autoincrementing Keys

   10. Adding Records with a GUID Primary Key

   11. Inserting Multiple Rows into a Database Table Using T-SQL Row Constructors

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

   13. Updating a Primary Key Value

   14. Getting Stored Procedure Parameter Information at Runtime

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

   16. Updating Data Asynchronously Using Message Queuing

   17. Controlling Edits, Deletions, or Additions to Data with a DataView

   18. Overcoming Keyword Conflicts When Using a CommandBuilder

   19. Capturing Changes to Data in a SQL Server Database

6. Chapter 6 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 DataTable

   5. Converting a DataReader to a DataSet

   6. Converting a DataTable to a DataReader

   7. Converting a DataSet to a DataReader

   8. Serializing Data

   9. Deserializing Data

   10. Merging Data in Two DataSet Objects

   11. Merging Data in Two Database Tables

   12. Encrypting a DataSet

   13. Securing Login Credentials

   14. Exporting the Results of a Query As a String

   15. Exporting the Results of a Query to an Array

   16. Loading an ADO Recordset into a DataSet

   17. Converting a DataSet to an ADO Recordset

7. Chapter 7 Maintaining Database Integrity

   1. Introduction

   2. Using Distributed Transactions

   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 Data in Related Tables

   8. Enforcing Business Rules with Column Expressions

   9. Retrieving Constraints from a SQL Server Database

   10. Checking for Concurrency Violations

   11. Resolving Data Conflicts

   12. Using Transaction Isolation Levels to Protect Data

   13. Specifying Locking Hints in a SQL Server Database

8. Chapter 8 Programmatically Working with Data in .NET Windows and Web Forms User Interfaces

   1. Introduction

   2. Loading Data into and Binding a Field to a Web Forms Control

   3. Binding Data to a Web Forms Control

   4. Binding Data to a Web Forms DetailsView Control

   5. Binding Data to a Web Forms Repeater Control

   6. Binding Data to a Web Forms DataList Control

   7. Binding Data to a Web Forms GridView Control

   8. Modifying and Updating Data in a Web Forms GridView Control

   9. Binding Data to a Web Forms FormView Control

   10. Synchronizing Master-Detail Data in a Web Forms Application

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

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

   13. Loading Data into and Binding a Field to a Windows Forms Control

   14. Binding Data to a Windows Forms Control

   15. Binding Data to a Windows Forms DataGridView Control

   16. Modifying and Updating Data in a Windows Forms DataGridView Control

   17. Using Windows Forms BindingNavigator and BindingSource Controls

   18. Synchronizing Master-Detail Data in a Windows Forms Application

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

   20. Binding a Group of Radio Buttons to a Windows Forms Data Field

   21. Searching a Windows Forms DataGridView Control

9. Chapter 9 Working with XML Data

   1. Introduction

   2. Using an XML File to Save and Load a DataSet or a DataTable

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

   4. Formatting Column Values When Saving Data As XML

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

   6. Synchronizing a DataSet and an XML Document

   7. Storing and Retrieving XML with a Non-XML Data Type Column

   8. Working with a SQL Server XML Data Type Column

   9. Using an XPath Query to Retrieve Data

   10. Reading XML Data Directly from SQL Server

   11. Transforming a DataSet Using XSLT

   12. Filling a DataSet Using an XML Template Query

   13. Using OpenXML to Update Multiple Changes to SQL Server

10. Chapter 10 Optimizing .NET Data Access

    1. Introduction

    2. Executing Multiple Commands on a Single Connection

    3. Executing a SQL Statement Asynchronously

    4. Executing Simultaneous SQL Statements Asynchronously

    5. Creating a DataReader Asynchronously

    6. Filling a DataSet Asynchronously

    7. Canceling an Asynchronous Query

    8. Caching Data

    9. Improving Paging Performance

    10. XML Bulk Loading with SQL Server

    11. Bulk Copying with SQL Server

    12. Improving DataReader Performance with Typed Accessors

    13. Improving DataReader Performance with Column Ordinals

    14. Debugging a SQL Server Stored Procedure

    15. Improving Performance While Filling a DataSet

    16. Reading and Writing Large-Value Data with SQL Server

    17. Reading and Writing a SQL Server User-Defined Type (UDT)

    18. Reading and Writing Oracle Large Data

    19. Performing Batch Updates with a DataAdapter

    20. Automatically Refreshing a DataTable Periodically

    21. Automatically Refreshing a DataTable When Underlying Data Changes

    22. Retrieving SQL Server Runtime Statistics

    23. Writing Provider- and Database-Independent Code

11. Chapter 11 Enumerating and Maintaining Database Objects

    1. Introduction

    2. Enumerating SQL Servers

    3. Retrieving Database Metadata

    4. Retrieving Database Schema Information from SQL Server

    5. Retrieving Column Default Values from SQL Server

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

    7. Retrieving a SQL Server Query Plan

    8. Retrieving SQL Server Column Metadata Without Returning Data

    9. Creating a New SQL Server Database

    10. Adding a Table to a SQL Server Database

    11. Creating a Database Table from a DataTable Schema

    12. Creating DataSet Relationships from SQL Server Relationships

    13. Creating a New Microsoft Access Database

    14. Listing Tables in an Access Database

    15. Enumerating .NET Data Providers

    16. Enumerating OLE DB Providers

    17. Enumerating ODBC Drivers

    18. Changing a SQL Server User Password

12. Chapter 12 SQL Server CLR Integration

    1. Introduction

    2. Creating a Stored Procedure

    3. Creating a Scalar-Valued Function

    4. Creating a Table-Valued Function

    5. Creating an Aggregate Function

    6. Creating a User-Defined Type

    7. Creating a DML Trigger

    8. Creating a DDL Trigger

1. Appendix What's New in ADO.NET Since Version 1.0

   1. ADO.NET 2.0

   2. ADO.NET 3.5

2. Colophon

The animal on the cover of ADO.NET 3.5 Cookbook, Second Edition, 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 three to four 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.