MCTS Self-Paced Training Kit (Exam 70-516): Accessing Data with Microsoft .NET Framework 4

671 Pages·2011·15.57 MB·English

by  Glenn Johnson

Introduction
System Requirements
CHAPTER
Hardware Requirements
Introducing LIN3Q
Software Requirements
Entities Before You Begin Code Samples
Adding New EnLetistsieosn t 1o: DUantdaeCrostnatnexdting Using the CD
Case Scenario 2: UDseilnegti nthge E EnntittiitLeyIs NFrQamework How to Install the Practice Tests
Suggested PracticUessing Stored PAro LcIeNdQu rEexsample How to Use the Practice Tests
AAnbsowuet rtshe AuthorCQCrrueeeaarttieee saann AApppplliiccaaSLUtteuiisoosbinsnnmog TTn iDthh tSaaianuttt mgaUM CCmsooehadnsDLFar tiIeLnfiyeeNaIgexfNQtetseu Qr stDrrPo ee iartn sdooSt auTa vEE ihbbxdTnameryettaic r tiUnuMtsie sstCsiaaion hckngatei n oUgnpe s HASucopkwnp ootowr tUl e&nd iFgnemsetaedlnbl tatshcek Practice Tests
LINQ to Entities Lesson ReviewLINQ Errata
Take a Practice TeCsatse ScenarioLesson Summary We Want to Hear from You
CHAPTER 7 Case Scenario:L Oesbsjoenct -ROevriieenwted Data Stay in Touch
WCF Data ServiceAsccess Lesson 2: Using LINQ Preparing for the Exam
Before You BeginSuggested PraQctuiceersies CHAPTER 1
Lesson 1: What IsC WreCaFte D aant aA SpeprSlviycicnaettisao?xn- BTahsaetd U asneds LINQ ADO.NET Disconnected Classes
Introducing ODattao SQL QueriesMethod-Based Queries Before You Begin
Creating a WCF DCarteaa Stee ravnic AeppLliIcNaQtio Kne Tyhwaotr Mdsodifies Lesson 1: Working with the DataTable and DataSet Classes
Querying Data thDroautag hb yW UCsFin Dga PLtrIaNo jSQeec rttvoioi cSneQssL The DataTable Class
Lesson SummaryTake a PracticeU Tseinstg the Let Keyword Using DataView as a Window into a Data Table
Lesson Review CHAPTER 5 to Help with Projections Using a DataSet Object to Coordinate Work Between Data Tables
Lesson 2: ConsumLiInNgQ W toC FX MDaLtaS Speercvificyeinsg a Filter Lesson Summary
Adding a Client ABpepfolicrea tYioonu BegSpine cifying a Sort Order Lesson Review
Lesson SummaryLesson 1: WorkPianggi nwgith the Lesson 2: Serialization, Specialized Types, and Data Binding
Lesson Review XmlDocumenJto ainnsd XmlReader Serializing and Deserializing the Data Table with XML Data
Case Scenario Classes Grouping and Serializing and Deserializing DataSet Objects
Case Scenario: ExTphoes iXnmg lDDaotcaumAegngtr Celgaastsion Handling Specialized Types
Suggested PracticTehse XmlReaderP aCrlaalslsel LINQ (PLINQ) Data Binding Overview
Take a Practice TeLsetsson SummaLreysson Summary Lesson Summary
CHAPTER 8 Lesson ReviewLesson Review Lesson Review
Developing ReliabLeles sAopnp 2li:c QatuioenrCysainseg Swciethn aLrIiNoQs to XML Case Scenarios
Before You BeginIntroducing thCe aXsDe oSccuemnaerniot F1a: mily Case Scenario 1: The Traveling Sales Team
Lesson 1: MonitoUrinsign ga nthde C XoDlleocFctiubinmogne nPatce crCif loaSresmsqeuasnecnec eData Case Scenario 2: Sorting and Filtering Data
Implementing InsLterussmonen StuamtiomnaCrayse Scenario 2: Sorting Suggested Practices
Logging QueriesLesson Reviewand Filtering Data Take a Practice Test
Accessing and ImLpelsesmonen 3t:i nTgra PnesSfruofogrmrgmeinsatgne cdXe M PCrLoa Ucutnsiictneegrss CHAPTER 2
Lesson SummaryLINQ to XML Create Query with ADO.NET Connected Classes
Lesson Review Transforming XEMxtLe ntosi oOnb Mjecettshods Before You Begin
Lesson 2: HandlinTgra Enxscfeoprmtioinngs XCMreLa tteo LTIeNxQt Queries Lesson 1: Connecting to the Data Store
Preventing ConneTcratinosnfo arnmdi nCgo mXTMamkLea ntaod P XErMaxccLetipceti oTnesst Using Providers to Move Data
Handling ConnecLteiosnso ann Sdu QmumeraCyrH yEAxcPeTpEtRio 4ns Getting Started with the DbConnection Object
Handling ExceptiLoensss oWnh Reenv SieuwbLmINitQtin tgo CShQaLnges Storing the Connection String in the Application Configuration File
Lesson SummaryCase ScenarioBefore You Begin Encrypted Communications to SQL Server
Lesson Review Case Scenario:L XeMssLo nW 1e:b W Shearvt iIcse LINQ Storing Encrypted Connection Strings in Web Applications
Lesson 3: ProtectiSnugg gYoeustre Dd aPtraatcoti cSeQsL? Connection Pooling
Encoding vs. EncrTyapkteio an PracticeM Toesdteling Your Data Lesson Summary
Symmetric CryptoCgHrAapPhTEyR 6 Examining the Designer Lesson Review
Asymmetric CrypAtoDgOra.NpEhTy EntitOy uFtrpaumtework Lesson 2: Reading and Writing Data
Hashing and SaltiBnegfore You BegMinanaging Your DbCommand Object
Digital SignaturesLesson 1: WhaDt Iast tahbea sAeD COo.nNnEeTc Etinotnit y DbDataReader Object
Encrypting ConneFcratimonesw aonrkd? Coannfidg uCroanttioenxt F Uilseisng Using Multiple Active Result Sets (MARS) to Execute Multiple Commands on a Connection
Principle of LeastE Pnrtiivtiyle FgreamewoDrakt aACrcohnitteexctture Performing Bulk Copy Operations with a SqlBulkCopy Object
Lesson SummaryOverview Lesson Summary DbDataAdapter Object
Lesson Review Entity FramewoLerkss vosn. LRIeNvQie wto SQL DbProviderFactory Classes
Lesson 4: SynchroMniozdinegli nDga tDaataLesson 2: Executing Using DbException to Catch Provider Exceptions
The Microsoft SynMca Fnraagminegw oyorkuQr Dueartiaebs aUsesi nCgo nLnINeQct itoon Working with SQL Server User-Defined Types (UDTs)
Lesson Summaryand Context USsiQnLg ObjectContext Lesson Summary
Lesson Review More ModelinBga asnicd Q Dueesrigy nwith Filter Lesson Review
Case Scenario Implementing aInnhde Sroitratnce in the Entity Lesson 3: Working with Transactions
Case Scenario: DaFtraa mSyenwcohrroknizPatrioojnections What Is a Transaction?
Suggested PracticPeOsCO EntitiesInner Joins Concurrency Models and Database Locking
Take a Practice TeLsetsson SummaOryuter Joins Transaction Isolation Levels
CHAPTER 9 Lesson ReviewGrouping and Single Transactions and Distributed Transactions
Deploying Your ALpepssliocnat 2io: nQuerAyginggr eagnadt iUonpdating with Creating a Transaction
Before You Beginthe Entity FramPeawgionrgk Introducing the System.Transactions Namespace
Lesson 1: DeployiUnsgin Ygo uLIrN AQp ptoli cLEaentstisoiotnine sS tuom Qmuaerryy Your Working with Distributed Transactions
Packaging and PuDbaltisahbiansge fromL Veisssuoanl SRteuvdieiow .NET Viewing Distributed Transactions
Deploying WCF DInattrao Sdeurcviincge sE AnLpteiptsylsi ocSanQt Li3o: nSsubmitting Lesson Summary
Deployment for AUSsPin.NgE OT bWjeecbtCsioCtenhstaenxgt etos tSou tbhme iDt atabaseLesson Review
Silverlight ConsidCehraatniogness to theU Dsinatga bDaasteaContext Case Scenarios
Deploying Entity LFerassmoenw Sourmk mMaetrotya Tdraatcak Changes and Case Scenario 1: Clustered Servers and Connection Pooling
Lesson SummaryLesson ReviewCache Objects Case Scenario 2: The Daily Imports
Lesson Review Case ScenariosThe Life Cycle of an Suggested Practices
Case Scenario Case Scenario E1n: tCithyoosing an Object- Take a Practice Test
Case Scenario: DaRteal aStyinocnharl oMnaizpMaptioeordnifying Existing
Suggested Practices
Take a Practice Test
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2011 by Glenn Johnson
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: 2011927329
ISBN: 978-0-7356-2739-0
Printed and bound in the United States of America.
Microsoft Press books are available through booksellers and distributors worldwide. If you need support related
to this book, email Microsoft Press Book Support at [e-mail]. Please tell us what you think of
this book at http://www.microsoft.com/learning/booksurvey.
Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty
/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of
their respective owners.
The example companies, organizations, products, domain names, email addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain name,
email 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: Martin Del Re
Developmental Editor: Karen Szall
Project Editor: Valerie Woolley
Editorial Production: nSight, Inc.
Technical Reviewer: Christophe Nasarre; Technical Review services provided by Content Master, a member of
CM Group, Ltd.
Copyeditor: Kerin Forsyth
Indexer: Luci Haskins
Cover: Twist Creative • Seattle
Exam 70-516: TS: A ccessing Data with
Microsoft .NET Framework 4
OBJECTIVE CHAPTER LESSON
MODELING DATA (20%)
Map entities and relationships by using the Entity Data Model. Chapter 6 Lesson 1
Map entities and relationships by using LINQ to SQL. Chapter 4 Lesson 1
Create and customize entity objects. Chapter 6 Lesson 1
Connect a POCO model to the entity Framework. Chapter 6 Lesson 1
Create the database from the Entity Framework model. Chapter 6 Lesson 1
Create model-defined functions. Chapter 6 Lesson 1
MANAGING CONNECTIONS AND CONTEXT (18%)
Configure connection strings and providers. Chapter 2 Lesson 1
Create and manage a data connection. Chapter 2 Lesson 1
Secure a connection. Chapter 2 Lesson 1
Manage the DataContext and ObjectContext. Chapter 4 Lesson 1
Chapter 6 Lesson 1
Implement eager loading. Chapter 4 Lesson 1
Chapter 6 Lesson 1
Chapter 7 Lesson 1
Cache data. Chapter 1 Lesson 1
Chapter 4 Lesson 3
Configure ADO.NET Data Services. Chapter 7 Lesson 1, 2
QUERYING DATA (22%)
Execute a SQL query. Chapter 2 Lesson 2
Create a LINQ query. Chapter 3 Lesson 1, 2
Chapter 4 Lesson 2
Create an Entity SQL (ESQL) query. Chapter 3 Lesson 1, 2
Chapter 4 Lesson 2
Chapter 6 Lesson 2
Handle special data types. Chapter 1 Lesson 2
Chapter 2 Lesson 2
Query XML. Chapter 5 Lesson 1, 2, 3
Query data by using ADO.NET Data Services. Chapter 7 Lesson 1
MANIPULATING DATA (22%)
Create, update, or delete data by using SQL statements. Chapter 2 Lesson 2
Create, update, or delete data by using DataContext. Chapter 4 Lesson 3
Create, update, or delete data by using ObjectContext. Chapter 6 Lesson 2
Manage transactions. Chapter 2 Lesson 3
Chapter 6 Lesson 2
Create disconnected objects. Chapter 1 Lesson 1
DEVELOPING AND DEPLOYING RELIABLE APPLICATIONS (18%)
Monitor and collect performance data. Chapter 8 Lesson 1
Handle exceptions. Chapter 8 Lesson 2
Protect data. Chapter 8 Lesson 3
Synchronize data. Chapter 2 Lesson 3
Chapter 8 Lesson 4
Deploy ADO.NET components. Chapter 9 Lesson 1
Exam Objectives The exam objectives listed here are current as of this book’s publication date. Exam objectives
are subject to change at any time without prior notice and at Microsoft’s sole discretion. Please visit the Microsoft
Learning Web site for the most current listing of exam objectives: http://www.microsoft.com/learning/en/us/Exam
.aspx?ID=70-516.
Contents at a Glance
Introduction xiii
CHAPTER 1 ADO.NET Disconnected Classes 1
CHAPTER 2 ADO.NET Connected Classes 63
CHAPTER 3 Introducing LINQ 143
CHAPTER 4 LINQ to SQL 237
CHAPTER 5 LINQ to XML 295
CHAPTER 6 ADO.NET Entity Framework 359
CHAPTER 7 WCF Data Services 459
CHAPTER 8 Developing Reliable Applications 503
CHAPTER 9 Deploying Your Application 581
Answers 601
Index 623

Contents
Introduction xiii
System Requirements .............................................xiii
Code Samples ...................................................xv
Using the CD ....................................................xv
Acknowledgments ..............................................xvii
Support & Feedback .............................................xviii
Chapter 1 ADO.NET Disconnected Classes 1
Lesson 1: Working with the DataTable and DataSet Classes .............3
The DataTable Class 4
Using DataView as a Window into a Data Table 17
Using a DataSet Object to Coordinate Work Between
Data Tables 20
Lesson 2: Serialization, Specialized Types, and Data Binding ...........34
Serializing and Deserializing the Data Table with XML Data 34
Serializing and Deserializing DataSet Objects 37
Handling Specialized Types 48
Data Binding Overview 51
Chapter 2 ADO.NET Connected Classes 63
Lesson 1: Connecting to the Data Store .............................65
Using Providers to Move Data 65
Getting Started with the DbConnection Object 66
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
Storing the Connection String in the Application
Configuration File 75
Encrypted Communications to SQL Server 76
Storing Encrypted Connection Strings in Web Applications 76
Connection Pooling 77
Lesson 2: Reading and Writing Data ................................85
DbCommand Object 85
DbDataReader Object 89
Using Multiple Active Result Sets (MARS) to Execute
Multiple Commands on a Connection 91
Performing Bulk Copy Operations with a SqlBulkCopy Object 93
DbDataAdapter Object 95
DbProviderFactory Classes 101
Using DbException to Catch Provider Exceptions 105
Working with SQL Server User-Defined Types (UDTs) 105
Lesson 3: Working with Transactions ...............................120
What Is a Transaction? 120
Concurrency Models and Database Locking 121
Transaction Isolation Levels 121
Single Transactions and Distributed Transactions 123
Creating a Transaction 123
Introducing the System.Transactions Namespace 126
Working with Distributed Transactions 130
Viewing Distributed Transactions 133
Chapter 3 Introducing LINQ 143
Lesson 1: Understanding LINQ ....................................145
A LINQ Example 145
Deferred Execution 147
LINQ Providers 149
Features That Make Up LINQ 150
Lesson 2: Using LINQ Queries .....................................205
Syntax-Based and Method-Based Queries 205
LINQ Keywords 208
Projections 210
viii Contents
Using the Let Keyword to Help with Projections 211
Specifying a Filter 211
Specifying a Sort Order 212
Paging 213
Joins 215
Grouping and Aggregation 221
Parallel LINQ (PLINQ) 223
Chapter 4 LINQ to SQL 237
Lesson 1: What Is LINQ to SQL? ...................................239
Modeling Your Data 239
Examining the Designer Output 243
Managing Your Database Connection and Context
Using DataContext 249
Lesson 2: Executing Queries Using LINQ to SQL .....................260
Basic Query with Filter and Sort 260
Projections 261
Inner Joins 262
Outer Joins 264
Grouping and Aggregation 267
Paging 268
Lesson 3: Submitting Changes to the Database .....................277
Using DataContext to Track Changes and Cache Objects 277
The Life Cycle of an Entity 278
Modifying Existing Entities 280
Adding New Entities to DataContext 282
Deleting Entities 283
Using Stored Procedures 285
Using DataContext to Submit Changes 286
Submitting Changes in a Transaction 286
Chapter 5 LINQ to XML 295
Lesson 1: Working with the XmlDocument and XmlReader Classes ....297
The XmlDocument Class 297
The XmlReader Class 306
Contents ix
Lesson 2: Querying with LINQ to XML .............................320
Introducing the XDocument Family 320
Using the XDocument Classes 328
Lesson 3: Transforming XML Using LINQ to XML ....................344
Transforming XML to Objects 344
Transforming XML to Text 347
Transforming XML to XML 348
Chapter 6 ADO.NET Entity Framework 359
Lesson 1: What Is the ADO.NET Entity Framework? ..................361
Entity Framework Architecture Overview 361
Entity Framework vs. LINQ to SQL 363
Modeling Data 365
Managing your Database Connection and Context
Using ObjectContext 376
More Modeling and Design 385
Implementing Inheritance in the Entity Framework 391
POCO Entities 407
Lesson 2: Querying and Updating with the Entity Framework .........421
Using LINQ to Entities to Query Your Database 421
Introducing Entity SQL 425
Using ObjectContext to Submit Changes to the Database 434
Chapter 7 WCF Data Services 459
Lesson 1: What Is WCF Data Services?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .461
Introducing OData 461
Creating a WCF Data Service 462
Querying Data through WCF Data Services 471
Lesson 2: Consuming WCF Data Services ...........................482
Adding a Client Application 482
Chapter 8 Developing Reliable Applications 503
Lesson 1: Monitoring and Collecting Performance Data ..............505
Implementing Instrumentation 505
x Contents