7/28/2019 9781590590126-t1

1/6

Contents

Acknowledgments .................................................................................................xiii

About the Authors ..............................................................................................xv

Introduction ........................................................................................................xix

Chapter 1 Introducing ADO.NET.......................................................1

How We Got Here ....................................................................................................2

What Do These Changes Mean? ........................................................................5ADO.NETA New Beginning ..................................................................................7

Comparing ADOc and ADO.NET ......................................................................8

Understanding ADO.NET Infrastructure ......................................................9Understanding ADO.NETs Distributed Architecture ....................................10

A Brief Look at XML ..........................................................................................11

ADO.NETThe Fundamentals .......................................................................12

ADO.NET .NET Data Providers ........................................................................15Leveraging Existing COM-based ADO Code ..................................................17

Creating DataSets from ADOc Recordset Objects .........................................19

How COM Interop Affects .NET Applications ................................................19ADO.NET and Disconnected Data Structures ...........................................20The System.Data Namespace ............................................................................21

The ADO.NET DataSet Object .........................................................................22

Descending the System.Data Namespace Tree .............................................26

Instantiating System.Data Objects .................................................................30

Introducing the ADO.NET DataAdapter .........................................................31

ADO.NETs Low-Level Data Stream...............................................................36

vii

7/28/2019 9781590590126-t1

2/6

Chapter 2 ADO.NETGetting Connected......................................39

ADO.NET .NET Data Providers ........................................................................40Choosing the Right Provider ............................................................................41

Creating ADO.NET Connection Objects ......................................................42

Using Constructors in Declarations ................................................................43Building an ADO.NET ConnectionString .......................................................44

Connecting via the SqlClient .NET Data Provider............................51Introducing System.Data.SQLClient ...............................................................51

Examining the Connection Object Properties ...............................................53

Examining Connection Object Methods ........................................................55

Sinking Connection Object Events .................................................................56

Managing Transactions .....................................................................................59Connecting with the Odbc .NET Data Provider.....................................59

Using the Odbc .NET Data Provider ...............................................................60

Managing the Odbc.Connection Object .........................................................61ODBC Connection Strings ...............................................................................64

Opening ADO.NET Connections in Code ......................................................65Using the IDE to Get Connected.................................................................66

Adding Connections .........................................................................................66

Closing Connections .........................................................................................68Managing the ADO.NET Garbage Collector ...................................................68

Connection Information and Security ...........................................................69

ADO.NET Connection Pooling ........................................................................71

OleDb and Odbc Connection Pooling ............................................................75

Debugging the Connection Pool .....................................................................75

COM-based ADO as a .NET Data Provider..................................................75Instantiating Your ADOc Namespace .............................................................76

Chapter 3 ADO.NET Command Strategies ...................................79

Understanding ADO.NET Commands .................................................................79Introducing ADO.NET Parameter Queries .....................................................82

Executing Ad Hoc Queries Using ADO.NET ...................................................84

Constructing SQL for your Query ...................................................................85Creating a DataSet with the IDE ......................................................................98

Using Drag-and-Drop with the IDE ..............................................................102

Managing Parameter Queries ........................................................................111

Managing Multiple-Table Queries ................................................................116

Managing Stored Procedure Queries ............................................................121

Managing Multiple-Resultset Queries ..........................................................129

Exploring the Command Execute Methods .................................................133

Binding to Resultset Columns ...................................................................136

Contents

viii

7/28/2019 9781590590126-t1

3/6

Chapter 4 ADO.NET DataReader Strategies .........................141

Comparing ADOc with ADO.NET......................................................................142Understanding the DataReader...................................................................144Using the DataReader Get Methods to Retrieve Data .....................151

Using GetValues to Fetch a Row ....................................................................154Cleaning Up After Your DataReader........................................................155

Chapter 5 Using the DataTable and DataSet ....................157

Understanding the DataSet and DataTable Structure .....................159Typed Versus Untyped DataSets ....................................................................161

Importing ADOc Recordsets ..........................................................................168Comparing ADOc Recordsets and DataTable Objects ................................169

Building DataTables .......................................................................................172Building DataTables with the Constructor ...................................................173

Building DataTables Using the DataAdapter Fill Method ...........................173

Building DataTable Objects Within a DataSet ..............................................177

Using FillSchema to Retrieve DDL ................................................................180

Accessing DataTable Data ............................................................................181Accessing the DataColumnCollection (Columns) .......................................182

Understanding the DataRow Object .............................................................187

Accessing the DataRowCollection (Rows) ....................................................187

Testing for Results ..........................................................................................191

Accessing DataSet Data ................................................................................194Positioning the Current Row Pointer ............................................................195

Binding to DataSets .......................................................................................196

Chapter 6 Filtering, Sorting, and Finding ....................197

Filtering and Sorting with the Select Method ...............................198Sorting with the Select Method .....................................................................199

Filtering on RowState and Version ................................................................205

Filtering and Sorting with the DataView Object ............................205Setting DataView Filter Properties ................................................................206

Creating DataView Objects with Visual Studio ............................................210

Updating a DataView .....................................................................................210

Working with the DataViewManager...........................................................211The DefaultViewManager Property ..............................................................212

Using the Find Methods ................................................................................212The Rows Collection Find Method ................................................................213

The DataView Objects Find Method ............................................................216

The Rows Collection Contains Method ........................................................219

Contents

ix

7/28/2019 9781590590126-t1

4/6

Chapter 7 ADO.NET Update Strategies ....................................221

Changing DataThe Fundamentals ...............................................................223Addressing the Value Property with Untyped Datasets ...............................223

Addressing the Value Property with Typed Datasets ...................................224

Creating New DataRows ................................................................................224Deleting DataSet Rows ...................................................................................226

Undoing a Delete Operation ......................................................................227

Postponing Constraint Enforcement ............................................................228

Using BeginEdit ..............................................................................................228

Data Validation ...............................................................................................229

Checking RowState ........................................................................................233

Update Strategies ...........................................................................................238Creating Updatable DataSets ........................................................................239

Using the CommandBuilder Class ................................................................240

Using Visual Studio to Generate Action Queries ............................251Reviewing the Generated Stored Procedures ..............................................251

Reviewing the Generated Code .....................................................................254

Retrieving Autonumber, Identity, or GUIDs ......................................258Understanding the Update Methods ...........................................................262Update Performance .........................................................................................264

Another Approach to Bulk Inserts: Use XML ...............................................265

Updating the Authors TableAn Example ................................................266Working with an Updatable DataGrid Control ............................................268

Showing the Available Data Versions ............................................................268

Showing the Row Counts ...............................................................................270

Using GetChanges to Manage Modified Rows .............................................272

Update Exception Handling Strategies ..................................................273Using the ContinueAfterError Property........................................................274

What to Do When an Update Fails ................................................................276

Handling Update Method Exceptions ........................................................277Coding the Update DBConcurrencyException Trap ...................................279

Executing Ad Hoc Queries to Perform Updates ...................................280Merging and Transporting Data .................................................................281

Chapter 8 ADO.NET Constraint Strategies .........................283

Understanding .NET Constraints ...............................................................283How ADO.NET Implements Constraints ......................................................285

Managing the Constraints Collection ...........................................................286

Implementing Unique Constraints ...............................................................287

Implementing Foreign Key Constraints .......................................................289

Understanding Parent/Child Primary Key/Foreign Key Relationships .....292

Using the FillSchema Method .......................................................................294

Creating DataRelation Objects .................................................................295

Contents

x

7/28/2019 9781590590126-t1

5/6

Chapter 9 ADO.NET Exception Management Strategies ..301

Structured Exception Handling .................................................................302Understanding try/catch ..............................................................................303

try/catch Syntax .............................................................................................303

Standard Exceptions ......................................................................................303Variable Scope in try/catch Blocks ...............................................................305

Exceptions That Fire Outside of try/catch Scope ........................................305

Nesting Exception Handlers ..........................................................................305

Trapping Specific vs. General Exceptions .........................................306The System.Data Exceptions ........................................................................306

The SqlException Class ..................................................................................308

The SqlError Class ..........................................................................................310

The OleDbException Class ............................................................................310

Displaying the Error Message ...................................................................311

Chapter 10 ADO.NET and XML ..............................................................313

XML Support in ADO.NET................................................................................314Standardizing on XML Formats ...................................................................315Understanding XML and DataSet Objects ................................................316

Importing DataSet Data from an XML Document ......................................316

What Are DiffGrams? ......................................................................................319

Merging XML Data with Your DataSet .........................................................321

Fetching and Saving DataSet Objects as XML ..............................................321Loading Schema from XML ...........................................................................324

Persisting XML Schema .................................................................................325

Creating a Web Service as an XML Data Source ................................326Passing an Updated DataSet Back to a Web Service ........................336Creating Custom Exception Handlers ......................................................341ADO.NET and SOAP ..............................................................................................342

Appendix Working with the

Biblio Sample Database ...........................................343

Index ....................................................................................................................345

Contents

xi

7/28/2019 9781590590126-t1

6/6

http://www.springer.com/978-1-59059-012-6