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

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

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
Table of Contents
iv
Simple Data Retrieval With the DataReader 141
Executing Stored Procedures with a DataReader 143

DataRelations Example 192
Merging DataSets 197
Merging Two DataSets 198
Merging Two DataSets and Maintaining Original Values 198
Merging Two DataSets with Different Schemas 198
Caching DataSets for Better Performance 199
Summary 204
Table of Contents

v
Chapter 6: Using the DataAdapter 207
The DataAdapter Base Class 207
DataAdapter and DataSet 208
More Details for the Fill Method 212
Using More Complex Queries 213
Filling a DataSet Object with Few Records 217
Filling a DataSet Object with Only the Schema 219
Filling a DataSet Object that Already has a Schema 222
Updating a Database 222
Using a CommandBuilder Object 223
Using SQL Commands 225
Making Updates Using Stored Procedures 228
The DataAdapter's Events 230
Summary 232
Chapter 7: Typed DataSets and DataSet Schemas 235
Overview of XSD 236
Simple Types 236
Basic Data Types 236
Attributes 238
Enumerations 239

typedName 263
typedPlural 263
typedParent 263
typedChildren 263
nullValue 263
msdata 264
ConstraintName 264
ConstraintOnly 264
UpdateRule 264
DeleteRule 264
PrimaryKey 264
Relationship 264
Annotated Typed DataSet Example 265
Summary 268
Chapter 8: XML and the DataSet 271
XmlDocument (W3C DOM) 272
XPath 275
Axis 276
Node Test 277
Predicate 277
DataSet Schemas 280
Schema Inference 280
Inference Rules 280
Inference Rules in Action 281
Supplied Schemas 285
Document Validation with Schemas 286
XmlValidatingReader 286
DataSets and XML Data 289
Loading XML 289
XmlReadMode 290

Filtering on Row State 346
Editing Data in the DataView 348
DataViewManager 349
Databinding 350
Bringing it Together 352
Examples 354
Example 1 354
Example 2 357
Summary 362
Chapter 10: Transactions 365
What is a Transaction? 365
ACID Properties 366
Database Transactions 366
Transaction Vocabulary 367
ADO.NET Transaction Support 367
Transaction Class 369
Methods of the Transaction class 369
Writing Transactional Database Applications 370
Implementing Transactions 370
Running the Application 373
Examining the Effect of Isolation Level 373
What are Isolation Levels? 374
Some Related Terms 374
Possible Isolation Levels in ADO.NET 374
Changing Isolation Levels 375
When to Use Transactions 377
Transactions and Performance 378
Default Behavior for Transactions 378
Transactions and User Confirmation 378
Simultaneous ADO.NET and DBMS Transactions 379

The Commands Class – Private Connection Method 422
Creating an Assembly Information File 423
Compiling the Data Service Component 424
Deploying a Data Service Component 425
The Global Assembly Cache – (GAC) 425
Making a Reference to Wrox_DL in machine.config 428
Using the Data Service Component 428
Using in a ASP.NET Web Form 429
Executing SQL Text 429
Executing Stored Procedures 435
Using in a Web Service 438
Table of Contents

ix
Performance and Optimization Tips 440
Object Pooling 440
Building a Hit Tracker Component 441
Transactions 448
Uninstalling the Components 452
Summary 453
Chapter 13: ADO.NET and Web Services 455
Setting Up the Code Samples 456
Web Services – The New DCOM 458
Common Standards 458
Supply and Demand – Web Service Providers and Consumers 459
Building a Basic Web Service 460
Building a Basic Consumer 465
Building an HTTP Consumer 466
Capturing the Data in an XmlDocument 469
Build a SOAP Consumer in Visual Studio .NET 471

FOR XML RAW 522
Using FOR XML RAW with ADO.NET 523
FOR XML AUTO 525
FOR XML AUTO and ADO.NET 527
FOR XML EXPLICIT 529
FOR XML EXPLICIT – Two-Level Example 531
Entity Encoding 532
Directives 533
FOR XML EXPLICIT – Three-Level Example 536
FOR XML EXPLICIT – ADO.NET 539
FOR XML EXPLICIT – Conclusion 541
OPENXML 541
OPENXML Stored Procedures: Deletion and Updates 544
OPENXML ADO.NET: Insertion, Deletion, and Updates 545
Summary 547
Chapter 15: Performance and Security 551
Optimizing Data Access 551
DataReader or DataSet? 552
Memory Consumption 552
Traversal Direction 552
Multiple Result Sets 553
Round Trips 553
Stored Procedures 554
Compiled Query Caching 555
Configuring DataAdapter Commands 555
High-Volume Data Processing 559
Latency 559
Cached Data 560
ASP.NET Object Caching 564
Birds of a Feather (Functionality Grouping) 567

Migration 595
ADO Data Types 596
Migrating Connections 597
Migrating the Recordset 599
Forward-Only Data Access 600
Publishing RecordSet Changes 603
Migrating Commands and Stored Procedures 609
Changes in XML Persistence 615
Handling Exceptions and Errors 618
Streams 620
Summary 622
Chapter 17: Creating a Custom .NET Data Provider 625
Data Provider Library 626
Application Requirements 626
Retail Store 626
E-Commerce Site 627
Telephone Sales 627
Architecture and Design 627
Distributed Order Entry System 628
The Order Class and Schema 629
A Sample Order 629
Implementing the Data Provider Assembly 630
The OQProvider Namespace 630
The OrderObject 631
The OrderItem 634
Table of Contents
xii
An MSMQ Review 636
Sending Messages 636
Receiving Messages 638

Database Detail 683
Customers 683
Cyclists Table 684
CyclistStates Table 684
Packages Table 684
Relationships 686
Class Description 686
ServiceCustomer 687
ServicePackage 689
ServiceCyclist 692
Web Interface classes 693
Call Center Operator Application 696
Hardware Configuration 699
Table of Contents

xiii
How to Deploy the System 700
Installing the Web Application and Web Service 700
Installing the Client – Call Center Application. 702
How to Build the System 703
Summary 704
Index 707
Table of Contents
xiv Introduction
What is ADO.NET?
ADO.NET is a large set of .NET classes that enable us to retrieve and manipulate data, and update data
sources, in very many different ways. As an integral part of the .NET framework, it shares many of its features:


Chapter 9 continues the look at the DataSet by examining constraints, relations and views, all of which
influence the way that data is presented and manipulated. The chapter introduces the DataView and includes
some examples.

Chapter 10 moves on to look at the topic of transactions, an important item in the business world where either
all the operations must succeed, or all of them must fail. The chapter examines, amongst other things, isolation
levels and their impact, performance, and advanced techniques.

The concept of mapping is explored in Chapter 11: this is where we can give our own names to unintuitive
column headings in order to understand the material better.

Chapter 12 looks at creating our own Data Services component: the benefits, the creation and deployment,
and using it once it exists. The chapter also looks at tips for better performance of data service components.
This leads well into Chapter 13, where we look at ADO.NET and Web Services, in particular exchanging data,
using XML, and security.

Chapter 14 looks again at the issue of XML, this time showing how SQL Server 2000 has native support for
this cross-platform standard of data retrieval. The chapter is example-based, showing all the native XML
options at every step.

Chapter 15 moves off into the more theoretical realm of performance and security. Both are important
considerations if we will be dealing with thousands of data access demands every minute. The chapter covers
many ways to increase performance and tighten security.

Chapter 16 discusses integration and migration, particularly accessing ADO from .NET and how to handle the
migration from ADO to ADO.NET.

Chapter 17 allows us to create our own custom .NET data provider. It goes through the whole process: why
we need our own provider, the architecture and design, and the actual implementation. The chapter also shows

<Invoice>
<part>
<name>Widget</name>
<price>$10.00</price>
</part>
</invoice>

In cases like this, the code with a white background is code we are already familiar with; the line highlighted
in gray is a new addition to the code since we last looked at it.

Advice, hints, and background information comes in this type of font.
Important pieces of information come in boxes like this.
Introduction
4
Bullets appear indented, with each new bullet marked as follows:

q Important Words are in a bold type font
q Words that appear on the screen, or in menus like the File or Window menu, are in a similar font to
the one you would see on a Windows desktop
q Keys that you press on the keyboard, like Ctrl and Enter, are in italics
Customer Support
We always value hearing from our readers, and we want to know what you think about this book: what you
liked, what you didn't like, and what you think we can do better next time. You can send us your comments,
either by returning the reply card in the back of the book, or by e-mail to Please be
sure to mention the book title in your message.
How to Download the Sample Code for the Book
When you visit the Wrox site, simply locate the title through our Search facility or by
using one of the title lists. Click on Download in the Code column, or on Download Code on the book's detail
page.


people to read it. They have files on most frequently asked questions and will answer anything
general about the book or the web site immediately.
q Editorial – Deeper queries are forwarded to the technical editor responsible for that book. They have
experience with the programming language or particular product, and are able to answer detailed
technical questions on the subject.
q The Authors – Finally, in the unlikely event that the editor cannot answer your problem, he or will
forward the request to the author. We do try to protect the author from any distractions to their
writing; however, we are quite happy to forward specific requests to them. All Wrox authors help
with the support on their books. They will e-mail the customer and the editor with their response,
and again all readers should benefit.

The Wrox Support process can only offer support to issues that are directly pertinent to the content of our
published title. Support for questions that fall outside the scope of normal book support is provided via the
community lists of our forum.
p2p.wrox.com
For author and peer discussion join the P2P mailing lists. Our unique system provides programmer to
programmer™ contact on mailing lists, forums, and newsgroups, all in addition to our one-to-one e-mail
support system. If you post a query to P2P, you can be confident that it is being examined by the many Wrox
authors and other industry experts who are present on our mailing lists. At p2p.wrox.com you will find a
number of different lists that will help you, not only while you read this book, but also as you develop your
own applications. Particularly appropriate to this book is the ADO.NET list.

To subscribe to a mailing list just follow these steps:

1. Go to
2. Choose the appropriate category from the left menu bar
3. Click on the mailing list you wish to join
4. Follow the instructions to subscribe and fill in your e-mail address and password
5. Reply to the confirmation e-mail you receive
6. Use the subscription manager to join more lists and set your e-mail preferences

understand the basic principles behind .NET before attempting any ADO.NET programming, we'll quickly
review the basics here.

For more information about programming in the .NET environment, check out
Professional .NET Framework, ISBN 1-861005-56-3.