ADO NET Cookbook



•	Table of Contents
•	Index
•	Reviews
•	Examples
•	Reader Reviews
•	Errata

ADO.NET Cookbook
By Bill Hamilton

Publisher	: O'Reilly
Pub Date	: September 2003
ISBN	: 0-596-00439-7
Pages	: 624

Preface

Who This Book Is For

What You Need to Use This Book

How This Book Is Organized

What Was Left Out

Conventions Used in This Book

About the Code

Configuration Files

Platform Notes

Comments and Questions

Acknowledgments

Chapter 1. Connecting to Data

Introduction

Recipe 1.1. Connecting to an ODBC Data Source

Recipe 1.2. Connecting to a Microsoft Excel Workbook

Recipe 1.3. Connecting to a Password-Protected Access Database

Recipe 1.4. Connecting to a Secured Access Database

Recipe 1.5. Connecting to an Access Databasefrom ASP.NET

Recipe 1.6. Using an IP Address to Connect to SQL Server

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

Recipe 1.8. Connecting to SQL Server Using Integrated Security from ASP.NET

Recipe 1.9. Connecting to an Oracle Database

Recipe 1.10. Connecting to Exchange or Outlook

Recipe 1.11. Writing Database-Independent Code

Recipe 1.12. Storing Connection Strings

Recipe 1.13. Using the Data Link Properties Dialog Box

Recipe 1.14. Monitoring Connections

Recipe 1.15. Taking Advantage of Connection Pooling

Recipe 1.16. Setting Connection Pooling Options

Recipe 1.17. Using Transactions with Pooled Connections

Recipe 1.18. Changing the Database for an Open Connection

Recipe 1.19. Connecting to a Text File

Chapter 2. Retrieving and Managing Data

Introduction

Recipe 2.1. Retrieving Hierarchical Data into a DataSet

Recipe 2.2. Building a DataSet Programmatically

Recipe 2.3. Creating a Strongly Typed DataSet

Recipe 2.4. Processing a Batch SQL Statement

Recipe 2.5. Using a Web Service as a Data Source

Recipe 2.6. Accessing Deleted Rows in a DataTable

Recipe 2.7. Counting Records in a DataReader

Recipe 2.8. Mapping .NET Data Provider Data Types to .NET Framework Data Types

Recipe 2.9. Returning an Output Parameter Using a DataReader

Recipe 2.10. Raising and Handling Stored Procedure Errors

Recipe 2.11. Testing for No Records

Recipe 2.12. Retrieving Stored Procedure Return Values Using a DataReader

Recipe 2.13. Executing SQL Server User-Defined Scalar Functions

Recipe 2.14. Passing Null Values to Parameters

Recipe 2.15. Retrieving Update Errors

Recipe 2.16. Mapping Table and Column Names Between the Data Source and DataSet

Recipe 2.17. Displaying Columns from a Related DataTable

Recipe 2.18. Controlling the Names Used in a Strongly Typed DataSet

Recipe 2.19. Replacing Null Values in a Strongly Typed DataSet

Recipe 2.20. Retrieving Data from an Oracle Package

Recipe 2.21. Using Parameterized SQL Statements

Recipe 2.22. Querying Data Asynchronously with Message Queuing

Chapter 3. Searching and Analyzing Data

Introduction

Recipe 3.1. Filtering and Sorting Data

Recipe 3.2. Using Expression Columns to Display Calculated Values

Recipe 3.3. Determining the Differences in Data Between Two DataSet Objects

Recipe 3.4. Navigating Between Parent and Child Records Using a DataRelation

Recipe 3.5. Localizing Client-Side Data in a Web Forms Application

Recipe 3.6. Combining Data in Tables from Heterogeneous Data Sources

Recipe 3.7. Using Expression Columns to Display Aggregate Values

Recipe 3.8. Finding Rows in a DataTable

Recipe 3.9. Finding Rows in a DataView

Recipe 3.10. Selecting the Top n Rows in a DataTable

Recipe 3.11. Getting Typed DataRows from DataViews

Recipe 3.12. Filtering for Null Values

Recipe 3.13. Executing Queries That Use COMPUTE BY

Recipe 3.14. Using the Shape Language to Retrieve Hierarchical Data

Chapter 4. Adding and Modifying Data

Introduction

Recipe 4.1. Using Auto-Incrementing Columns Without Causing Conflicts

Recipe 4.2. Getting an Identity Column Value from SQL Server

Recipe 4.3. Getting an AutoNumber Value from Microsoft Access

Recipe 4.4. Getting a Sequence Value from Oracle

Recipe 4.5. Adding Parent/Child Rows with Auto-Incrementing Keys

Recipe 4.6. Adding Records with a GUID Primary Key

Recipe 4.7. Updating a Data Source with Data from a Different Data Source

Recipe 4.8. Updating a Primary Key Value

Recipe 4.9. Getting Stored Procedure Parameter Information at Runtime

Recipe 4.10. Updating a DataSet with a Many-to-Many Relationship

Recipe 4.11. Updating Server Data Using a Web Service

Recipe 4.12. Updating Server Data Using .NET Remoting

Recipe 4.13. Updating Data Asynchronously Using Message Queuing

Recipe 4.14. Overcoming Keyword Conflicts When Using CommandBuilders

Chapter 5. Copying and Transferring Data

Introduction

Recipe 5.1. Copying Rows from One DataTable to Another

Recipe 5.2. Copying Tables from One DataSet to Another

Recipe 5.3. Converting a DataReader to a DataSet

Recipe 5.4. Serializing Data

Recipe 5.5. Deserializing Data

Recipe 5.6. Merging Data

Recipe 5.7. Transmitting a DataSet Securely

Recipe 5.8. Transferring Login Credentials Securely

Recipe 5.9. Loading an ADO Recordset into a DataSet

Recipe 5.10. Converting a DataSet to an ADO Recordset

Recipe 5.11. Exporting the Results of a Query as a String

Recipe 5.12. Exporting the Results of a Query to an Array

Chapter 6. Maintaining Database Integrity

Introduction

Recipe 6.1. Creating a Class That Participates in an Automatic Transaction

Recipe 6.2. Using Manual Transactions

Recipe 6.3. Nesting Manual Transactions with the SQL Server .NET Data Provider

Recipe 6.4. Using ADO.NET and SQL Server DBMS Transactions Together

Recipe 6.5. Using a Transaction with a DataAdapter

Recipe 6.6. Avoiding Referential Integrity Problems When Updating the Data Source

Recipe 6.7. Enforcing Business Rules with Column Expressions

Recipe 6.8. Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columns

Recipe 6.9. Retrieving Constraints from a SQL Server Database

Recipe 6.10. Checking for Concurrency Violations

Recipe 6.11. Resolving Data Conflicts

Recipe 6.12. Using Transaction Isolation Levels to Protect Data

Recipe 6.13. Implementing Pessimistic Concurrency Without Using Database Locks

Recipe 6.14. Specifying Locking Hints in a SQL Server Database

Chapter 7. Binding Data to .NET User Interfaces

Introduction

Recipe 7.1. Binding Simple Data to Web Forms Controls

Recipe 7.2. Binding Complex Data to Web Forms Controls

Recipe 7.3. Binding Data to a Web Forms DataList

Recipe 7.4. Binding Data to a Web Forms DataGrid

Recipe 7.5. Editing and Updating Data in a Web Forms DataGrid

Recipe 7.6. Synchronizing Master-Detail Web Forms DataGrids

Recipe 7.7. Displaying an Image from a Database in a Web Forms Control

Recipe 7.8. Displaying an Image from a Database in a Windows Forms Control

Recipe 7.9. Binding a Group of Radio Buttons in a Windows Form

Recipe 7.10. Creating Custom Columns in a Windows Forms DataGrid

Recipe 7.11. Populating a Windows Forms ComboBox

Recipe 7.12. Binding a Windows DataGrid to Master-Detail Data

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

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

Recipe 7.15. Adding Search Capabilities to Windows Forms

Recipe 7.16. Dynamically Creating Crystal Reports

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

Chapter 8. Working with XML

Introduction

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

Recipe 8.2. Saving and Loading a DataSet from XML

Recipe 8.3. Synchronizing a DataSet with an XML Document

Recipe 8.. Storing XML to a Database Field

Recipe 8.5. Reading XML Data Directly from SQL Server

Recipe 8.6. Using XPath to Query Data in a DataSet

Recipe 8.7. Transforming a DataSet Using XSLT

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

Recipe 8.9. Formatting Column Values When Outputting Data as XML

Recipe 8.10. Filling a DataSet Using an XML Template Query

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

Chapter 9. Optimizing .NET Data Access

Introduction

Recipe 9.1. Filling a DataSet Asynchronously

Recipe 9.2. Canceling an Asynchronous Query

Recipe 9.3. Caching Data

Recipe 9.4. Improving Paging Performance

Recipe 9.5. Performing a Bulk Insert with SQL Server

Recipe 9.6. Improving DataReader Performance with Typed Accessors

Recipe 9.7. Improving DataReader Performance with Column Ordinals

Recipe 9.8. Debugging a SQL Server Stored Procedure

Recipe 9.9. Improving Performance While Filling a DataSet

Recipe 9.10. Retrieving a Single Value from a Query

Recipe 9.11. Reading and Writing Binary Data with SQL Server

Recipe 9.12. Reading and Writing Binary Data with Oracle

Recipe 9.13. Performing Batch Updates with a DataAdapter

Recipe 9.14. Refreshing a DataSet Automatically Using Extended Properties

Chapter 10. Enumerating and Maintaining Database Objects

Introduction

Recipe 10.1. Listing SQL Servers

Recipe 10.2. Retrieving Database Schema Information from SQL Server

Recipe 10.3. Retrieving Column Default Values from SQL Server

Recipe 10.4. Determining the Length of Columns in a SQL Server Table

Recipe 10.5. Counting Records

Recipe 10.6. Creating a New Access Database

Recipe 10.7. Creating a New SQL Server Database

Recipe 10.8. Adding Tables to a Database

Recipe 10.9. Getting a SQL Server Query Plan

Recipe 10.10. Compacting an Access Database

Recipe 10.11. Creating DataSet Relationships from SQL Server Relationships

Recipe 10.12. Getting SQL Server Column Metadata Without Returning Data

Recipe 10.13. Listing Installed OLE DB Providers

Recipe 10.14. Listing Tables in an Access Database

Recipe 10.15. Creating a Table in the Database from a DataTable Schema

Recipe 10.16. Listing Installed ODBC Drivers

Appendix A. Converting from C# to VB Syntax

Section A.1. General Considerations

Section A.2. Classes

Section A.3. Structures

Section A.4. Interfaces

Section A.5. Class, Structure, and Interface Members

Section A.6. Delegates

Section A.7. Enumerations

Colophon

Index

[ Team LiB ]