Professional ADO.NET 2

Professional ADO.NET 2

Programming with SQL Server 2005,

Oracle®, and MySQL®

Wallace B. McClure

Gregory A. Beamer

John J. Croft IV

J. Ambrose Little

Bill Ryan

Phil Winstanley

David Yack

Jeremy Zongker

01_584375 ffirs.qxd 10/28/05 10:49 PM Page iii

Professional ADO.NET 2

Programming with SQL Server 2005,

Oracle®, and MySQL®

01_584375 ffirs.qxd 10/28/05 10:49 PM Page i

01_584375 ffirs.qxd 10/28/05 10:49 PM Page ii

Professional ADO.NET 2

Programming with SQL Server 2005,

Oracle®, and MySQL®

Wallace B. McClure

Gregory A. Beamer

John J. Croft IV

J. Ambrose Little

Bill Ryan

Phil Winstanley

David Yack

Jeremy Zongker

01_584375 ffirs.qxd 10/28/05 10:49 PM Page iii

Professional ADO.NET 2 Programming with SQL Server

2005, Oracle®, and MySQL®

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-8437-4

ISBN-10: 0-7645-8437-5

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1B/RW/RR/QV/IN

Library of Congress Cataloging-in-Publication Data is available from the publisher.

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

REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF

THE CONTENTS 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 PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS

REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT.

NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HERE￾FROM. 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 DISAP￾PEARED 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_584375 ffirs.qxd 10/28/05 10:49 PM Page iv

About the Authors

Wallace B. McClure

Wallace B. McClure graduated from the Georgia Institute of Technology in 1990 with a bachelor of

science degree in electrical engineering. He continued his education there, receiving a master’s degree in

the same field in 1991. Since that time, he has done consulting and development for such companies as

Coca-Cola, Bechtel National, Magnatron, and Lucent Technologies, among others. Products and

services have included work with ASP, ADO, XML, and SQL Server, as well as numerous applications in

the Microsoft .NET Framework. Wally McClure specializes in building applications that have large

numbers of users and large amounts of data. He is a Microsoft MVP and an ASPInsider, and a partner in

Scalable Development, Inc. You can read Wally’s blog at http://weblogs.asp.net/wallym/.

Gregory A. Beamer

Gregory A. Beamer is a solutions architect specializing in Microsoft Internet technologies. Greg got

involved in programming in the early 1990s with Visual Basic 3 and has stayed on the leading edge of

Microsoft Internet technologies since the Denali beta (ASP 1.0). Greg first worked with .NET with the

PDC 2000 beta and has been on both the SQL Server 2005 and .NET 2.0 betas since spring 2003. When

Greg is not working, he spends his time with his wife, Tiffany, and their four daughters, Rebecca, Emily,

Annabelle, and Miranda.

John J. Croft IV

John J. Croft IV graduated from the Georgia Institute of Technology in 1991, receiving a bachelor’s

degree in mechanical engineering. He then spent five years consulting for large companies, including

Coca-Cola, BellSouth, and MCI. Work at these companies primarily involved C and C++ programming

and object-oriented systems analysis. His various clients have included both Fortune 100s and small

startup companies. Their problems have ranged drastically, from large databases and executive informa￾tion systems to lithotripter control and satellite telemetry. Croft has completed projects with Java, XML,

and, recently, C# and .NET applications. He is a partner in Scalable Development, Inc.

J. Ambrose Little

Ambrose is the editor-in-chief of the ASPAlliance, an ASPInsider, and a Microsoft ASP.NET MVP who

currently works as a Web architect for a large credit union in Tampa, Florida. Previously, he worked as a

consultant at Verizon, creating XML Web Services and middle-tier components, and for BOK Financial’s

Web Services department creating ASP.NET applications for their intranet. His pre-.NET programming

experience consists mostly of developing Web applications using ASP and VB COM/DCOM for several

years. He has a bachelor’s degree in medieval European history, which remains an interest. Apart from

developing software, he enjoys movies, reading, writing, foosball, chess, tennis, badminton, and spend￾ing time with his wonderful family.

01_584375 ffirs.qxd 10/28/05 10:49 PM Page v

vi

About the Authors

Bill Ryan

Bill currently works as a senior software developer for TiBA Solutions in Greenville, SC. He is also a

Windows Embedded MVP, has served on Macromedia’s Flash Advisory Board, and helps run two

popular .NET Focused Web sites (www.devbuzz.com and www.knowdotnet.com) and his blog

www.msmvps.com/WilliamRyan. After earning his master’s degree in business administration, Bill

began work as a statistical analyst, but quickly realized that his true love was programming. He has

worked in multiple industries, including financial services/securities, manufacturing, health care, phar￾maceuticals, and, currently, consulting. Bill is a frequent speaker at user’s group meetings, has spoken at

multiple Microsoft Code Camps, and has hosted multiple MSDN Webcasts. Although technologically

related things consume most of his time, Bill’s other interests include cult films, economics,

Freemasonry, cuckoo clocks, and, most important, his girlfriend, Kim, and her daughter, Sarah.

Phil Winstanley

Phil Winstanley is a Web applications developer working for Portfolio Europe, located in Manchester,

England. He has been involved with ASP.NET since its inception, and has developed a deep understand￾ing of the platform. As a Microsoft MVP (Most Valuable Professional), member of the ASPInsiders,

co-owner of Microsoft Web Developers UK, and the North West England Regional Director for the .NET

Exchange, Phil is deeply embedded in the development community and works closely with the Web

Platforms team at Microsoft, regularly visiting the developers in Redmond, Washington.

David Yack

David is the president of Colorado Technology Consultants, Inc. He is a hands-on technology

consultant with solid management experience in privately held and Fortune 500 companies and has over

15 years of experience in the IT industry. David is currently responsible for leading Colorado Technology

Consultants’ focus on Microsoft .NET technologies. David is an active participant in the Microsoft

development community, ranging from the Denver .NET user group to Microsoft’s Public Communities,

such as www.asp.net and http://aspalliance.com. David is the leader of the South Colorado .NET

user group. David is recognized by Microsoft as a .NET MVP (Most Valuable Professional).

Jeremy Zongker

Jeremy Zongker is a software development manager who works primarily on data-driven ASP.NET

applications with Microsoft SQL Server databases. He is a Microsoft Certified Solutions Developer for

.NET and a 2004 MVP for ASP.NET. Jeremy is the founder and senior developer for Trilitech, LLC, a

Broken Arrow, Oklahoma, software development company.

01_584375 ffirs.qxd 10/28/05 10:49 PM Page vi

Credits

Acquisitions Editor

Bob Elliott

Development Editor

Gabrielle Nabi

Production Editor

William A. Barton

Technical Editor

Wiley - Dreamtech India Pvt Ltd

Copy Editor

Luann Rouff

Editorial Manager

Mary Beth Wakefield

Vice President and Publisher

Joseph B. Wikert

Project Coordinator

Michael Kruzil

Graphics and Production Specialists

Carrie Foster

Denny Hager

Jennifer Heleine

Alicia B. South

Quality Control Technicians

Amanda Briggs

John Greenough

Media Development Specialists

Angela Denny

Kit Malone

Travis Silvers

Proofreading and Indexing

TECHBOOKS Production Services

01_584375 ffirs.qxd 10/28/05 10:49 PM Page vii

01_584375 ffirs.qxd 10/28/05 10:49 PM Page viii

For my wife, Ronda, my two children, Kirsten and Bradley, and the rest of my family.

—Wallace B. McClure

To my loving wife and four daughters, and to God, without whom the aforementioned miracles would

not be possible.

—Greg Beamer

To my wife, Valerie, for her support, and to my boys, Jack and Conor, for their patience on the weekends

while I was writing.

—John J. Croft IV

To my mom and stepfather, for putting up with me all of these years and always being there. To my

girlfriend, Kim, and her daughter, Sarah, for always being able to make me smile.

—Bill Ryan

For my wife, Julie, and my two great kids, Drew and Jacqueline.

—David Yack

For my wife, Jeanette, for her support, patience, and understanding during the many hours I worked on

this book.

—Jeremy Zongker

To my caring father and mother, my loving brother, and to the Almighty, for giving me the power to

work every day.

—Anand Narayanaswamy,

Technical Editor

01_584375 ffirs.qxd 10/28/05 10:49 PM Page ix

01_584375 ffirs.qxd 10/28/05 10:49 PM Page x

Acknowledgments

The initial planning and thinking about this book began during a discussion of SQL Server futures in

July 2001. The discussion was with Rob Howard during a trip to Microsoft to discuss the first book I was

working on at that time. After that, I stayed involved in what was happening in ADO.NET by going to

the SQL Server Yukon Technical Preview in Bellevue, Washington, in February 2002 and by working

with the ASP.NET and SQL Server teams at Microsoft since July 2003.

Shortly after the excitement of talking with Bob Elliott at Wiley about this book wore off, it became

apparent that I would need to put together an author team that knew about the problems Microsoft was

trying to solve with ADO.NET Version 2. It is fortunate that I had recently been named a Microsoft MVP

and an ASPInsider. Based on memberships in those groups, I was able to work with and gain the respect

of Jeremy Zongker, Ambrose Little, and Phil Winstanley. From that group, I was able to meet David

Yack, William (Bill) Ryan, and Gregory Beamer. Adding these six people to John Croft and myself, we

created a really good group to work with. I want to thank them for working together very well, for

working quickly, and for examining the new features that are provided by ADO.NET 2 that are of partic￾ular interest to developers and readers of this book.

Personally, I would like to thank Bob Elliott for keeping me focused on what was going on and working

with us to develop this book. Our thanks also go out to the editorial staff at Wiley. Their help keeping us

on track as “life happened” was appreciated. The work of our technical editor, Anand Narayanaswamy,

was impressive, and his attention to detail was great. Many other people behind the scenes have worked

hard on the book. By pulling this group together, Wiley created a team that was dedicated to creating the

best possible book on ADO.NET Version 2. For that, we are truly appreciative.

— Wallace B. McClure and the author team

01_584375 ffirs.qxd 10/28/05 10:49 PM Page xi

01_584375 ffirs.qxd 10/28/05 10:49 PM Page xii

Contents

Acknowledgments xi

Contents xiii

Introduction xxv

What This Book Is About xxv

Who Should Buy This Book xxv

How to Use This Book xxvi

What This Book Covers xxvi

Providing Feedback xxvii

Chapter 1: History of Data Access 1

The Early Days 2

CODASYL 2

IMS 3

Relational Databases 3

The Birth of Universal Data Access 4

ODBC 4

OLE-DB 5

Data Access Consumers 6

DAO 6

RDO 7

ADO 8

ADO.NET 10

ADO.NET 2.0 11

Summary 12

For More Information 12

Chapter 2: Standardized Database Objects and Design 13

Creating Databases 13

Naming Conventions 15

Tables 15

Stored Procedures 17

Primary Keys 19

Foreign Keys 20

Indexes 22

02_584375 ftoc.qxd 10/28/05 10:48 PM Page xiii