NET 4 ® Step by Step Tim Patrick Copyright © 2010 Tim Patrick. Complying with all applicable copyright laws is the responsibility of the user. All rights reserved. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without express written permission of Microsoft Press, Inc.
Printed and bound in the United States of America. 1 2 3 4 5 6 7 8 9 WCT 5 4 3 2 1 0 Microsoft Press titles may be purchased for educational, business or sales promotional use. Online editions are also available for most titles (http://my. For more information, contact our corporate/institutional sales department: (800) 998-9938 or send comments to mspinput@microsoft.
Microsoft, Microsoft Press, ActiveX, Excel, FrontPage, Internet Explorer, PowerPoint, SharePoint, Webdings, Windows, and Windows 7 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. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, prod- uct, 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 author, Microsoft Corporation, nor their respective resellers or distributors, will be held liable for any damages caused or alleged to be caused either directly or indirectly by such information. Acquisitions and Development Editor: Russell Jones Production Editor: Kristen Borg Production Services: Octal Publishing, Inc. Technical Reviewer: Sahil Malik Indexing: Potomac Indexing, LLC Cover: Karen Montgomery Compositor: Susan Brown Illustrator: Robert Romano 978-0-735-63888-4 To Abel Chan, a good friend and a good programmer.
Contents at a Glance Part I Getting to Know ADO.NET Chapter 1 Introducing ADO. 3 Chapter 2 Building Tables of Data. 17 Chapter 3 Storing Data in Memory. 37 Chapter 4 Accessing the Right Data Values.
59 Chapter 5 Bringing Related Data Together. 73 Chapter 6 Turning Data into Information. 89 Chapter 7 Saving and Restoring Data. 107 Part II Connecting to External Data Sources Chapter 8 Establishing External Connections.
121 Chapter 9 Querying Databases. 135 Chapter 10 Adding Standards to Queries. 153 Chapter 11 Making External Data Available Locally. 169 Chapter 12 Guaranteeing Data Integrity.
191 v vi Contents at a Glance Part III Entity Framework Chapter 13 Introducing the Entity Framework. 213 Chapter 14 Visualizing Data Models. 225 Chapter 15 Querying Data in the Framework. 245 Chapter 16 Understanding Entities Through Objects.
267 Part IV LINQ Chapter 17 Introducing LINQ. 289 Chapter 18 Using LINQ to DataSet. 305 Chapter 19 Using LINQ to Entities. 315 Chapter 20 Using LINQ to SQL.
331 Part V Providing RESTful Services with WCF Data Services Chapter 21 Binding Data with ADO. 347 Chapter 22 Providing RESTful Services with WCF Data Services. 369 Table of Contents Acknowledgments. xvii Part I Getting to Know ADO.NET 1 Introducing ADO.
3 What Is ADO. 5 Major Components of ADO. 5 Extensions to ADO. 7 Connecting to External Data.
15 Chapter 1 Quick Reference. 16 2 Building Tables of Data. 17 Logical and Physical Table Implementations. 17 The DataTable Class.
18 Adding Data Columns. 34 Chapter 2 Quick Reference. 35 3 Storing Data in Memory. 37 Creating New Rows.
37 Defining Row Values. 38 Storing Rows in a Table. 40 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.com/learning/booksurvey/ vii viii Table of Contents Examining and Changing Data.
49 Exception-Based Errors. 50 Validation-Based Errors. 56 Chapter 3 Quick Reference. 57 4 Accessing the Right Data Values.
59 Querying and Sorting Data. 59 Finding Rows by Primary Key. 60 Selecting Rows with a Search Criteria. 62 Sorting Search Results.
64 Performing Case-Sensitive Lookups. 67 Using Expression Columns. 71 Chapter 4 Quick Reference. 71 5 Bringing Related Data Together.
73 Collecting Tables into Sets. 73 Establishing Relationships Between Tables. 76 Understanding Table Relations. 76 Creating Data Relations.
78 Locating Parent and Child Records. 79 Defining Table Constraints. 87 Chapter 5 Quick Reference. 88 6 Turning Data into Information.
89 Generating a Single Aggregate. 91 Adding an Aggregate Column. 94 Aggregating Data Across Related Tables. 95 Referencing Parent Fields in Expressions.
98 Table of Contents ix Setting Up Indexed Views. 106 Chapter 6 Quick Reference. 106 7 Saving and Restoring Data. 107 Serializing DataSet and DataTable Objects.
110 Guiding XML Generation. 111 Nesting Child Tables. 113 Managing and Positioning Columns. 117 Chapter 7 Quick Reference.
118 Part II Connecting to External Data Sources 8 Establishing External Connections. 121 Using Connection Strings. 121 SQL Server Connection Strings. 122 OLE DB and ODBC Connection Strings.
124 Connection String Builders. 124 Storing Connection Strings. 126 Understanding Data Providers. 126 Connecting to SQL Server via a Data Provider.
127 Creating and Opening Connections. 133 Chapter 8 Quick Reference. 135 Processing SQL Queries. 135 Creating Command Objects.
139 x Table of Contents Returning Query Results. 140 Returning a Single Value. 141 Returning Data Rows. 142 Accessing Field Values.
144 Processing More Complicated Results. 150 Chapter 9 Quick Reference. 151 10 Adding Standards to Queries. 153 Developing Parameterized Queries.
153 Understanding the Need for Parameters. 154 Implementing Standard Queries. 155 Using Parameters with Other Providers. 160 Using Parameters in Stored Procedures.
166 Chapter 10 Quick Reference. 167 11 Making External Data Available Locally. 169 Understanding Data Adapters. 169 Moving Data from Source to Memory.
171 Moving Data into a DataTable. 171 Moving Data into a DataSet. 173 Moving Data from Memory to Source. 175 Configuring the Update Commands.
175 Performing the Update. 179 Generating Update Commands Automatically. 180 Table and Column Mapping. 188 Chapter 11 Quick Reference.
189 12 Guaranteeing Data Integrity. 191 Transactions and Concurrency. 191 Using Local Transactions. 202 Using Distributed Transactions.
208 Chapter 12 Quick Reference. 209 Table of Contents xi Part III Entity Framework 13 Introducing the Entity Framework. 213 Understanding the Entity Framework. 213 Defining the Entity Framework’s Terms.
215 Understanding the Entity Framework’s Layers. 216 Understanding the Conceptual Model. 217 Understanding the Storage Model. 217 Understanding the Model Mappings.
218 Using the Entity Framework. 218 Building the Model. 218 Generating the Objects. 220 Instantiating the Context.
221 Running Framework Queries. 223 Chapter 13 Quick Reference. 223 14 Visualizing Data Models. 225 Designing an Entity Framework Model.
225 Using the Entity Data Model Wizard. 225 Entity Data Model Designer. 230 Working with the Mapping Details Panel. 235 Using the Model Browser.
240 Managing the Object Layer. 242 Chapter 14 Quick Reference. 243 15 Querying Data in the Framework. 245 Getting to Know Entity SQL.
246 Writing Basic Queries. 246 Using Literals, Operators, and Expressions. 249 Grouping and Aggregating Entity Data. 252 Using Features Unique to Entity SQL.
254 Running Entity SQL Queries. 256 Running Queries Using an ObjectQuery. 256 Running Queries Using a Provider. 264 Chapter 15 Quick Reference.
265 xii Table of Contents 16 Understanding Entities Through Objects. 267 Managing Entity Data Through Objects. 267 Accessing Entity Data Through Objects. 268 Modifying Entity Data Through Objects.
271 Using Query Builder Methods. 278 Queryable Extension Methods. 285 Chapter 16 Quick Reference. 285 Part IV LINQ 17 Introducing LINQ.
289 Getting to Know LINQ. 289 Using LINQ with. 291 Starting a Query with the From Clause. 293 Projecting Results with the Select Clause.
293 Filtering Results with the Where Clause. 295 Sorting Results with the Order By Clause. 296 Selecting Linked Results with the Join Keyword. 297 Limiting the Queried Content.
299 Summarizing Data Using Aggregates. 301 Applying Set Operations. 303 Chapter 17 Quick Reference. 304 18 Using LINQ to DataSet.
305 Understanding the LINQ to DataSet Provider. 305 Writing Queries with LINQ to DataSet. 312 Chapter 18 Quick Reference. 313 19 Using LINQ to Entities.
315 Understanding the LINQ to Entities Provider. 315 Writing Queries with LINQ to Entities. 316 Working with Entity and Database Functions. 321 Working with Custom Database Functions.
329 Chapter 19 Quick Reference. 329 Table of Contents xiii 20 Using LINQ to SQL. 331 Understanding the LINQ to SQL Provider. 332 Comparing LINQ to SQL with LINQ to Entities.
332 Understanding the Components of LINQ to SQL. 333 Using the Object Relational Designer. 335 Using Custom Database Functions in Queries. 343 Chapter 20 Quick Reference.
343 Part V Presenting Data to the World 21 Binding Data with ADO. 347 Binding Data in Windows Forms. 347 Creating Complex-Bound Applications. 348 Creating Simple-Bound Applications.
351 Understanding Windows Forms Data Binding. 352 Binding Data in WPF. 354 Creating Data-Bound WPF Applications. 354 Understanding WPF Data Binding.
360 Binding Data in ASP. 362 Creating Data-Bound ASP.NET Data Binding. 367 Chapter 21 Quick Reference. 367 22 Providing RESTful Services with WCF Data Services.
369 Getting to Know the Service Layers. 369 Introducing WCF Data Services. 370 Setting Up a Data Service. 371 Defining Service Rights.
375 Accessing a Data Service using REST. 377 Querying Entities with REST. 377 Updating Entities with REST. 384 Chapter 22 Quick Reference.
385 Acknowledgments An open-ended thank you goes to Microsoft, not only for developing some of the products that have kept me gainfully employed for nearly three decades, but for welcoming me into their book-writing fold. It was also a pleasure to work again with the team at O’Reilly Media, Microsoft’s publishing partner. Editors Russell Jones and Kristen Borg kept all the trains run- ning on time, which wasn’t easy given the busy technical and publishing traffic. Rounding out the group were Meghan Blanchette, Sumita Mukherji, and Adam Witwer.
Thank you all. Sahil Malik, ADO.NET expert and fellow author, has the distinction of having read through every single word of this book looking for technical concerns. Nancy Sixsmith did the same for the mechanics of language, grammar, and consistency. The book is richer for their involvement.
Claudette Moore once again worked her agenting magic, somehow always managing to make everyone on both sides of a contract happy. This book would be nothing more than a series of discarded emails were it not for her hard work and dedication. Thank you, Claudette, for yet another adventure. Thanks to all my friends at Harvest, especially fellow food and movie lovers Alice, Brenda, Andy, Suzy, Matt, Tiffany, Jeff, and Monica.
Love and appreciation in heaps to my wife Maki and my son Spencer, both of whom exude patience and care. And thanks once again to God for making all these other acknowledgments possible in the first place. Tim Patrick October 2010 xv Introduction ADO.