|
|
|
|
|
|
|
|
|
|
|
| Hitchhiker's Guide to Visual Basic® and SQL Server™, Sixth Edition |
|
|
|
|||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
Table of Contents
| Acknowledgements | xix |
| PART I Understanding Data Access Interfaces | |
| Chapter 1 The Road Ahead | 3 |
|
The Internet Craze |
4 |
|
Evolving Standards |
5 |
|
Where We Are Now |
7 |
|
And Where We're Headed |
11 |
| Chapter 2 Data Interfaces | 13 |
|
Accessing SQL Server from Visual Basic |
14 |
|
Understanding the Low-Level Interfaces |
22 |
|
Taking the Next Step |
28 |
| Chapter 3 Data Access: A Jump Start | 29 |
|
Getting Ready to Create Samples |
30 |
|
DAO/Jet Road Test |
37 |
|
DAO/ODBCDirect Road Test |
49 |
|
RDO Road Test |
54 |
|
ADO Road Test |
60 |
|
Comparing the Samples |
65 |
| Chapter 4 Choosing an Architecture | 69 |
|
Asking the Right Questions |
71 |
|
Dealing with the Answers |
76 |
|
Understanding the Different Architectures |
78 |
|
Evaluating Client/Server and Distributed Engine Architectures |
92 |
| PART II Designing SQL Server Applications | |
| Chapter 5 Planning Your Design | 113 |
|
The State Machine |
114 |
|
A Panoramic View of Cursors and Buffers |
115 |
|
Basic Design Decisions |
125 |
|
Avoiding the Top 10 Design Mistakes |
126 |
|
Creating a Virtual Application |
130 |
| Chapter 6 Making Connections to SQL Server | 139 |
|
Dog Paddling in the Connection Pool |
141 |
|
Connection Security |
142 |
|
Connection Management |
147 |
|
Managing Connection State |
151 |
|
Handling Connection Errors |
152 |
| Chapter 7 Designing and Building Queries | 161 |
|
Types of Queries |
162 |
|
Designing Queries for Performance |
163 |
|
Building Queries |
166 |
|
Understanding Queries |
169 |
|
Query-Related Error Messages |
179 |
|
Debugging with Visual Basic |
179 |
|
Addressing SQL Server Elements |
180 |
| Chapter 8 Retrieving Data and Working with Result Sets | 185 |
|
The Query Process |
186 |
|
Using Single-Row Result Sets |
187 |
|
Using Cursors |
188 |
|
Updating and Inserting Data |
191 |
|
Working with Batches |
193 |
| PART III Using DAO with the Jet Database Engine | |
| Chapter 9 The Jet Database Engine and the DAO Model | 199 |
|
DAO/Jet and SQL Server: Some History |
200 |
|
How Jet Accesses Remote Databases |
201 |
|
Understanding the DAO Model |
206 |
| Chapter 10 Data Access Objects Up Close | 215 |
|
Component Architecture |
217 |
|
The DBEngine Object |
217 |
|
Workspace Objects |
218 |
|
Database Objects |
220 |
|
TableDef Objects |
225 |
|
Field Objects |
230 |
|
Index Objects |
231 |
|
Tables and Indexes |
232 |
|
Creating or Adding Tables and Indexes |
233 |
| Chapter 11 Configuring and Tuning the Jet Engine | 235 |
|
The Windows Registry Database |
236 |
|
Understanding Jet Engine Options |
242 |
| Chapter 12 Using DAO/Jet to Get Connected | 249 |
|
Jet's Connection Management Scheme |
251 |
|
Connection Sharing |
252 |
|
Connection Caching and Aging |
253 |
|
Managing Connections on Your Own |
254 |
|
Opening Attached Tables |
257 |
|
Creating an Attached Table with DAO/Jet Methods |
258 |
|
Connecting: Common Pitfalls |
259 |
| Chapter 13 Using DAO/Jet to Access Data | 263 |
|
Creating DAO/Jet Cursors |
270 |
|
Understanding DAO/Jet Cursors |
274 |
|
Choosing an Index |
275 |
|
Using the ODBC Cache |
276 |
|
GetRows and Variant Arrays |
278 |
|
Relocating the Current-Row Pointer |
281 |
|
Updating DAO/Jet Data Sources |
285 |
|
Handling DAO/Jet Errors |
291 |
| Chapter 14 Executing Transact-SQL Queries | 293 |
|
Using SQL Pass-Through Queries |
294 |
|
Working with Stored Procedures |
305 |
|
Handling SQL Server Messages |
309 |
| Chapter 15 Using the Data Control | 313 |
|
Applying the Data Control |
315 |
|
Using the Data Control with SQL Server |
316 |
|
Setting Properties |
317 |
|
Checking the Properties |
319 |
|
Using Bound Controls |
320 |
| Chapter 16 Understanding the Jet Query Processor | 323 |
|
Constructs That Jet Must Evaluate Locally |
325 |
|
Restriction Splitting |
327 |
|
Evaluation of Outputs |
328 |
|
Removal of Execution of Crosstab Queries |
328 |
|
Outer Joins |
328 |
|
Generating SQL to Send to a Server |
329 |
|
Wildcards for the LIKE Operator |
329 |
|
Owner and Table Prefixing |
329 |
|
Identifier Quoting |
330 |
| Chapter 17 Using ODBCDirect | 331 |
|
Understanding the ODBCDirect Interface |
334 |
|
Activating ODBCDirect |
337 |
|
Connecting to Databases |
338 |
|
Comparing Database Objects and Connection Objects |
339 |
|
Executing SQL Queries |
342 |
|
Handling Recordset Objects |
346 |
|
Running Asynchronous Operations |
349 |
|
Optimistic Batch Updating |
349 |
|
Viewing Errors |
350 |
|
ODBCDirect Examples |
351 |
|
Tips for Converting Your Application to ODBCDirect |
355 |
|
Working with the Data Control |
357 |
| Chapter 18 Jet: A Traveler's Diary | 359 |
|
Allocating Database File Space |
360 |
|
Transaction Loss and Damage Repair |
361 |
|
Backing Up Access Databases |
361 |
|
Updating Records |
362 |
|
Tidbits |
362 |
| PART IV Using Remote Data Objects | |
| Chapter 19 Understanding Remote Data Objects | 365 |
|
Understanding RDO |
366 |
|
Design Features |
368 |
|
Comparing RDO and DAO |
373 |
|
Where RDO Falls Short |
375 |
|
Getting the Latest Information on RDO |
377 |
|
Licensing RDO |
377 |
| Chapter 20 Remote Data Objects Up Close | 379 |
|
Understanding the RDO Model |
380 |
|
The rdoEngine Object |
381 |
|
The rdoEnvironment Object |
385 |
|
The rdoConnection Object |
392 |
| Chapter 21 Getting Connected with RDO | 397 |
|
Locating and Naming the SQL Server |
398 |
|
Establishing a Connection |
400 |
| Chapter 22 Using the RDO UserConnection Designer | 413 |
|
Understanding Custom UserConnection Objects |
415 |
|
Building Custom UserConnection Objects |
417 |
|
Executing Queries and Stored Procedures as Methods |
421 |
|
Setting UserConnection Properties at Run Time |
431 |
|
Using the UserConnection Object's Events |
432 |
| Chapter 23 Building Result Sets with RDO | 433 |
|
Choosing a Cursor—or Not |
437 |
|
Support for rdoResultset Cursors |
444 |
|
Page Locking |
448 |
|
Specifying the Source of the Result Set |
451 |
|
Managing Asynchronous Operations |
452 |
|
Performance Tuning for rdoResultset Operations |
456 |
|
The rdoResultset Object |
459 |
|
The rdoColumn Object |
467 |
| Chapter 24 Optimistic Batch Updates | 471 |
|
An Overview of Optimistic Batch Updates |
473 |
|
Using RDO to Perform Optimistic Batch Updates |
474 |
| Chapter 25 Taking RDO Off Road | 485 |
|
The rdoQuery Object |
486 |
|
Working with Parameter Queries |
494 |
|
Executing Stored Procedures |
508 |
|
Managing Multiple Result Sets |
509 |
|
Working with Page-Based Data Types |
513 |
|
Face to Face with the rdoTable Object |
522 |
| Chapter 26 Maintaining Data with the RDO Interface | 527 |
|
Options for Modifying Data |
528 |
|
Adding Data |
531 |
|
Updating Data |
531 |
|
Deleting Data |
535 |
|
When Things Go Wrong |
536 |
| Chapter 27 Using the RemoteData Control | 539 |
|
What's Different About the RemoteData Control? |
541 |
|
RemoteData Control Properties |
543 |
|
RemoteData Control Methods and Events |
545 |
|
Setting Up Bound Controls |
549 |
| PART V Using ADO and OLE DB | |
| Chapter 28 Understanding ADO and OLE DB | 555 |
|
Your First Peek at OLE DB |
556 |
|
Your First Peek at ADO |
567 |
| Chapter 29 ActiveX Data Objects Up Close | 581 |
|
Understanding the ADO Object Model |
582 |
|
ADO Connection Object |
587 |
|
ADO Command Object |
602 |
|
ADO Recordset Object |
611 |
|
ADO Field Object |
650 |
|
ADO Parameter Object |
658 |
|
ADO Property Object |
661 |
|
ADO Error Object |
664 |
| Chapter 30 Getting Connected with ADO | 667 |
|
Establishing a Client/Server Connection |
669 |
|
Connecting Using a Code-Based Approach |
685 |
|
Connecting from a Component |
692 |
| Chapter 31 Building ADO Recordsets | 697 |
|
Building ADO Recordset Objects |
700 |
|
ADO Queries |
708 |
|
ADO Cursors |
717 |
|
Managing Concurrency with ADO |
729 |
|
Fetching ADO Recordset Data |
729 |
|
Filtering, Finding, and Sorting ADO Recordset Data |
732 |
| Chapter 32 Working with ADO Events and Asynchronous Operations | 739 |
|
ADO Event Types |
741 |
|
Enabling Event Handlers |
742 |
|
Examining Event Handler Arguments |
742 |
|
Two Event Examples |
745 |
|
Working with Will and Complete Event Handlers |
749 |
|
Managing Asynchronous Operations |
751 |
| Chapter 33 Taking ADO Off Road | 755 |
|
Building Custom ADO Command Objects |
756 |
|
Managing Complex (Stored) Procedures |
759 |
|
Performing Optimistic Client-Batch Operations |
769 |
|
Working with Page-Based Data |
774 |
|
Handling Special Cases |
780 |
| Chapter 34 Migrating from RDO to ADO | 789 |
|
ADO from an RDO Perspective |
791 |
|
To ADO or Not to ADO |
791 |
|
Using RDO to Solve Basic Data Access Problems |
792 |
|
Using RDO to Access More Complex Data |
798 |
|
Using ADO to Solve Basic Data Access Problems |
803 |
|
Using ADO to Access More Complex Data |
810 |
|
Conversion Tips and Techniques |
814 |
| PART VI Using the New Tools | |
| Chapter 35 Using the Transact-SQL Debugger | 821 |
|
Setting Up SQL Server and Visual Basic |
824 |
|
Invoking the TSQL Debugger Interactively |
828 |
|
Invoking the TSQL Debugger from Code |
831 |
|
Debugging the TSQL Debugger |
832 |
| Chapter 36 Using the Data View Window and Query Builder | 835 |
|
The Data View Window |
836 |
|
Using the Visual Database Tools to Manage Your SQL Server Database |
841 |
|
Creating Queries with Query Builder |
848 |
|
Using Query Builder with SQL Server |
854 |
| Chapter 37 Taking the Data Environment Designer Out for a Spin | 861 |
|
Getting Started with the Data Environment Designer |
864 |
|
Working with Stored Procedures from the Data Environment |
871 |
|
Working with Hierarchical Result Sets and Other Children |
878 |
| Chapter 38 Test Driving the Data Object Wizard | 883 |
|
Getting Started with the Data Object Wizard |
884 |
|
DOW Design Issues |
885 |
|
Setting Up DOW Commands |
886 |
|
A Simple DOW Example |
887 |
|
DOW Tips |
899 |
|
Using DOW Objects in an Application |
900 |
|
DOW-Generated Data Class Code |
902 |
|
DOW-Generated UserControl Code |
905 |
| APPENDIX A Setting Up the Test Databases | 911 |
|
Installing the Biblio Database |
912 |
|
Creating a DSN |
915 |
|
Adding Stored Procedures |
918 |
|
The Attach.MDB Database |
919 |
|
Setting Up the Sample Applications |
919 |
| APPENDIX B SQL Server Error Codes Decoded | 921 |
| APPENDIX C SQL Server Tips and Tricks | 933 |
|
Tips on the Bulk Copy Program |
934 |
|
Tips on Stored Procedures |
936 |
|
General Tips on Transact-SQL |
937 |
|
Tips for Enhancing Performance |
938 |
|
Tips for Efficient Designs |
940 |
|
Accessing Visual Basic Components from SQL Server |
942 |
| INDEX |
955 |
DUSTY ARCHIVES |
|
|
(Chapters on the Companion CD) |
|
| PART I The Visual Basic Library for SQL Server | |
| Chapter 1 Getting Ready for the VBSQL Interface | |
| Chapter 2 Getting Connected with VBSQL | |
| Chapter 3 Accessing Data with VBSQL | |
| Chapter 4 Using VBSQL Row Buffers | |
| Chapter 5 Using VBSQL Cursors | |
| Chapter 6 Taking the VBSQL Interface Off Road | |
| Chapter 7 Managing Page-Based Data | |
| Chapter 8 Running Bulk Copy via API | |
| PART II The ODBC API | |
| Chapter 9 The Core ODBC API Functions | |
| Chapter 10 Managing ODBC API Result Sets |
|
Visit Microsoft Press for more information on Hitchhiker's Guide to Visual Basic and SQL Server, Sixth Edition