Microsoft .NET Framework 3.5—ADO.NET Application Development

524 Pages·2009·5.11 MB·English

www.it-ebooks.info
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2009 by Shawn Wildermuth, Mark Blomsma, Jim Wightman
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 Control Number: 2009920790
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWE 4 3 2 1 0 9
Distributed in Canada by H.B. Fenn and Company Ltd.
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 infor¬mation about
international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at
fax [phone]. Visit our Web site at www.microsoft.com/mspress. Send comments to [email protected].
Microsoft, Microsoft Press, Access, ActiveX, Bankshot Billiards, BattleTech, Blinx, Blue Dragon, Brute Force, Carbonated
Games, Crackdown, Crimson Skies, Digital Anvil, Ensemble Studios, Excel, FASA Studio, Forza Motorsport, Fuzion
Frenzy, Halo, Halo Wars, Hexic, High Road to Revenge, Infinite Undiscovery, Internet Explorer, Lost Odyssey, MechAssault,
MS, MSDN, Ninety-Nine Nights, Perfect Dark Zero, Phantom Dust, Project Gotham Racing, Quantum Redshift, Rally,
Shadowrun, Silverlight, Sneakers, SQL Server, The Time Sweeper, Visual Basic, Visual C#, Visual Studio, Viva Piñata,
Whacked!, Windows, Windows NT, Windows Server, Windows Vista, Xbox, Xbox 360 and Xbox LIVE 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.
This book expresses the author’s views and opinions. The information contained in this book is provided without any
express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will
be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.
Acquisitions Editor: Ken Jones
Developmental Editor: Laura Sackerman
Project Editor: Maureen Zimmerman
Editorial Production: Christian Holdener, S4Carlisle Publishing Services
Technical Reviewer: Matt Stoecker; Technical Review services provided by Content Master, a member of CM Group, Ltd.
Cover: Tom Draper Design
Body Part No. X15-41556
www.it-ebooks.info
To Tricia:
“See it wasn’t that bad. . .”
—Shawn wildermuth
To Marcel de Vries:
Thanks!
—mark BlomSma
To Eleanor:
“You are #1, everyone else is #2 or lower.”
—JameS wightman
www.it-ebooks.info
Contents at a Glance
Introduction xvii
Chapter 1 Creating Database Connections 1
Chapter 2 Selecting and Querying Data 83
Chapter 3 DataSets 133
Chapter 4 Updating Data 185
Chapter 5 Synchronizing Data 237
Chapter 6 Introducing LINQ 279
Chapter 7 XML 323
Chapter 8 LINQ to SQL 361
Chapter 9 Using the Entity Framework 395
Chapter 10 ADO.NET Data Services 423
Answers 463
Glossary 479
Index 481
www.it-ebooks.info
www.it-ebooks.info
Contents
Introduction xvii
Chapter 1 Creating Database Connections 1
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Lesson 1: Connecting to a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Connection Object Overview 3
Lesson Summary 50
Lesson Review 50
Lesson 2: Using Data Providers and More Complex
Connection Scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Data Providers 54
Lesson Summary 68
Lesson Review 68
Lesson 3: Working with Multiple Active Result Sets . . . . . . . . . . . . . . . . . . . 71
MARS 71
Lesson Summary 79
Lesson Review 80
Chapter Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81
Chapter Summary 81
Key Terms 81
Case Scenario 81
Suggested Practice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Take a Practice Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our
books and learning resources for you. To participate in a brief online survey, please visit:
www.microsoft.com/learning/booksurvey/ vii
www.it-ebooks.info
Chapter 2 Selecting and Querying Data 83
Lesson 1: Building Command Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Command Object Overview 85
DbParameters Object 92
Lesson Summary 102
Lesson Review 103
Lesson 2: Consuming Data Before You Begin . . . . . . . . . . . . . . . . . . . . . . .104
Writing Queries 104
Using DataReader 107
Using DbDataAdapter and DbTableAdapter 109
Asynchronous Queries 112
Special Datatypes 115
Using LINQ 120
Lesson Summary 129
Lesson Review 129
Chapter Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .131
Chapter Summary 131
Key Terms 131
Case Scenario 131
Suggested Practice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Take a Practice Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Chapter 3 DataSets 133
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Lesson 1: Introduction to DataSets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
DataSet Class Hierarchy 135
Reading Data from a Database 151
DataSet Datatypes 152
SQL Server User-Defined Types 153
Sample: Order and OrderDetails 153
Using a DataTableReader 160
Lesson Summary 165
Lesson Review 166
viii Contents
www.it-ebooks.info
Lesson 2: Working with Typed DataSets . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Using Typed DataSets 168
Sample: Products and Suppliers 175
Lesson Summary 179
Lesson Review 180
Chapter Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .181
Chapter Summary 181
Key Terms 181
Case Scenario 182
Suggested Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .182
Using ADO .NET in a Windows application 182
Take a Practice Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Chapter 4 Updating Data 185
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Lesson 1: Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187
Insert /Update/Delete Data Using a Command 187
Update Data Using a DataAdapter 191
Using a Command Builder 191
Performing Bulk Updates 193
Update Data Using a Table Adapter 193
Updating Custom Types 195
Using Stored Procedures 197
Sample: Updating the Database 197
Lesson Review 209
Lesson 2: Data Integrity and Transactions . . . . . . . . . . . . . . . . . . . . . . . . . .213
Enforcing Data Integrity in a DataSet 213
Sample: Deleting Products and Suppliers 223
Lesson Summary 233
Lesson Review 233
Chapter Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
Chapter Summary 235
Key Terms 235
Case Scenario 235
Contents ix
www.it-ebooks.info
Suggested Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236
Take a Practice Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Chapter 5 Synchronizing Data 237
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Lesson 1: Caching Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .239
Why Cache? 240
Caching Options 240
ASP .NET Caching 241
Desktop Caching 249
Lesson Summary 257
Lesson Review 257
Lesson 2: Microsoft Sync Framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
What Is the Microsoft Sync Framework? 259
Using the Sync Services for ADO .NET 261
Lesson Summary 274
Lesson Review 275
Chapter Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .276
Chapter Summary 276
Key Terms 276
Case Scenarios 276
Suggested Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .277
Caching Data 277
Microsoft Sync Framework 277
Take a Practice Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Chapter 6 Introducing LINQ 279
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
Lesson 1: Constructing Queries with LINQ . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Why LINQ? 281
LINQ Basics 283
Your First LINQ Query 288
Expressions 292
Query Execution 296
x Contents
www.it-ebooks.info
Lesson Summary 302
Lesson Review 302
Lesson 2: Shaping Results with LINQ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .304
Retrieving Primitive Results 304
Projecting Results into Types 305
Projecting Results into Anonymous Types 307
Joining with LINQ 308
Grouping with LINQ 311
Lesson Summary 318
Lesson Review 318
Chapter Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .320
Chapter Summary 320
Key Terms 320
Case Scenario 320
Suggested Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .321
Introducing LINQ 321
Take a Practice Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Chapter 7 XML 323
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
Lesson 1: DataSets and XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Writing a DataSet as XML 325
Reading XML with a DataSet 328
DataSets and XML Schema 331
Customizing XML Serialization 336
Using DiffGrams 339
Lesson Summary 346
Lesson Review 347
Lesson 2: Querying XML with LINQ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .348
Introducing Queryable XML Classes 348
Constructing a Query 350
Working with Results 352
Lesson Summary 358
Lesson Review 358
Contents xi
www.it-ebooks.info