Summary of Contents
Introduction 1
Chapter 1: Data Access and .NET 9
Chapter 2: The .NET Data Providers 45
Chapter 3: Visual Studio .NET and ADO.NET 69
Chapter 4: Using DataReaders 133
Chapter 5: The DataSet 163
Chapter 6: Using the DataAdapter 207
Chapter 7: Typed DataSets and DataSet Schemas 235
Chapter 8: XML and the DataSet 271
Chapter 9: Constraints, Relations, and Views 317
Chapter 10: Transactions 365
Chapter 11: Mapping 387
Chapter 12: Making a Data Services Component 409
Chapter 13: ADO.NET and Web Services 455
Chapter 14: SQL Server Native XML Support 517
Chapter 15: Performance and Security 551
Chapter 16: Integration and Migration 589
Chapter 17: Creating a Custom .NET Data Provider 625
Chapter 18: Case Study – Cycle Couriers 671
Index 707
Table of Contents
Introduction 1
What Is ADO.NET? 1
What Does This Book Cover? 1
Who Is This Book For? 3
What You Need To Use This Book 3
Conventions 3
Customer Support 4
How to Download the Sample Code for the Book 4
Errata 4
E-mail Support 4
p2p.wrox.com 5
Why this System Offers the Best Support 6
Chapter 1: Data Access and .NET 9
The .NET Framework 9
The Common Language Runtime 10
Garbage Collection 11
The Common Language Infrastructure 11
Assemblies 11
The Common Type System 12
The Common Language Specification 12
.NET Class Libraries 12
Not Another Data Access Technology? 13
Brief History of Data Access 13
ODBC 13
DAO 13
RDO 14
OLE DB 14
ADO 14
Introduction to ADO.NET 15
Advantages of Using Managed Classes 15
Cross-Language Support 15
Cleaner Architecture 15
XML Support 15
Optimized Object Model 16
Table of Contents
ii
Architectural Overview of ADO.NET 16
.NET Data Providers 16
Data Provider Components 16
Existing Data Providers 19
The DataSet 21
The DataTable Class 23
Updating the Data Source 24
ADO.NET and XML 25
Typed DataSets 27
ADO.NET and ADO 2.6 28
Disconnected Data Access 29
Read-Only, Forward-Only Access 30
Provider-Specific Classes 31
Using ADO 2.x in .NET 34
Using ADO.NET 36
C# Example 36
Visual Basic.NET Example 36
JScript.NET Example 37
Managed C++ Example 38
J# Example 39
ADO.NET Events 40
Summary 42
Chapter 2: The .NET Data Providers 45
The SQL Server Data Provider 46
The OLE DB Data Provider 47
Meet the Players 47
Connection 48
Command 49
DataReader 49
DataAdapter 52
Establishing Connections 53
The SqlConnection and OleDbConnection Classes 53
Constructing a Connection 53
Storing Connection Strings in the Configuration File 54
Connection Events 56
Connection Pooling in the Data Providers 58
Using Commands 59
The SqlCommand and OleDbCommand Classes 59
Using a Command with a T-SQL Statement 59
Executing the Command 60
Using a Command with a Stored Procedure 63
Using the SqlParameter and OleDbParameter Classes 64
Summary 67
Table of Contents
iii
Chapter 3: Visual Studio .NET and ADO.NET 69
Connection Classes 70
SqlConnection and OleDbConnection Data Components 70
Adding the Connection String 72
Retrieving Connection Strings Programmatically 76
Adding an Event 77
Command Data Components 79
SqlCommand and OledbCommand Data Components 80
Defining a Query Command 81
Executing a Stored Procedure 87
Data Adapter Components 91
Table Mappings 101
DataSet Data Component 103
The Typed Dataset 104
Adding a Typed Dataset to the Project 104
Relating Two or More Tables 108
Generating and Filling a Typed DataSet Object 110
Using the Techniques Acquired to Create a Web Service 112
The DataView Data Component 116
Using the DataView to View Selected Rows 117
Filtering Rows Using a Filter Expression 117
Filtering Rows on Row State 118
Using the DataView to Sort Rows 119
The DataGrid Component 120
DataGrid Web Component 120
Binding the DataGrid Component to a Data Source 120
Formatting the DataGrid 122
Sorting DataGrid Records 125
Selecting, Editing, Updating, and Deleting DataGrid Records 125
Breaking a DataGrid into Pages 128
DataGrid Window Component 129
Summary 130
Chapter 4: Using DataReaders 133
A Note About the Chapter Contents 134
The Basics of a DataReader 135
The IDataReader Interface 135
The IDataRecord Interface 135
The ADO.NET DataReaders 136
DataReader Operations 138
Creating a DataReader 139
The ExecuteReader Method 139
Creating and Using a DataReader 140