| Acknowledgments | xvii |
| Introduction | xix |
| PART I GETTING STARTED WITH MICROSOFT ADO.NET | |
| 1 Overview of ADO.NET | 3 |
| Why a New Object Model? | 3 |
| The ADO.NET Object Model | 5 |
| .NET Data Providers | 6 |
| Why Use Separate Classes and Libraries? | 8 |
| Coverage of .NET Data Providers in This Book | 10 |
| Connected Objects | 10 |
| Disconnected Objects | 13 |
| Metadata | 20 |
| Strongly Typed DataSet Objects | 21 |
| Questions That Should Be Asked More Frequently | 23 |
| 2 Building ADO.NET Applications with the DataForm Wizard | 25 |
| Everyone Loves a Demo | 25 |
| Using the Data Form Wizard to Build a Data-Bound Form | 26 |
| Choosing a Connection | 29 |
| Selecting Tables from Your Database | 32 |
| Creating Relationships in Your DataSet | 32 |
| Selecting Columns to Display | 33 |
| Choosing a Display Style | 34 |
| Using the New Data-Bound Form | 35 |
| Showing Data in Your New Data-Bound Form | 37 |
| Cascading Changes with the DataRelation Object | 38 |
| Submitting Changes to Your Database | 39 |
| The Component Tray | 43 |
| The Data Form Wizard as a Starting Point | 45 |
| Questions That Should Be Asked More Frequently | 45 |
| PART II GETTING CONNECTED: USING A .NET DATA PROVIDER | |
| 3 Connecting to Your Database | 49 |
| Using Connection Objects | 50 |
| Creating Connection Objects | 54 |
| Connection Strings | 55 |
| Opening and Closing Connections | 60 |
| Connection Pooling | 61 |
| Destroying Connections | 65 |
| Using Connections to Create Other Objects | 65 |
| Retrieving Database Schema Information | 68 |
| Visual Studio .NET Design-Time Features | 70 |
| Working with Connections in Server Explorer | 70 |
| Adding a Data Connection to Server Explorer | 72 |
| Adding Connections to Your Application | 75 |
| Using the New Connection at Run Time | 77 |
| Where's the Code? | 78 |
| OleDbConnection Object Reference | 79 |
| Properties of the OleDbConnection Object | 80 |
| Methods of the OleDbConnection Object | 84 |
| Events of the OleDbConnection Object | 90 |
| Questions That Should Be Asked More Frequently | 94 |
| 4 Querying Your Database | 97 |
| Using Command Objects in Code | 97 |
| Creating a Command Object | 97 |
| Executing a Non-Row-Returning Query | 98 |
| Using a DataReader Object to Examine the Results of a Query | 101 |
| Executing a Query That Returns a Single Value | 110 |
| Executing a Parameterized Query | 111 |
| Calling a Stored Procedure | 113 |
| Retrieving Data from Output Parameters | 115 |
| Executing a Query Within a Transaction | 117 |
| Creating Commands in Visual Studio .NET | 118 |
| Dragging and Dropping from the Toolbox | 118 |
| Specifying a Connection | 119 |
| Using Query Builder | 120 |
| Using Your New Command Object in Code | 122 |
| Dragging and Dropping from Server Explorer | 124 |
| Command, DataReader, and Parameter Object Reference | 125 |
| Properties of the OleDbCommand Object | 126 |
| Methods of the OleDbCommand Object | 129 |
| Properties of the OleDbDataReader Object | 134 |
| Methods of the OleDbDataReader Object | 135 |
| Creating Parameter Objects | 141 |
| Properties of the OleDbParameter Object | 141 |
| Questions That Should Be Asked More Frequently | 145 |
| 5 Retrieving Data Using DataAdapter Objects | 157 |
| What Is a DataAdapter Object? | 158 |
| How the DataAdapter Differs from Other Query Objects | 158 |
| Anatomy of the DataAdapter | 161 |
| Creating and Using DataAdapter Objects | 164 |
| Creating a DataAdapter | 164 |
| Retrieving Results from a Query | 166 |
| Mapping the Results of Your Query to Your DataSet | 172 |
| Working with Batch Queries | 174 |
| Retrieving Rows from a Stored Procedure | 175 |
| Fetching Schema Information | 176 |
| Creating DataAdapter Objects in Visual Studio .NET | 177 |
| Dragging and Dropping a DataAdapter from the Toolbox | 178 |
| Using the Data Adapter Configuration Wizard | 178 |
| Dragging and Dropping from Server Explorer | 183 |
| Previewing the Results of Your DataAdapter | 184 |
| Examining the Code that the Wizard Generated | 185 |
| DataAdapter Reference | 185 |
| Properties of the DataAdapter Object | 185 |
| Methods of the DataAdapter Object | 191 |
| Events of the DataAdapter Object | 198 |
| Questions That Should Be Asked More Frequently | 201 |
| PART III WORKING WITH DATA OFF LINE—THE ADO.NET DATASET | |
| 6 Working with DataSet Objects | 205 |
| Features of the DataSet Object | 205 |
| Working with Disconnected Data | 206 |
| Scrolling, Sorting, Searching, and Filtering | 206 |
| Working with Hierarchical Data | 206 |
| Caching Changes | 207 |
| XML Integration | 207 |
| Uniform Functionality | 207 |
| Using DataSet Objects | 208 |
| Creating a DataSet Object | 208 |
| Examining the Structure Created by Calling DataAdapter.Fill | 209 |
| Examining the Data Returned by a DataAdapter | 211 |
| Validating Data in Your DataSet | 214 |
| Creating DataTable Objects in Code | 219 |
| Modifying the Contents of a DataTable | 235 |
| Working with DataSet Objects in Visual Studio .NET | 247 |
| Generating a DataSet from DataAdapter Objects | 247 |
| Creating a New DataSet from Scratch | 250 |
| Creating an Untyped DataSet | 254 |
| DataSet, DataTable, DataColumn, DataRow, UniqueConstraint, and ForeignKeyConstraint Object Reference | 258 |
| Properties of the DataSet Object | 258 |
| Methods of the DataSet Object | 263 |
| Events of the DataSet Object | 267 |
| Properties of the DataTable Object | 267 |
| Methods of the DataTable Object | 272 |
| Events of the DataTable Object | 277 |
| Properties of the DataColumn Object | 278 |
| Properties of the DataRow Object | 285 |
| Methods of the DataRow Object | 286 |
| Properties of the UniqueConstraint Object | 293 |
| Properties of the ForeignKeyConstraint Object | 294 |
| Questions That Should Be Asked More Frequently | 296 |
| 7 Working with Relational Data | 299 |
| A Brief Overview of Relational Data Access | 300 |
| Join Queries | 300 |
| Separate Queries | 302 |
| Hierarchical ADO Recordset Objects | 302 |
| ADO.NET DataRelation Objects | 303 |
| Working with DataRelation Objects in Code | 304 |
| Creating DataRelation Objects | 305 |
| Locating Related Data | 308 |
| Using DataRelation Objects to Validate Your Data | 311 |
| Self-Referencing DataRelationship Objects | 315 |
| Many-to-Many Relationships | 317 |
| Using DataRelation Objects in Expression-Based DataColumn Objects | 320 |
| Cascading Changes | 323 |
| Moving Away from Join Queries | 325 |
| Creating DataRelation Objects in Visual Studio .NET | 325 |
| Adding a DataRelation to a Strongly Typed DataSet | 325 |
| Adding a DataRelation to an Untyped DataSet | 327 |
| DataRelation Object Reference | 328 |
| Properties of the DataRelation Object | 328 |
| Questions That Should Be Asked More Frequently | 332 |
| 8 Sorting, Searching, and Filtering | 337 |
| Using the DataTable Object's Searching and Filtering Features | 337 |
| Locating a Row by Its Primary Key Values | 337 |
| Conducting More Dynamic Searches | 340 |
| Conducting Wildcard Searches | 341 |
| Working with Delimiters | 341 |
| Using the Additional Select Methods | 344 |
| What Is a DataView Object? | 346 |
| DataView Objects Return Data from a DataTable | 346 |
| DataView Objects Are Not SQL Queries | 347 |
| Working with DataView Objects in Code | 348 |
| Creating DataView Objects | 348 |
| Using the RowStateFilter Property | 349 |
| Using the DataRowView Object | 350 |
| Examining All Rows of Data Available Through a DataView | 351 |
| Searching for Data in a DataView | 352 |
| Modifying DataRowView Objects | 355 |
| Creating DataView Objects in Visual Studio .NET | 356 |
| Adding a New DataView Object to Your Designer | 356 |
| Setting Properties of Your DataView Object | 356 |
| DataView Object Reference | 357 |
| Properties of the DataView Object | 357 |
| Methods of the DataView Object | 361 |
| The ListChanged Event of the DataView Object | 365 |
| Properties of the DataRowView Object | 366 |
| Methods of the DataRowView Object | 367 |
| Questions That Should Be Asked More Frequently | 369 |
| 9 Working with Strongly Typed DataSet Objects | 375 |
| Creating Strongly Typed DataSet Objects | 376 |
| The Hard Way | 376 |
| The Easy Way | 379 |
| Using Strongly Typed DataSet Objects | 382 |
| Adding a Row | 382 |
| Finding a Row | 384 |
| Editing a Row | 385 |
| Working with Null Data | 386 |
| Working with Hierarchical Data | 387 |
| Other DataSet, DataTable, and DataRow Features | 389 |
| When to Use Strongly Typed DataSet Objects | 389 |
| Software Components and Swiss Army Knives | 389 |
| Design-Time Benefits | 390 |
| Run-Time Benefits | 391 |
| Additional Considerations | 394 |
| Choosing Your Path | 397 |
| Questions That Should Be Asked More Frequently | 397 |
| 10 Submitting Updates to Your Database | 401 |
| A History Lesson | 404 |
| Benefits of Submitting Updates Using ADO Recordset Objects | 405 |
| Drawbacks of Submitting Updates Using ADO Recordset Objects | 406 |
| Using ADO.NET Command Objects to Submit Updates | 407 |
| Using ADO.NET DataAdapter Objects to Submit Updates | 419 |
| Manually Configuring Your DataAdapter Objects | 419 |
| Introducing Bound Parameters | 419 |
| Using Stored Procedures to Submit Updates | 424 |
| Supplying Your Own Updating Logic | 430 |
| Using the CommandBuilder Object to Generate Updating Logic | 431 |
| How the CommandBuilder Generates Updating Logic | 432 |
| Benefits and Drawbacks of Using the CommandBuilder | 434 |
| Using the Data Adapter Configuration Wizard to Generate Updating Logic | 434 |
| Examining the Structure of the DataAdapter | 435 |
| Options for Building Updating Logic | 436 |
| Using Stored Procedures to Submit Updates | 437 |
| Benefits and Drawbacks of Using the Wizard | 441 |
| Other Updating Concerns | 441 |
| Optimistic Concurrency Options | 441 |
| Working with Null Values | 446 |
| Submitting Updates in Transactions | 447 |
| Using the TableMappings Collection | 451 |
| The Best Way to Update | 454 |
| OleDbCommandBuilder Object Reference | 455 |
| Properties of the OleDbCommandBuilder Object | 455 |
| Methods of the OleDbCommandBuilder Object | 456 |
| Questions That Should Be Asked More Frequently | 458 |
| 11 Advanced Updating Scenarios | 467 |
| Refreshing a Row After Submitting an Update | 468 |
| Retrieving the Newly Generated Value for the Timestamp Column After You Submit an Update | 469 |
| Using Batch Queries to Retrieve Data After You Submit an Update | 469 |
| Retrieving New Data Using Output Parameters | 471 |
| Using the DataAdapter Object's RowUpdated Event to Retrieve Data After You Submit an Update | 472 |
| The Timestamp Sample Application | 474 |
| Retrieving Newly Generated Autoincrement Values | 474 |
| Working with SQL Server | 475 |
| Working with Access 2000 | 478 |
| Working with Oracle Sequences | 478 |
| Sample Applications That Retrieve Autoincrement Values | 481 |
| Using SQL Server's NOCOUNT Setting | 482 |
| Submitting Hierarchical Changes | 485 |
| Submitting Pending Insertions and Deletions | 485 |
| Working with Autoincrement Values and Relational Data | 488 |
| Isolating and Reintegrating Changes | 490 |
| Saving Bandwidth Using the GetChanges Method | 491 |
| Handling Failed Update Attempts Elegantly | 503 |
| Planning Ahead for Conflicts | 503 |
| Informing the User of Failures | 504 |
| Fetching the Current Contents of Conflicting Rows | 506 |
| If at First You Don't Succeed. | 508 |
| The Conflicts Sample Application | 509 |
| Working with Distributed Transactions | 510 |
| Transaction Coordinators and Resource Managers | 512 |
| Distributed Transactions in the .NET Framework | 513 |
| Database Support for Distributed Transactions | 514 |
| Building Your Components | 514 |
| The DistributedTransaction Sample Application | 518 |
| Other Benefits of Using Component Services | 519 |
| When Handling Advanced Updating Scenarios, Use ADO.NET | 519 |
| Questions That Should Be Asked More Frequently | 520 |
| 12 Working with XML Data | 523 |
| Bridging the Gap Between XML and Data Access | 523 |
| Reading and Writing XML Data | 524 |
| The DataSet Object's XML Methods | 524 |
| Inferring Schemas | 528 |
| ADO.NET Properties That Affect the Schema of Your XML Document | 529 |
| Caching Changes and XML Documents | 531 |
| DataSet + XmlDocument = XmlDataDocument | 534 |
| Using the XmlDataDocument Object | 534 |
| Accessing Your DataSet as an XML Document | 534 |
| Caching Updates to the XML Document | 536 |
| Retrieving XML Data from SQL Server 2000 | 537 |
| Working with SELECT. FOR XML Queries | 538 |
| The SQL XML .NET Data Provider | 541 |
| Using a SqlXmlCommand to Load Data into an XmlDocument | 542 |
| Using a SqlXmlAdapter to Load Data into a DataSet | 543 |
| Working with Template Queries | 544 |
| Working with XPath Queries | 547 |
| Applying an XSLT Transform | 550 |
| Submitting Updates | 550 |
| A Simple ADO.NET and XML Sample | 555 |
| Two Paths, One Destination | 556 |
| ADO.NET and XML: A Happy Couple | 556 |
| Questions That Should Be Asked More Frequently | 557 |
| PART IV BUILDING EFFECTIVE APPLICATIONS WITH ADO.NET | |
| 13 Building Effective Windows-Based Applications | 561 |
| Building a User Interface Quickly Using Data Binding | 562 |
| Step 1: Creating Your DataAdapter and DataSet | 563 |
| Step 2: Adding Bound Textboxes | 564 |
| Step 3: Retrieving Data | 567 |
| Step 4: Adding Navigation Buttons | 568 |
| Step 5: Adding Add and Delete Buttons | 571 |
| Step 6: Submitting Changes | 572 |
| Step 7: Adding Edit, Update, and Cancel Buttons | 573 |
| Step 8: Viewing Child Data | 576 |
| Step 9: Binding a Second Form to the Same Data Source | 581 |
| Step 10: Improving the User Interface | 583 |
| Step 11: If You Want Something Done (Just) Right ... | 587 |
| Data Binding Summary | 588 |
| Application Design Considerations | 588 |
| Fetching Only the Data You Need | 588 |
| Updating Strategies | 589 |
| Connection Strategies | 593 |
| Working with BLOB Data | 595 |
| User Interfaces Built with ADO.NET Power | 601 |
| Questions That Should Be Asked More Frequently | 601 |
| 14 Building Effective Web Applications | 605 |
| Brief Introduction to Web Applications | 605 |
| ASP.NET Makes Building Web Applications Easier | 605 |
| The Good and Bad of Statelessness | 606 |
| Connecting to Your Database | 607 |
| Working with Trusted Connections | 607 |
| Working with Access Databases | 609 |
| Displaying Data on Your Web Page | 610 |
| Using DataBinder.Eval | 611 |
| Binding DataGrid Controls to the Results of Queries | 613 |
| Caching Data Between Round-Trips | 615 |
| The Stateless Approach—Maintaining No State | 615 |
| Caching Data at the Client | 616 |
| Maintaining State in Your Web Server | 619 |
| Maintaining State in Your Database | 621 |
| Guidelines for Maintaining State | 622 |
| Paging | 623 |
| Paging Features of the Web DataGrid | 624 |
| Paging Features of the DataAdapter Fill Method | 627 |
| Building Queries That Return a Page of Data | 628 |
| The PagingOptions Sample | 629 |
| Editing Data on a Web Page | 629 |
| Using the DataGrid to Simplify Editing Data | 630 |
| Handling the DataGrid's Editing Events | 631 |
| Submitting Changes to Your Database | 632 |
| The ShoppingCart Sample | 633 |
| Questions That Should Be Asked More Frequently | 634 |
| PART V APPENDIXES | |
| A Using Other .NET Data Providers | 641 |
| The SQL Client .NET Data Provider | 641 |
| Named Parameters vs. Parameter Markers | 641 |
| Connecting to a SQL Server Database Using a SqlConnection | 642 |
| Retrieving the Results of a Query Using a SqlDataAdapter | 643 |
| Using the SqlCommand and SqlDataReader Objects | 644 |
| The GetSql<DataType> Methods and the SqlTypes Namespace | 645 |
| Calling Stored Procedures | 647 |
| Retrieving Database Schema Information | 648 |
| The ODBC .NET Data Provider | 649 |
| Connecting to Your Database Using an OdbcConnection | 650 |
| Working with Parameterized Queries | 651 |
| Retrieving the Results of a Query Using an OdbcDataAdapter | 651 |
| Examining the Results of a Query Using an OdbcDataReader | 651 |
| Calling a Stored Procedure | 653 |
| Retrieving Database Schema Information | 654 |
| The Oracle Client .NET Data Provider | 654 |
| Connecting to Your Oracle Database Using an OracleConnection | 655 |
| Working with Parameterized Queries | 655 |
| Retrieving the Results of a Query Using an OracleDataAdapter | 655 |
| Examining the Results of a Query Using an OracleDataReader | 656 |
| Oracle-Specific Data Types | 657 |
| Calling a Stored Procedure | 658 |
| Fetching Data from Oracle REF Cursors | 660 |
| Retrieving Database Schema Information | 661 |
| Common Cross-Provider Concerns | 662 |
| Writing Provider-Portable Code | 662 |
| Determining the Correct .NET Provider Data Type | 666 |
| B Tools | 669 |
| ADO.NET Ad Hoc Query Tool | 669 |
| Connecting to Your Database | 670 |
| Adding .NET Data Providers | 670 |
| Executing Queries | 671 |
| Viewing Query Schema Information | 672 |
| Submitting Changes | 674 |
| Application Settings | 674 |
| ADO.NET DataAdapter Builder | 675 |
| Specifying Your Updating Logic | 676 |
| ADO.NET Navigation Control | 677 |
| Adding the ADO.NET Navigation Control to the Visual Studio .NET Toolbox | 678 |
| Setting the Data Properties of the ADO.NET Navigation Control | 678 |
| Setting the Remaining Properties of the ADO.NET Navigation Control | 679 |
| INDEX | 681 |
