ADO.NET Using C# - ITCourseware

1 Object Innovations Courses 4120 and 4121 Student Guide Revision Using C# Rev. Copyright 2010 Object Innovations Enterprises, LLC ii All Rights Reserved Using C# for Web Applications Using C# Rev. This student guide is for these two Object Innovations courses: 4120 Using C# 4121 for Web Applications Using C# There is a separate lab manual for each course. Student Guide Information in this document is subject to change without notice. Companies, names and data used in examples herein are fictitious unless otherwise noted. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Object Innovations.

2 Product and company names mentioned herein are the trademarks or registered trademarks of their respective owners. is a registered trademark of Object Innovations. Author: Robert J. Oberg Copyright 2010 Object Innovations Enterprises, LLC All rights reserved. Object Innovations 877-558-7246 Published in the United States of America. Rev. Copyright 2010 Object Innovations Enterprises, LLC iii All Rights Reserved Table of Contents (Overview) Chapter 1 Introduction to Chapter 2 Connections Chapter 3 Commands Chapter 4 DataReaders and Connected Access Chapter 5 DataSets and Disconnected Access Chapter 6 More about DataSets Chapter 7 XML and Chapter 8 Concurrency and Transactions Chapter 9 Additional Features Chapter 10 LINQ and Entity Framework Appendix A Acme Computer Case Study Appendix B Learning Resources Rev.

3 Copyright 2010 Object Innovations Enterprises, LLC iv All Rights Reserved Directory Structure The course software installs to one of two root directories: C:\OIC\AdoCsWin contains Windows example programs (Course 4120). C:\OIC\AdoCsWeb contains Web example programs (Course 4121). Each of these directories have these subdirectories: Example programs for each chapter are in named subdirectories of chapter directories Chap01, Chap02, and so on. The Labs directory contains one subdirectory for each lab, named after the lab number. Starter code is frequently supplied, and answers are provided in the chapter directories. The Demos directory is provided for doing in-class demonstrations led by the instructor. The CaseStudy directory contains progressive steps for two case studies.

4 Data files install to the directory C:\OIC\Data. Rev. Copyright 2010 Object Innovations Enterprises, LLC v All Rights Reserved Table of Contents (Detailed) Chapter 1 Introduction to .. 1 Microsoft Data Access Technologies .. 3 ODBC .. 4 OLE DB .. 5 ActiveX Data Objects (ADO) .. 6 Accessing SQL Server before .. 7 .. 8 Architecture .. 9 .NET Data Providers .. 11 Programming with Interfaces .. 12 .NET 13 Connected Data Access .. 14 Sample Database .. 15 Example: Connecting to SQL 16 Class Libraries .. 17 Connecting to an OLE DB Data Provider .. 18 Using 19 Creating a Command 20 21 Using a Data Reader .. 22 Data Reader: Code Example .. 23 Disconnected Datasets .. 24 Data Adapters .. 25 Acme Computer Case Study .. 26 Buy Computer.

5 27 Model .. 28 Component .. 29 Part .. 30 32 SystemId as Identity 33 SystemDetails .. 35 StatusCode .. 36 37 Stored Procedure ..38 Lab 1 .. 39 Summary .. 40 Chapter 2 Connections .. 41 Block 43 .NET Data Providers .. 44 Rev. Copyright 2010 Object Innovations Enterprises, LLC vi All Rights Reserved Namespaces for .NET Data Providers .. 45 Basic Connection Programming .. 46 Using Interfaces .. 47 IDbConnection 48 Connection String .. 49 SQL Server Connection String .. 50 OLE DB Connection String .. 51 SQL Server Security .. 52 IDbConnection Methods .. 53 BasicConnect (Version 2) .. 54 Connection Life Cycle .. 55 BasicConnect (Version 3) .. 56 Database Application 57 Lab 2A .. 58 59 Connection 60 Pool Settings for SQL 61 Connection Demo 62 Connection Events.

6 63 Connection Event Demo .. 64 Exception Handling .. 65 Lab 66 Summary .. 67 Chapter 3 Commands .. 69 Command 71 Creating 72 Executing Commands .. 73 Sample 74 Dynamic Queries .. 76 Parameterized Queries .. 77 Parameterized Query Example .. 78 Command Types .. 79 Stored Procedures .. 80 Stored Procedure 81 Testing the Stored Procedure .. 82 Stored Procedures in 83 Batch 85 Transactions .. 86 Lab 3 .. 87 Summary .. 88 Chapter 4 DataReaders and Connected Access .. 89 91 Using a DataReader .. 92 Closing a DataReader .. 93 Rev. Copyright 2010 Object Innovations Enterprises, LLC vii All Rights Reserved IDataRecord .. 94 Type-Safe 95 Type-Safe Access Sample 97 GetOrdinal() .. 99 Null Data .. 100 Testing for ExecuteReader 102 Returning Multiple Result 103 DataReader Multiple Results Sets.

7 104 Obtaining Schema 105 Output from Sample Program .. 107 Lab 4 .. 108 Summary .. 109 Chapter 5 DataSets and Disconnected Access .. 111 DataSet .. 113 DataSet 114 Why DataSet? .. 115 DataSet 116 DataAdapter ..117 DataSet Example Program .. 118 Data Access Class .. 119 Retrieving the Data .. 120 Filling a DataSet .. 121 Accessing a 122 Updating a DataSet Scenario .. 123 Example ModelDataSet .. 124 Disconnected DataSet Example .. 125 Adding a New 126 Searching and Updating a Row .. 127 Deleting a Row .. 128 Row 129 Row State .. 130 BeginEdit and 131 DataTable Events .. 132 Updating a Database .. 133 Insert 134 Update Command .. 135 Delete Command .. 136 Exception Handling .. 137 Command Builders .. 138 Lab 5 .. 139 Summary .. 140 Chapter 6 More about DataSets.

8 141 Filtering DataSets .. 143 Rev. Copyright 2010 Object Innovations Enterprises, LLC viii All Rights Reserved Example of Filtering .. 144 PartFinder Example Code ( ) .. 145 Using a Single DataTable .. 147 Multiple Tables .. 148 DataSet 149 Multiple Table Example .. 150 Schema in the DataSet .. 153 Relations .. 154 Navigating a DataSet .. 155 Using Parent/Child Relation .. 156 Inferring 157 AddWithKey .. 158 Adding a Primary Key .. 159 TableMappings .. 160 Identity 161 Part Updater Example .. 162 Creating a Dataset 163 Manual DataSet Code .. 164 Lab 6 .. 166 Summary .. 167 Chapter 7 XML and 169 and XML .. 171 Rendering XML from a 172 XmlWriteMode .. 173 Demo: Writing XML 174 Reading XML into a 177 Demo: Reading XML Data.

9 178 DataSets and XML Schema .. 180 Demo: Writing XML 181 .. 182 Reading XML 183 XmlReadMode .. 184 Demo: Reading XML 185 Writing Data as Attributes .. 187 XML Data in 189 Typed DataSets .. 190 Table Adapter .. 191 Demo: Creating a Typed DataSet Using Visual 192 Demo: Creating a Typed DataSet .. 195 Using a Typed DataSet .. 197 Synchronizing DataSets and XML .. 198 Using 199 Windows Client Code .. 201 Web Client 202 XML Serialization .. 204 Rev. Copyright 2010 Object Innovations Enterprises, LLC ix All Rights Reserved XML Serialization Code 205 Default 206 Lab 7 .. 207 Summary .. 208 Chapter 8 Concurrency and Transactions .. 209 DataSets and 211 Demo Destructive 212 Demo Optimistic Concurrency .. 214 Handling Concurrency Violations.

10 216 Pessimistic 217 Transactions .. 218 Demo Transactions .. 219 Programming 221 Transaction 222 Using Transactions .. 223 DataBase 224 Transaction in Stored 225 Testing the Stored Procedure .. 226 Client Example .. 227 SQL Server Error .. 231 Summary .. 232 Chapter 9 Additional Features .. 233 AcmePub Database .. 235 Connected Database Access .. 236 Long Database Operations .. 238 Asynchronous Operations .. 240 Async Example 242 Multiple Active Result Sets .. 245 MARS Example Program .. 246 Bulk Copy .. 247 Bulk Copy Example .. 248 Bulk Copy Example Code .. 249 Lab 9 .. 250 Summary .. 251 Chapter 10 LINQ and Entity Framework .. 253 Language Integrated Query (LINQ) .. 255 LINQ to .. 256 Bridging Objects and 257 LINQ Demo.