Expert one-on-one Visual BASIC 2005 database programming

Expert One-on-One™ Visual Basic®2005

Database Programming

Roger Jennings

01_57678x ffirs.qxd 11/10/05 11:25 PM Page iii

Expert One-on-One™ Visual Basic®2005

Database Programming

01_57678x ffirs.qxd 11/10/05 11:25 PM Page i

01_57678x ffirs.qxd 11/10/05 11:25 PM Page ii

Expert One-on-One™ Visual Basic®2005

Database Programming

Roger Jennings

01_57678x ffirs.qxd 11/10/05 11:25 PM Page iii

Expert One-on-One™ Visual Basic®2005

Database Programming

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-7645-7678-2

ISBN-10: 0-7645-7678-X

Printed in the United States of America

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1B/TQ/RR/QV/IN

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any

means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section

107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or

authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222

Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission

should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN

46256, (317) 572-3447, fax (317) 572-4355 or online at http://www.wiley.com/go/permissions.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REP￾RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CON￾TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT

LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED

OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED

HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING

THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFES￾SIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFES￾SIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE

FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO

IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT

MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR

WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE

THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN

WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services please contact our Customer Care Department

within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade

dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United

States and other countries, and may not be used without written permission. Linux is a registered trademark

of Linus Torvalds. MySQL is a registered trademark of MySQL AB A Company. All other trademarks are the

property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor

mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not

be available in electronic books.

01_57678x ffirs.qxd 11/10/05 11:25 PM Page iv

About the Author

Roger Jennings

Roger Jennings is an author and consultant specializing in Microsoft Visual Basic .NET n-tier and

client/server database applications, and data-intensive ASP.NET Web services. He’s been a member of

the beta test team for all versions of Visual Basic starting with the Professional Extensions for Visual

Basic 2.0 (code-named Rawhide) and Visual Studio, all releases of Microsoft SQL Server starting with

version 4.2 for OS/2, every version of Microsoft Access, and all Windows client and server operating

systems beginning with the Windows 3.0 Multimedia Extensions.

Roger’s 25 computer-oriented books have more than 1.25 million English copies in print and have been

translated into more than 20 languages. He’s the author of three editions of Database Developer’s Guide

to Visual Basic (SAMS Publishing), two editions of Access Developer’s Guide (SAMS), nine editions of

Special Edition Using Microsoft Access (QUE Publishing), and two editions of Special Edition Using

Windows NT 4.0 Server (QUE). He has also written developer-oriented books about Windows 3.1 multi￾media, Windows 95, Windows 2000 Server, Active Directory Group Policy, Visual Basic .NET Web

services, and Microsoft Office InfoPath 2003 SP-1. Roger is a contributing editor of Fawcette Technical

Publications’ Visual Studio Magazine and a columnist for Fawcette’s .NETInsight and XML & Web

Services Insight newsletters.

Roger has more than 25 years of computer-related experience, beginning with real-time medical data

acquisition and chemical process control systems driven by Wang 700 calculators/computers. He is

a principal of OakLeaf Systems, a Northern California software consulting firm, the developer of

the OakLeaf XML Web Services site (www.oakleaf.ws/), and author of the OakLeaf Systems weblog

(oakleafblog.blogspot.com). His OakLeaf Code of Federal Regulations (CFR) ASP.NET Web service

and client (www.oakleaf.ws/cfr/) projects won the charter Microsoft .NET Best Award for Horizontal

Solutions (www.microsoft.com/presspass/features/2002/aug02/08-07netwinners.mspx).

You can reach Roger at [email protected].

01_57678x ffirs.qxd 11/10/05 11:25 PM Page v

01_57678x ffirs.qxd 11/10/05 11:25 PM Page vi

Credits

Executive Editor

Robert Elliott

Development Editor

Adaobi Obi Tulton

Technical Editor

Thomas Rizzo

Production Editor

Pamela Hanley

Copy Editor

Nancy Rapoport

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President & Executive Group Publisher

Richard Swadley

Vice President and Publisher

Joseph B. Wikert

Quality Control Technicians

Leeann Harney

Jessica Kramer

Joe Niesen

Project Coordinator

Michael Kruzil

Graphics and Production Specialists

Carrie Foster

Denny Hager

Barbara Moore

Alicia B. South

Proofreading and Indexing

TECHBOOKS Production Services

01_57678x ffirs.qxd 11/10/05 11:25 PM Page vii

01_57678x ffirs.qxd 11/10/05 11:25 PM Page viii

This book is dedicated to my wife, Alexandra.

01_57678x ffirs.qxd 11/10/05 11:25 PM Page ix

01_57678x ffirs.qxd 11/10/05 11:25 PM Page x

Contents

Acknowledgments xxi

Introduction xxiii

Part I: ADO.NET 2.0 Basics 1

Chapter 1: Migrating from ADO to ADO.NET 3

A New Approach to Data Access 4

The System.Data Namespace 4

ADO.NET Data Providers 6

Basic ADO.NET Data Objects 7

Creating Basic ADO.NET Data Objects with SqlClient 8

Applying Transactions to Multi-Table Updates 15

Using OleDb, SqlXml, and Odbc Member Classes 18

Working with Typed DataReader and SqlResultSet Data 21

ADO.NET Typed DataSet Objects 23

Add a Typed DataSet from an SQL Server Data Source 24

Add a DataGridView and BindingNavigator Controls 30

Persist and Reopen the DataSet 32

Change from a DataViewGrid to a Details Form 33

Add a Related DataBound Control 34

Summary 37

Chapter 2: Introducing New ADO.NET 2.0 Features 39

Working with New ADO.NET 2.0 Runtime Windows Form Objects 39

Use the DbProviderFactories to Create Database-Agnostic Projects 40

Retrieve Base Table Schemas 43

Check Available SQL Server Instances and ADO.NET 2.0 Data Providers 46

Batch Inserts to SQL Server Tables with the SqlBulkCopy Object 47

Get SQL Server Connection Statistics 51

Execute SqlCommands Asynchronously 52

Create Standalone DataTables 62

Use Nullable Types to Support DBNull Values 66

02_57678x ftoc.qxd 11/10/05 11:27 PM Page xi

xii

Contents

Using New ADO.NET 2.0 Persistent Windows Form Objects 69

Compare ADO.NET 1.x and 2.0 Data Designers 70

Add Missing ADO.NET Controls to the Toolbox 72

Upgrade 1.x Projects to ADO.NET 2.0 Components 72

Add Multi-Level Subforms 73

Parameterize the MasterDetailsForm 75

Batch Multiple Updates 77

Design and Display Reports with the ReportViewer Control 79

Summary 82

Chapter 3: Adopting Best Practices for Data-Centric Projects 83

Establish Architectural Best Practices 84

Reference Architectures 85

Find Patterns for Projects 86

Enterprise Solution Patterns Using Microsoft .NET 87

Data Patterns 87

Distributed Systems Patterns 88

Integration Patterns 88

Try Application Block Libraries 89

The Data Access Application Block 90

The DataAccessQuickStart Client 94

Adhere to Design Guides 96

The .NET Data Access Architecture Guide 96

Improving .NET Application Performance and Scalability 96

Designing Data Tier Components and Passing Data Through Tiers 97

Apply Class Library Design Guidelines 102

Naming Guidelines 102

Class Member Usage Guidelines 103

Prepare for Service-Oriented Architecture 103

The Road to Service-Oriented Architecture 104

Implement SOA with Web Services 105

Ensure Fully Interoperable Web Services 106

Use FxCop to Validate Project Code 110

Automate Test-Driven Development 112

Add a Unit Test Project to a VS 2005 Solution 112

Edit and Run the Wizard-Generated Tests 114

Run the SQL Server 2000 Best Practices Analyzer 116

Apply Specific Best Practices to ADO.NET 2.0 Projects 118

Use Identical Connection Strings to Pool Database Connections 118

Run SQL Server Profiler to Inspect SQL and RPC Queries 120

Avoid Adding Runtime CommandBuilder Instances 121

02_57678x ftoc.qxd 11/10/05 11:27 PM Page xii

xiii

Contents

Substitute Stored Procedures for SQL Batch Queries 122

Add Default Values for Parameters That Aren’t Required 122

Use sp_executesql and Named Parameters to Reuse Cached Query Plans 122

Add timestamp Columns for Optimistic Concurrency Control 123

Check All Related Records in Concurrency Tests 126

Batch Updates to Minimize Server Roundtrips 126

Avoid SqlExceptions with Client-Side Validation 126

Summary 127

Part II: Data Binding in Windows Forms and Controls 129

Chapter 4: Programming TableAdapters, BindingSources,

and DataGridViews 131

Design a Basic Customer-Orders-Order Details Form 132

Reduce DataSet Size with Parameterized Queries 132

Create the Data Source and Add the Controls 133

Add FillBy Methods for Each Data Table 135

Alter the Autogenerated Code to Fill the Controls 137

Fill the ComboBox with CustomerID Values 137

Clean Up the UI and Code 138

Format and Add DataGridView Columns 139

Format the OrdersDataGridView Columns 140

Format and Add a Computed Column to the Order_DetailsDataGridView 141

Provide Default Values for New Records 143

Add Default Orders Record Values 144

Add Default Order Details Record Values 145

Handle the DataErrors Event 146

Streamline Heads-Down Data Entry 146

Migrate the UI to a Tabbed Form 149

Test Drive the OrdersByCustomersV3 Project 149

Fix Missing Default Values When Adding Rows with Code 150

Edit a Selected DataGridView Record on the Second Tab Page 152

Create and Bind Lookup Lists for Primary Key Values 153

Create an Untyped Lookup DataSet and Its DataTables 154

Populate the cboCustomerID Combo Box 156

Replace DataGridView Text Boxes with Combo Boxes 156

Associate Combo Boxes with Text Boxes 159

Add a Combo Box That Sets Additional Values 162

Create and Bind a DataView Sorted by ProductName 162

Test for Duplicates and Update the UnitPrice Column 163

02_57678x ftoc.qxd 11/10/05 11:27 PM Page xiii