Professional ADO.NET Programming

Chia sẻ: Huy Hoang | Ngày: | Loại File: PDF | Số trang:0

Thêm vào BST

Báo xấu

83
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

ADO.NET is a large set of .NET classes that enable us to retrieve and manipulate data, and update data sources, in very many different ways. As an integral part of the .NET framework, it shares many of its features: features such as multi-language support, garbage collection, just-in-time compilation, object-oriented design, and dynamic caching, and is far more than an upgrade of previous versions of ADO.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Professional ADO.NET Programming

Summary of Contents Introduction 1 Chapter 1: Data Access and .NET 9 Chapter 2: The .NET Data Providers 45 Chapter 3: Visual Studio .NET and ADO.NET 69 Chapter 4: Using DataReaders 133 Chapter 5: The DataSet 163 Chapter 6: Using the DataAdapter 207 Chapter 7: Typed DataSets and DataSet Schemas 235 Chapter 8: XML and the DataSet 271 Chapter 9: Constraints, Relations, and Views 317 Chapter 10: Transactions 365 Chapter 11: Mapping 387 Chapter 12: Making a Data Services Component 409 Chapter 13: ADO.NET and Web Services 455 Chapter 14: SQL Server Native XML Support 517 Chapter 15: Performance and Security 551 Chapter 16: Integration and Migration 589 Chapter 17: Creating a Custom .NET Data Provider 625 Chapter 18: Case Study – Cycle Couriers 671 Index 707
Table of Contents Introduction 1 What Is ADO.NET? 1 What Does This Book Cover? 1 Who Is This Book For? 3 What You Need To Use This Book 3 Conventions 3 Customer Support 4 How to Download the Sample Code for the Book 4 Errata 4 E- mail Support 4 p2p.wrox.com 5 Why this System Offers the Best Support 6 Chapter 1: Data Access and .NET 9 The .NET Framework 9 The Common Language Runtime 10 Garbage Collection 11 The Common Language Infrastructure 11 Assemblies 11 The Common Type System 12 The Common Language Specification 12 .NET Class Libraries 12 Not Another Data Access Technology? 13 Brief History of Data Access 13 ODBC 13 DAO 13 RDO 14 OLE DB 14 ADO 14 Introduction to ADO.NET 15 Advantages of Using Managed Classes 15 Cross-Language Support 15 Cleaner Architecture 15 XML Support 15 Optimized Object Model 16
Table of Contents Architectural Overview of ADO.NET 16 .NET Data Providers 16 Data Provider Components 16 Existing Data Providers 19 The DataSet 21 The DataTable Class 23 Updating the Data Source 24 ADO.NET and XML 25 Typed DataSets 27 ADO.NET and ADO 2.6 28 Disconnected Data Access 29 Read-Only, Forward-Only Access 30 Provider-Specific Classes 31 Using ADO 2.x in .NET 34 Using ADO.NET 36 C# Example 36 Visual Basic.NET Example 36 JScript.NET Example 37 Managed C++ Example 38 J# Example 39 ADO.NET Events 40 Summary 42 Chapter 2: The .NET Data Providers 45 The SQL Server Data Provider 46 The OLE DB Data Provider 47 Meet the Players 47 Connection 48 Command 49 DataReader 49 DataAdapter 52 Establishing Connections 53 The SqlConnection and OleDbConnection Classes 53 Constructing a Connection 53 Storing Connection Strings in the Configuration File 54 Connection Events 56 Connection Pooling in the Data Providers 58 Using Commands 59 The SqlCommand and OleDbCommand Classes 59 Using a Com mand with a T-SQL Statement 59 Executing the Command 60 Using a Command with a Stored Procedure 63 Using the SqlParameter and OleDbParameter Classes 64 Summary 67 ii
Table of Contents Chapter 3: Visual Studio .NET and ADO.NET 69 Connection Classes 70 SqlConnection and OleDbConnection Data Components 70 Adding the Connection String 72 Retrieving Connection Strings Programmatically 76 Adding an Event 77 Command Data Components 79 SqlCommand and OledbCommand Data Components 80 Defining a Query Command 81 Executing a Stored Procedure 87 Data Adapter Components 91 Table Mappings 101 DataSet Data Component 103 The Typed Dataset 104 Adding a Typed Dataset to the Project 104 Relating Two or More Tables 108 Generating and Filling a Typed DataSet Object 110 Using the Techniques Acquired to Create a Web Service 112 The DataView Data Component 116 Using the DataView to View Selected Rows 117 Filtering Rows Using a Filter Expression 117 Filtering Rows on Row State 118 Using the DataView to Sort Rows 119 The DataGrid Component 120 DataGrid Web Component 120 Binding the DataGrid Component to a Data Source 120 Formatting the DataGrid 122 Sorting DataGrid Records 125 Selecting, Editing, Updating, and Deleting DataGrid Records 125 Breaking a DataGrid into Pages 128 DataGrid Window Component 129 Summary 130 Chapter 4: Using DataReaders 133 A Note About the Chapter Contents 134 The Basics of a DataReader 135 The IDataReader Interface 135 The IDataRecord Interface 135 The ADO.NET DataReaders 136 DataReader Operations 138 Creating a DataReader 139 The ExecuteReader Method 139 Creating and Using a DataReader 140 iii
Table of Contents Simple Data Retrieval With the DataReader 141 Executing Stored Procedures with a DataReader 143 Navigating Multiple Result Sets 147 Accessing the Data in a Type-Safe Manner 148 SQL Server Types 150 Getting the Result Set's Schema 151 Bringing it all Together 153 Commonly Encountered Exceptions 157 IndexOutOfBoundsException 157 InvalidOperationException 158 DataReader Performance Considerations 158 Column Ordinal versus Column Name 159 Type Safe Access versus Non-Type-Safe Access 160 Summary 161 Chapter 5: The DataSet 163 The DataTable 164 DataColumn 165 DataRow 166 Constraints 166 Primary Key 167 Dynamically Constructing a DataTable 168 DataTable Events 169 DataTable Events Example 170 Populating a DataSet 180 Constructing a DataAdapter 181 Invoking Fill 182 DataAdapter Example 183 The Tables Collection 185 Populating the Tables Collection with Multiple DataTables 185 Multiple DataSet Tables Example 186 Retrieving the Tables Collection Metadata 188 The Relations Collection 190 DataRelations Example 192 Merging DataSets 197 Merging Two DataSets 198 Merging Two DataSets and Maintaining Original Values 198 Merging Two DataSets with Different Schemas 198 Caching DataSets for Better Performance 199 Summary 204 iv
Table of Contents Chapter 6: Using the DataAdapter 207 The DataAdapter Base Class 207 DataAdapter and DataSet 208 More Details for the Fill Method 212 Using More Complex Queries 213 Filling a DataSet Object with Few Records 217 Filling a DataSet Object with Only the Schema 219 Filling a DataSet Object that Already has a Schema 222 Updating a Database 222 Using a CommandBuilder Object 223 Using SQL Commands 225 Making Updates Using Stored Procedures 228 The DataAdapter's Events 230 Summary 232 Chapter 7: Typed DataSets and DataSet Schemas 235 Overview of XSD 236 Simple Types 236 Basic Data Types 236 Attributes 238 Enumerations 239 User-Defined Types 240 Facets 240 Complex Types 241 Mixed Attribute 241 Element Groups 242 ll Element 242 choice Element 242 sequence Element 243 group Element 243 Attribute Groups 244 XSD Annotation 244 documentation Element 245 appinfo Element 245 XmlSchema Class 245 DataSet Schemas 247 Schema Translation 247 Generating Tables and Columns 248 Constraints 250 Keys 250 Unique Constraints 250 Foreign Keys (Keyrefs) and Relationships 250 Typed DataSets 254 Building Strongly Typed DataSets in Visual Studio .NET 255 Building Typed DataSets Manually 259 Strongly Typed DataSets and Relational Data 260 Typed DataSet Performance 262 v
Table of Contents Annotating Typed DataSets 262 codegen 263 typedName 263 typedPlural 263 typedParent 263 typedChildren 263 nullValue 263 msdata 264 ConstraintName 264 ConstraintOnly 264 UpdateRule 264 DeleteRule 264 PrimaryKey 264 Relationship 264 Annotated Typed DataSet Example 265 Summary 268 Chapter 8: XML and the DataSet 271 XmlDocument (W3C DOM) 272 XPath 275 Axis 276 Node Test 277 Predicate 277 DataSet Schemas 280 Schema Inference 280 Inference Rules 280 Inference Rules in Action 281 Supplied Schemas 285 Document Validation with Schemas 286 XmlValidatingReader 286 DataSets and XML Data 289 Loading XML 289 XmlReadMode 290 Writing XML 291 Fidelity Loss and DataSet Schemas 293 DataSet Marshaling 295 DataSet Serialization 295 Transferring DataSet XML Between Applications 295 Data Filtering 299 Select Method 300 Data Views 303 The DataSet and the XmlDataDocument 305 Relational Projection of DOM View via XSD 306 Relational Projection Views with a Typed DataSet 309 Using XSL and XSLT Transformations 310 Summary 314 vi
Table of Contents Chapter 9: Constraints, Relations, and Views 317 Constraints 318 Unique Constraint 319 ForeignKeyConstraint 321 Custom Constraint 326 DataRelations 333 XML and DataRelations 337 DataViews 340 Sorting 341 Filtering 343 Operators 344 Relationship Referencing 344 Aggregate Functions 345 Functions 346 Filtering on Row State 346 Editing Data in the DataView 348 DataViewManager 349 Databinding 350 Bringing it Together 352 Examples 354 Example 1 354 Example 2 357 Summary 362 Chapter 10: Transactions 365 What is a Transaction? 365 ACID Properties 366 Database Transactions 366 Transaction Vocabulary 367 ADO.NET Transaction Support 367 Transaction Class 369 Methods of the Transaction class 369 Writing Transactional Database Applications 370 Implementing Transactions 370 Running the Application 373 Examining the Effect of Isolation Level 373 What are Isolation Levels? 374 Some Related Terms 374 Possible Isolation Levels in ADO.NET 374 Changing Isolation Levels 375 When to Use Transactions 377 Transactions and Performance 378 Default Behavior for Transactions 378 Transactions and User Confirmation 378 Simultaneous ADO.NET and DBMS Transactions 379 vii
Table of Contents Advanced Techniques 379 Savepoints 379 Nested Transactions 382 Using Transactions with a DataSet and DataAdapter 382 Summary 384 Chapter 11: Mapping 387 Using the SQL AS Keyword 387 The ADO.NET Mapping Mechanism 389 Using Mapping when Retrieving Data 389 The MissingMappingAction and MissingSchemaAction Properties 392 Inserting Records Using Mapped Names 393 Web Services with Mapping 396 Creating the Supplier Database 398 Creating the Supplier Web Service 400 Creating the Pet Lovers Application 401 Summary 406 Chapter 12: Making a Data Services Component 409 Installing ODBC .NET 410 What is a Data Service Component and Why Use it? 411 What is the Data Servic e Component? 411 What are the Benefits? 412 Creating a Data Service Component 413 The Data Service Component 413 The DataLayer Namespace – Public Enumerators 414 The ConfigSettings Class – Public Properties 415 The ConfigSettings Class – Public Constructors 416 The Commands Class – Public ExecuteQuery Method 418 The Commands Class – Public ExecuteNonQuery Method 422 The Commands Class – Private Connection Method 422 Creating an Assembly Information File 423 Compiling the Data Service Component 424 Deploying a Data Service Component 425 The Global Assembly Cache – (GAC) 425 Making a Reference to Wrox_DL in machine.config 428 Using the Data Service Component 428 Using in a ASP.NET Web Form 429 Executing SQL Text 429 Executing Stored Procedures 435 Using in a Web Service 438 viii
Table of Contents Performance and Optimization Tips 440 Object Pooling 440 Building a Hit Tracker Component 441 Transactions 448 Uninstalling the Components 452 Summary 453 Chapter 13: ADO.NET and Web Services 455 Setting Up the Code Samples 456 Web Services – The New DCOM 458 Common Standards 458 Supply and Demand – Web Service Providers and Consumers 459 Building a Basic Web Service 460 Building a Basic Consumer 465 Building an HTTP Consumer 466 Capturing the Data in an XmlDocument 469 Build a SOAP Consumer in Visual Studio .NET 471 Discovering Web Services 472 Building the Consumer Code Behind Class 477 What is a Proxy Client? 480 The WSDL.exe Utility 482 Storing a Web Service URL in a Configuration File 484 Exchanging Data in Web Services 485 Working with DataSets 486 Building a Pre-populated DataSet Derived Class 486 Building the Web Service Method 488 Building a Windows Form Consumer with Visual Studio .NET 489 Running the Windows Form Project 491 DataSets as Input Arguments 491 Building a Web Form Consum er 493 Using XML with Web Services 498 Working with Custom Classes as XML 499 Working with XML Attributes 503 Working with XML Elements and Attributes 504 Working with Multiple Custom Classes As XML 505 Web Service Security 506 Using Windows Authentication 507 Adding Credentials to a Consumer 507 Using SOAP-based Authentication 508 Building a Private Web Service 509 Building the Consumer 511 Summary 514 ix
Table of Contents Chapter 14: SQL Server Native XML Support 517 FOR XML 518 FOR XML – Optional Arguments 521 FOR XML RAW 522 Using FOR XML RAW with ADO.NET 523 FOR XML AUTO 525 FOR XML AUTO and ADO.NET 527 FOR XML EXPLICIT 529 FOR XML EXPLICIT – Two-Level Example 531 Entity Encoding 532 Directives 533 FOR XML EXPLICIT – Three- Level Example 536 FOR XML EXPLICIT – ADO.NET 539 FOR XML EXPLICIT – Conclusion 541 OPENXML 541 OPENXML Stored Procedures: Deletion and Updates 544 OPENXML ADO.NET: Insertion, Deletion, and Updates 545 Summary 547 Chapter 15: Performance and Security 551 Optimizing Data Access 551 DataReader or DataSet? 552 Memory Consumption 552 Traversal Direction 552 Multiple Result Sets 553 Round Trips 553 Stored Procedures 554 Compiled Query Caching 555 Configuring DataAdapter Commands 555 High-Volume Data Processing 559 Latency 559 Cached Data 560 ASP.NET Object Caching 564 Birds of a Feather (Functionality Grouping) 567 Marshaling Considerations 568 DataSet Serialization 569 XML over HTTP 571 Connection Pooling 571 SqlConnection 572 OleDbConnection 572 Message Queuing 573 To Queue or Not to Queue 573 Sending Messages 574 Receiving Messages 575 x
Table of Contents Security Concerns 576 Code Access Security 576 Administration 577 Code Groups 577 Permission Sets 578 Permissions 578 CAS in Action 579 SSL 583 Encryption 584 Summary 586 Chapter 16: Integration and Migration 589 InterOp 590 COM InterOp and the RCW 590 Accessing ADO from .NET 590 Whether to Access ADO from .NET 590 Accessing ADO from .NET 591 Platform Invocation Services (PInvoke) 594 Migration 595 ADO Data Types 596 Migrating Connections 597 Migrating the Recordset 599 Forward- Only Data Access 600 Publishing RecordSet Changes 603 Migrating Commands and Stored Procedures 609 Changes in XML Persistence 615 Handling Exceptions and Errors 618 Streams 620 Summary 622 Chapter 17: Creating a Custom .NET Data Provider 625 Data Provider Library 626 Application Requirements 626 Retail Store 626 E-Commerce Site 627 Telephone Sales 627 Architecture and Design 627 Distributed Order Entry System 628 The Order Class and Schema 629 A Sample Order 629 Implementing the Data Provider Assembly 630 The OQProvider Namespace 630 The OrderObject 631 The OrderItem 634 xi
Table of Contents An MSMQ Review 636 Sending Messages 636 Receiving Messages 638 The OQConnection 638 The OQCommand 643 The OQParameterCollection and OQParameter 648 The OQDataReader 652 The OQDataAdapter 657 The OQException 663 Utilizing the Custom Data Provider 663 A Retail Store Interface 664 An E-Commerce Web Site Interface 665 The Telephone Sales Interface 667 Summary 669 Chapter 18: Case Study – Cycle Couriers 671 Requirements 673 Customer 674 Recipient (Addressee) 674 Cyclist 674 Call Center Operator 675 Design 675 User Interface Layer 676 The Customers View 677 The Cyclist View 679 The Call Center Operator View 679 Business Layer 681 Customer 681 Package 681 Cyclist 681 User Authentication 682 Package Status Change 682 Data layer 682 Implementation 683 Database Detail 683 Customers 683 Cyclists Table 684 CyclistStates Table 684 Packages Table 684 Relationships 686 Class Description 686 ServiceCustomer 687 ServicePackage 689 ServiceCyclist 692 Web Interface classes 693 Call Center Operator Application 696 Hardware Configuration 699 xii
Table of Contents How to Deploy the System 700 Installing the Web Application and Web Service 700 Installing the Client – Call Center Application. 702 How to Build the System 703 Summary 704 Index 707 xiii
Table of Contents xiv
Introduction What is ADO.NET? ADO.NET is a large set of .NET classes that enable us to retrieve and manipulate data, and update data sources, in very many different ways. As an integral part of the .NET framework, it shares many of its features: features such as multi-language support, garbage collection, just-in-time compilation, object -oriented design, and dynamic caching, and is far more than an upgrade of previous versions of ADO. ADO.NET is set to become a core component of any data-driven .NET application or Web Service, and understanding its power will be essential to anyone wishing to utilize .NET data support to maximum effect. What Does This Book Cover? This book provides a thorough investigation of the ADO.NET classes (those included in the System.Data, System.Data.Common, System.Data.OleDb, System.Data.SqlClient, and System.Data.Odbc namespaces). We adopt a practical, solutions-oriented approach, looking at how to effectively utilize the various components of ADO.NET within data-centric application development. We begin our journey in Chapter 1 by looking at a brief history of data access in general, then looking more closely at ADO.NET itself. This includes looking at some of the features and comparing it to ADO 2.6. This theme continues into Chapter 2, which looks at the .NET data providers, which provide connectivity to a variety of data stores.
Introduction Chapter 3 moves on to delving into Visual Studio .NET and how this graphical user interface makes using ADO.NET intuitive and easy to handle. The chapter includes a number of examples to demonstrate the principles learned. Now that we are a little more comfortable with ADO.NET, we can begin to delve deeper into the specifics of the technology. Chapter 4 looks at the DataReader: what it is, why you wou ld use it and also how you would use it in a number of situations. This in -depth look continues in Chapter 5 , where we learn about the DataSet, while Chapter 6 introduces and explores the DataAdapter. Chapter 7 takes a closer look at the DataSet, which enables us to work with data while disconnected from the data source; this includes an introduction to how the XML Schema Definition (XSD) language is useful when manipulating DataSets. This leads us nicely into Chapter 8, where we explore the use of XML with the DataSet, covering various issues such as data marshalling and data filtering, amongst others. Chapter 9 continues the look at the DataSet by examining constraints, relations and views, all of which influence the way that data is presented and manipulated. The chapter introduces the DataView and includes some examples. Chapter 10 moves on to look at the topic of transactions, an important item in the business world where either all the operations must succeed, or all of them must fail. The chapter examines, amongst other things, isolation levels and their impact, performance, and advanced techniques. The concept of mapping is explored in Chapter 11: this is where we can give our own names to unintuitive column headings in order to understand the material better. Chapter 12 looks at creating our own Data Services component: the benefits, the creation and deployment, and using it once it exists. The chapter also looks at tips for better performance of data service components. This leads well into Chapter 13, where we look at ADO.NET and Web Services, in particular exchanging data, using XML, and security. Chapter 14 looks again at the issue of XML, this time showing how SQL Server 2000 has native support for this cross-platform standard of data retrieval. The chapter is example -based, showing all the native XML options at every step. Chapter 15 moves off into the more theoretical realm of performance and security. Both are important considerations if we will be dealing with thousands of data access demands every minute. The chapter covers many ways to increase performance and tighten security. Chapter 16 discusses integration and migration, particularly accessing ADO from .NET and how to handle the migration from ADO to ADO.NET. Chapter 17 allows us to create our own custom .NET data provider. It goes through the whole process: why we need our own provider, the architecture and design, and the actual implementation. The chapter also shows a number of ways that we can utilize our custom provider. The same method is employed by Chapter 18, which finishes the book by building a case study that uses ADO.NET in the middle layer of a multi-tier system that tracks packages for a fictional inner city bicycle courier company. 2
Introduction Who is This Book For? This book is aimed at experienced developers, who already have some experience of developing or experimenting within the .NET framework, with either C# or Visual Basic .NET. We do not cover the basics of C# or Visual Basic .NET, and assume some prior experience of Microsoft data access technologies. What You Need to Use This Book To run the samples in this book you need to have the following: q Windows 2000 or Windows XP q The .NET Framework SDK. The code in this book will not work with .NET Beta 1. The complete source code for the samples is available for download from our web site at http://www.wrox.com/Books/Book_Details.asp?isbn=186100527X. Conventions We've used a number of different styles of text and layout in this book to help differentiate between the different kinds of information. Here are examples of the styles we used and an explanation of what they mean. Code has several fonts. If it's a word that we're talking about in the text –for example, when discussing a For...Next loop, it's in this font. If it's a block of code that can be typed as a program and run, then it's also in a gray box: Sometimes we'll see code in a mixture of styles, like this: Widget $10.00 In cases like this, the code with a white background is code we are already familiar with; the line highlighted in gray is a new addition to the code since we last looked at it. Advice, hints, and background information comes in this type of font. Important pieces of information come in boxes like this. 3
Introduction Bullets appear indented, with each new bullet marked as follows: q Important Words are in a bold type font q Words that appear on the screen, or in menus like the File or Window menu, are in a similar font to the one you would see on a Windows desktop q Keys that you press on the keyboard, like Ctrl and Enter, are in italics Customer Support We always value hearing from our readers, and we want to know what you think about this book: what you liked, what you didn't like, and what you think we can do better next time. You can send us your comments, either by returning the reply card in the back of the book, or by e-mail to feedback@wrox.com . Please be sure to mention the book title in your message. How to Download the Sample Code for the Book When you visit the Wrox site, http://www.wrox.com/, simply locate the title through our Search facility or by using one of the title lists. Click on Download in the Code column, or on Download Code on the book's detail page. The files that are available for download from our site have been archived using WinZip. When you have saved the attachments to a folder on your hard -drive, you need to extract the files using a de-compression program such as WinZip or PKUnzip. When you extract the files, the code is usually extracted into chapter folders. When you start the extraction process, ensure your software (WinZip, PKUnzip, etc.) is set to use folder names. Errata We've made every effort to make sure that there are no errors in the text or in the code. However, no one is perfect and mistakes do occur. If you find an error in one of our books, like a spelling mistake or a faulty piece of code, we would be very grateful for feedback. By sending in errata you may save another reader hours of frustration, and of course, you will be helping us provide even higher quality information. Simply e-mail the information to support@wrox.com; your information will be checked and if correct, posted to the errata page for that title, or used in subsequent editions of the book. To find errata on the web site, go to http://www.wrox.com/, and simply locate the title through our Advanced Search or title list. Click on the Book Errata link, which is below the cover graphic on the book's detail page. E-Mail Support If you wish to directly query a problem in the book with an expert who knows the book in detail then e-mail support@wrox.com , with the title of the book and the last four numbers of the ISBN in the subject field of the e-mail. A typical e-mail should include the following things: 4