A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2002 by Microsoft Corporation
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher.
Library of Congress Cataloging-in-Publication Data
Sceppa, David, 1972- Microsoft ADO.NET : core reference / David Sceppa. p. cm. ISBN 0-7356-1423-7 1. Database design. 2. Object oriented programming (Computer science) 3. ActiveX. I. Title. TK5105.8885.A26 S24 2002 005.74--dc21 2001059195
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 7 6 5 4 3 2
Distributed in Canada by Penguin Books Canada Limited.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further information about international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to firstname.lastname@example.org.
IntelliSense, Microsoft, Microsoft Press, MSDN, Visual Basic, Visual C#, Visual InterDev, Visual Studio, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
Acquisitions Editor: Anne Hamilton
Project Editor: Lynn Finnel
Technical Editor: Dail Magee Jr.
First and foremost, I'd like to thank my mother and father for their constant support and patience, through this project as well as through my life in general.
I'd like to thank Jackie Richards for her encouragement, feedback, and inquisitiveness regarding ADO.NET's feature set.
Thanks to Sam Carpenter for dragging me out for a beer, a few laughs, and some well-needed perspective throughout this process.
I'd like to thank Brad Rhodes for providing the Foreword to this book.
I'd like to thank Steve DuMosch for putting up with my occasional rants and for convincing me to get some use out of my indoor soccer shoes again.
Thanks to Steve Ellis for helping me to complete this project the right way.
Thanks to Dr. Jonathan and Stephanie Braman for that trip to the Met earlier this year and for giving my Xbox a good home for the past few months.
I'd like to thank and congratulate the Microsoft WebData and Visual Studio .NET teams for developing the most powerful set of data access technologies that we've released to date.
Finally, thanks to everyone at Microsoft Press who has played a role in getting this book onto the shelves.
Microsoft ADO.NET represents a major step forward for Microsoft data access technologies. It gives developers an unprecedented level of control over how their code interacts with their dataa welcome advance for developers who have been frustrated by the lack of control offered by previous "black box" technologies such as the ADO cursor engine, the Microsoft Visual Studio 6 Data Environment, and the MSDataShape OLE DB Provider.
ADO.NET is not only the most powerful and robust data access technology that Microsoft has produced to date, but it also requires arguably the steepest learning curve. I've watched a number of experienced Visual Studio 6 developers struggle with the ADO.NET object model in usability studies, trying to figure out where to get started. Developers who grasp the basic object model still wind up asking questions about some of the nuances in ADO.NET's feature set, such as "How do I control the table names that the DataAdapter uses to map the results of my batch query to my DataSet?" or "Why do I get duplicate rows in a DataSet that I build by hand if I fill it twice, when the same code doesn't create duplicate rows if I use a DataSet generated by Visual Studio .NET?"
Who Is This Book For?
I wrote this book as a thorough guide to ADO.NET for all developers, even those who have no experience with the technology. I do not assume that you know a DataReader from a DataSet. I've organized the book so that you can either read the chapters sequentially to learn the technology from scratch or, if you're more seasoned, you can find the information you need quickly and easily.
What's in the Book?
Each chapter that focuses on an object or a set of objects opens with a discussion of the object or objects followed by descriptions of how to use the major features of that object. Most chapters also show how you can save development time by building the object using Visual Studio .NET. The chapter includes reference information for the objects covered, followed by a section titled "Questions That Should Be Asked More Frequently," which addresses questions that are generally all too often overlooked. Some information in the reference section might repeat information from earlier in the chapter. I felt that this repetition was necessary in order to prevent the reader from having to jump back and forth within each chapter.
The book is divided into four parts. Part I provides an overview of the ADO.NET object model, followed by a guide to the Data Form Wizard. Part II shows how to use the various objects available in a .NET data providerthe Connection, Command, DataReader, and DataAdapter objects. Part III includes a discussion of the "disconnected" objects in the ADO.NET object modelthe DataSet, DataTable, DataColumn, DataRow, DataRelation, and DataView objects. This part also covers basic and advanced scenarios that use the DataAdapter to submit changes to your database, and it includes a discussion of ADO.NET's XML features. Part IV covers techniques for building effective Windows-based and Web applications using ADO.NET.
Code Samples, Tools, and Other Fun Stuff
Most of the code snippets in the book use the OLE DB .NET Data Provider. I chose to focus on this data provider because it's the more "generic" of the two .NET Data Providers included in the Microsoft .NET Framework. The code snippets are designed to communicate with the Microsoft Desktop Engine (MSDE) and Microsoft SQL Server, but they can be changed to communicate with any database that has an OLE DB provider. Appendix A includes code snippets that use the SQL Client, ODBC, and Oracle Client .NET Data Providers.
The book closes with Appendix B, which covers the three tools included on the book's companion CD: a Windows forms navigation control, an ad hoc query tool, and a tool that builds code for DataAdapter updating logic. Each tool is designed to work with any .NET data provider, not just the SQL Client and OLE DB .NET Data Providers, and the source code for each tool is also included on the compaion CD. I've included the source code for developers who want to modify the tools' functionality, but please do not redistribute the tools or code in any way, shape, or form. The tools are not supported and are not intended for distribution.
Each of these tools represents a work in progress. I plan to add functionality to each tool as well as to fix bugs and make the tools available at http://www.ininety.com for everyone who has purchased a copy of the book.
The companion CD includes the book's sample code, the three tools described above, and a fully searchable eBook. The book's sample code can also be found on the Web at http://www.microsoft.com/mspress/books/5354.aspTo load the companion content page, which includes links for downloading the sample files, click on the "Companion Content" link in the menu to the right of the screen.
I hope you find this information helpful.
You'll need the following software to run the companion content:
Microsoft .NET Framework SDK (can be downloaded from http://msdn.microsoft.com/net)
Microsoft Visual Studio .NET (optional, but recommended)
Microsoft Internet Explorer 5.01 or later
Every effort has been made to ensure the accuracy of this book and the contents of the companion CD. Microsoft Press provides corrections for books through the World Wide Web at the following address:
To connect directly to the Microsoft Press Knowledge Base and enter a query regarding a question or an issue that you may have, go to:
If you have comments, questions, or ideas regarding this book or the companion content, please send them to Microsoft Press using either of the following methods:
Attn: Microsoft ADO.NET (Core Reference) Editor
One Microsoft Way
Redmond, WA 98052-6399
Please note that product support is not offered through the above mail address. For support information regarding ADO.NET, Visual Basic .NET, Visual C# .NET, Visual Studio .NET, or the .NET Framework, please visit the Microsoft Press Support Web site at