ado.net 高级编码

【实例截图】

【核心代码】

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
ii
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 Command 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
Table of Contents
iii
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
Table of Contents
iv
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
Table of Contents
v
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
Table of Contents
vi
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
Table of Contents
vii
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
Table of Contents
viii
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 Service 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
Table of Contents
ix
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 Consumer 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
Table of Contents
x
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
Table of Contents
xi
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
Table of Contents
xii
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
Table of Contents
xiii
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