Serious ADO: Universal Data Access with Visual Basic

614 Pages·2000·16.45 MB·English

by  Rob Macdonald (auth.)

Serious ADO:
Universal Data Access
with Visual Basic
ROB MACDONALD
APress Media, LLC
Serious ADO: Universal Data Access with Visual Basic
Copyright ©2000 by Rob Macdonald
Originally published by Apress in 2000
Ali rights reserved. No part of this work maybe reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information stor
age or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN 978-1-893115-19-4 ISBN 978-1-4302-0862-4 (eBook)
DOI 10.1007/978-1-4302-0862-4
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names on1y in an editorial fashion and to the ben
efit of the trademark owner, with no intention of infringement of the trademark.
Technical Reviewer: Russ Lewis
Editors: Andy Carroll and Grace Wong
Production Services, Page Composition, and Icon Trainer: Susan Glinert
Artist: Karl Miyajima
Indexer: Nancy Guenther
Cover and Interior Design: Derek Yee Design
Additional material to this book can be downloaded rrom http://extras.springer.com
In the United States, phone 1-800-SPRINGER; [email protected];
www.springer-ny.com
Outside the United States, contact [email protected]; www.springer.de; fax
[phone]
For information on translations, please contact Apress directly at 901 Grayson Street, Suite 204,
Berkeley, California, 94710
Phone: [phone]; Fax: [phone]; [email protected];www.apress.com
The information in this book is distributed on an "as is" basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
Contents at a Glance
Introduction ....................................................................................................... xiii
Chapter 1 ADO's Big Idea ........................................................................... 1
Part One ADO IN DEPTH .......................................................................... 9
Chapter 2 ADO Unplugged ........................................................................... 11
Chapter 3 Recordsets with SQL Data Sources .................................. 63
Chapter 4 Explicit Connections .......................................................... 127
Chapter 5 Explicit Commands ................................................................. 163
Chapter 6 The ADO Event Model and
Asynchronous Processing .................................................... 193
Chapter 7 Disconnected Recordsets .................................................... 227
Chapter 8 Recordset Recursion and Data Shaping ....................... 287
Chapter 9 Working with Documents--
Records and Streams ............................................................. 327
Chapter 10 The ADO Data Definition Language
and Security Model (ADOX) ............................................... 361
Chapter 11 Creating Simple Providers ............................................... 381
Part Two ADO AT LARGE ...................................................................... 419
Chapter 12 Binding and Data-aware Objects .................................... 421
Chapter 13 The Data Environment .......................................................... 451
Chapter 14 ADO, DNA, MTS, and COM+ .................................................... 481
Chapter 15 RDS and DHTML ......................................................................... 513
Chapter 16 Multidimensional ADO (ADOMD) ......................................... 543
GLOSSARY ................................................................................................................ 581
Index ...................................................................................................................... 591
iii
Contents
Introduction ................................................................................................... xiii
Chapter 1 ADO-'s Big Idea .................................................................... 1
The Look and Feel of ADO .................................................................................. 2
ADO Components and Libraries ......................................................................... 3
Summary ....................................................................................................................... 7
Part One ADO IN DEPTH ....................................................................
9
Chapter 2 ADO Unplugged .................................................................... 11
Recordset and Field Objects ......................................................................... 12
Look} No Connection! -Creating a Standalone Recordset ................. 15
Basic Record set Navigation ............................................................................
23
Find} Sort} and Filter .................................................................................... 27
Working with Pages ............................................................................................. 34
Modification Operations .................................................................................. 35
ADO Data Types and Attributes ..................................................................... 41
Data Types .......................................................................................................... 42
Attributes .. .. . .. . .. .. .. .. .. .. .. . .. .. .. .. .. . .. .. .. .. .. .. .. .. .. .. . .. . .. .. .. .. .. . .. . .. .. . .. .. . . .. . .. .. .. . . .. .. . .. .. .. .. . 44
Decimal and Numeric Data Types .................................................................... 45
Introducing Property Objects ....................................................................... 46
Recordset Optimizations ..................................................................................
48
Cloning ..................................................................................................................... 52
Recordset Error Handling ................................................................................ 56
Recordsets Compared to Other Data Structures .................................... 57
The Data Structure Performance "Shoot-out" ................................................ 58
Performance Tests Applied in the "Shoot-out" ............................................... 59
The "Shoot-out" Results .................................................................................... 60
Summary ..................................................................................................................... 61
v
Contents
Chapter 3 Recordsets with SQL Data Sources .................... 63
ADO and ODBC ........................................................................................................... 65
The Open Method .................................................................................................... 70
Staying Connected-Cursor Control ............................................................... 77
Forward-Only Cursor ......................................................................................... 81
Static Cursor ........................................................................................................ 82
Keyset Cursor ...................................................................................................... 83
Dynamic Cursor .................................................................................................. 86
Unspecified Cursor ............................................................................................ 87
Staying Connected-Lock Control ................................................................... 88
Read-Only Lock .................................................................................................. 91
Pessimistic Lock. ................................................................................................. 91
Optimistic Lock ................................................................................................... 93
Batch Optimistic Lock ........................................................................................ 94
Examining the Fields Collection ................................................................. 95
Handling Null Data ............................................................................................ 96
Handling Large Data Fields ............................................................................... 97
Handling Special Columns ................................................................................ 99
Dynamic Field Properties .. . .. .. .. . .. .. .. .. . .. .. .. . .. .. . ... . .. .. .. .. .. .. .. .. .. .. . .. . .. .. .. . .. .. .. . .. .. .. . 100
Modifications and Locking with Client-Side Cursors ...................... 103
Overcoming Missing Fields ............................................................................. 106
Modifications Applied to Joins ........................................................................ 108
Specifying a Unique Table ............................................................................... 110
Resynchronizing Connected Recordsets ........................................................ 112
Using Index and Seek ....................................................................................... 117
Processing Multiple Recordsets ................................................................. 119
Optimization for SQL Data Sources ........................................................... 121
Append-only Recordsets .................................................................................. 123
Cache Size ......................................................................................................... 123
Summary ................................................................................................................... 124
Chapter 4 Explicit Connections ................................................. 127
Creating and Using Connections ................................................................. 128
Data Link Files .................................................................................................. 131
Other Properties Used When Connecting ...................................................... 135
The Execute Method ........................................................................................... 135
Getting Schema Information .......................................................................... 138
Connection Properties ..................................................................................... 142
ADO and Transactions ....................................................................................... 144
Programming Transactions with ADO ........................................................... 146
Concurrency and Isolation .............................................................................. 148
vi
Contents
ADO and Errors .................................................................................................... 152
Connection Pooling ........................................................................................... 153
A Two-Tier Pooling Scenario........................................................................... 156
AnN-TierPoolingScenario ............................................................................. 159
Summary ................................................................................................................... 162
Chapter 5 Explicit Commands ........................................................ 163
Why Use Command Objects? .............................................................................. 163
Parameters and Parameter Objects ............................................................. 171
The Lazy Way to Use Paran1eters. .. .. ................ .. ....................... ..... .. ......... .. .... 173
The Inefficient Way to Use Paran1eters .......................................................... 173
Using Explicit Paran1eter Objects Efficiently ................................................. 17 4
Commands with Multiple Paran1eters .. ...... .................. ................. ........ ........ 176
Working with Stored Procedures ................................................................. 177
Stored Procedure Example .............................................................................. 182
Stored Procedures with Multiple SELECT Statements ................................. 185
Stored Procedures and Temporary Tables ..................................................... 186
Command Optimizations ..................................................................................... 189
Summary ................................................................................................................... 191
Chapter 6 The ADO Event Model
and Asynchronous Processing .............................. 193
Connection Events ............................................................................................. 194
Recordset Events ................................................................................................ 204
Strategies for Using Events ....................................................................... 210
Asynchronous Processing ................................................................................ 213
Opening a Connection Asynchronously ........................................................ 216
Cancelling an Asynchronous Command ....................................................... 218
Asynchronous Fetch with Client -side Cursors .............................................. 220
Summary ................................................................................................................... 224
Chapter 7 Disconnected Recordsets ........................................ 227
ADO in an N-Tier Environment ..................................................................... 229
The Mechanics of Disconnecting ................................................................. 236
Batch Updating .................................................................................................... 238
Preprocessing a Batch Update ........................................................................ 242
Postprocessing a Batch Update ...................................................................... 24 7
Advanced Postprocessing ................................................................................ 249
vii
Contents
Resynchronizing Disconnected Recordsets ............................................. 254
Disconnected Recordsets and Stored Procedures ................................ 257
Recordset Marshalling ..................................................................................... 264
Batch Updating Using anN-Tier Model ........................................................ 270
Recordset Persistence ..................................................................................... 277
Persisting with a Stream Object ...................................................................... 278
Persisting Using the ASP Response Object .................................................... 280
Persisting with a PropertyBag ......................................................................... 282
Persisting with an MSMQ Message Body ....................................................... 284
Explicit Persisting ............................................................................................. 284
Summary ................................................................................................................... 285
Chapter 8 Recordset Recursion
and Data Shaping ..........................................................
287
The Idea of Data Shaping .............................................................................. 288
Creating the Connection String ................................................................. 294
Relation-based Hierarchies .......................................................................... 296
Hierarchical Recordset Navigation ................................................................. 298
Generic Hierarchical Recordset Navigation .................................................. 300
Updating Hierarchical Recordsets .................................................................. 301
Creating Complex Shapes Using Reshaping .................................................. 303
Chapterless Child Recordsets .......................................................................... 307
Avoiding Command Objects ........................................................................... 308
Extending and Fabricating Recordsets
Using Data Shaping .................................................................................... 308
Fabricated Hierarchical Recordsets ............................................................... 310
Combining Provider and Fabricated Recordsets .......................................... 312
Parameterized Data Shaping .......................................................................... 313
External Parameters ......................................................................................... 313
Internal Parameters ......................................................................................... 314
Combining Internal and External Parameters ............................................... 316
Group-based Hierarchies ................................................................................ 319
Functions Supported by the COMPUTE Statement... ................................... 322
Combining Group-based and
Parameterized Relation-based Hierarchies ........................................... 323
Summary ................................................................................................................... 325
Chapter 9 Working with Documents--
Records and Streams ...................................................
327
Semi-structured Data ....................................................................................... 328
viii
Contents
The Internet Publishing Provider ............................................................. 332
The Record Object .............................................................................................. 333
Opening and Closing Records ......................................................................... 336
Deleting, Moving, and Copying Resources .................................................... 339
The GetChildren Method ................................................................................. 341
Using Records and Recordsets ...................................................................... 342
Streams ................................................................................................................... 349
Opening, Closing, and Saving Streams ........................................................... 351
Uploading and Downloading Using Streams ................................................ 352
Stream Content Manipulation ........................................................................ 355
Summary ................................................................................................................... 358
Chapter 10 The ADO Data Definition Language
and Security Model (ADOX) .................................... 361
The Death of DAD ................................................................................................ 362
Using ADOX ............................................................................................................. 363
Working with Table Objects ............................................................................ 365
Adding Tables into SQLServer ......................................................................... 369
ADOX and Jet ......................................................................................................... 371
Using ADOX Security Features with Jet .......................................................... 373
Summary ................................................................................................................... 379
Chapter 11 Creating Simple Providers .................................... 381
Why Do It? ............................................................................................................. 382
The Simple Provider Interface ................................................................... 385
Creating a Read-only Provider ................................................................... 388
Reading VB Source Code .................................................................................. 389
Creating the Record Source Object Class ....................................................... 392
Creating the Data Source Object Class ........................................................... 399
Registering and Using Simple Providers ................................................ 400
Creating an Updateable Provider ............................................................... 405
Supporting OLEDBSimpleProviderListeners ................................................. 407
Implementing deleteRows ............................................................................... 408
Implementing insertRows ............................................................................... 410
Implementing setVariant ................................................................................. 411
Data Shaping with Simple Providers ......................................................... 415
Summary ................................................................................................................... 417
ix
Contents
Part Two ADO AT LARGE ................................................................
.419
Chapter 12 Binding and Data-aware Objects ...................... .421
Binding with Controls ..................................................................................... 422
Binding Programmatically ............................................................................... 425
Using Format Objects ...................................................................................... 427
Binding to Controls and Properties
That Are Not Data Aware .......................................................................... 429
Binding with Classes ....................................................................................... 430
Creating a Nonvisual Data Consumer ............................................................ 435
The Data Repeater Control ............................................................................ 438
Binding Hierarchical Recordsets ............................................................... 442
Displaying Complex Hierarchies .................................................................... 445
Summary ................................................................................................................... 448
Chapter 13 The Data Environment ................................................ .451
What Is a Data Environment? ........................................................................ 452
Building a Form Using Drag-and-Drop .......................................................... 456
Building a Form Using Explicit Binding ......................................................... 459
Using the Data Environment Programmatically ........................................... 460
Adding Code to the Data Environment .......................................................... 462
Using Parameterized Commands ...................................................................... 464
Executing Before Loading the Form ............................................................... 466
Executing After Loading the Form .................................................................. 468
Defining Hierarchical Recordsets ............................................................. 470
Making Data Environments into Components ........................................... 474
Summary ................................................................................................................... 478
Chapter 14 ADO} DNA} MTSJ and COM+ ........................................ .481
What Are MTS and COM+? ................................................................................... 483
Non-Transactional Components .................................................................... 484
Transactional Components ............................................................................. 487
Connection-oriented vs. Transaction-oriented Systems .............................. 489
Creating Transactional Components Using ADO ..................................... 493
Distributed Transactions .............................................................................. 500
Controlling Secondary Objects in COM+ ....................................................... 506
Controlling Secondary Objects in MTS .......................................................... 508
Summary ................................................................................................................... 511
X