Cover image for ADO.NET cookbook
ADO.NET cookbook
Hamilton, Bill.
Personal Author:
First edition.
Publication Information:
Beijing ; Farnham : O'Reilly, [2003]

Physical Description:
xvii, 605 pages ; 24 cm
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D26 H36 2003 Adult Non-Fiction Central Closed Stacks

On Order



Designed in the highly regarded O'Reilly Cookbook format, ADO.NET Cookbook is strikingly different from other books on the subject. It isn't bogged down with pages of didactic theory. The ADO.NET Cookbook focuses exclusively on providing developers with easy-to-find coding solutions to real problems. ADO.NET Cookbook is a comprehensive collection of over 150 solutions and best practices for everyday dilemmas. For each problem addressed in the book, there's a solution--a short, focused piece of code that programmers can insert directly into their applications.And ADO.NET Cookbook is more than just a handy compilation of cut-and-paste C# and VB.NET code. ADO.NET Cookbook offers clear explanations of how and why the code works, warns of potential pitfalls, and directs you to sources of additional information, so you can learn to adapt the problem-solving techniques to different situations. This is a painless way for developers who prefer to learn by doing to expand their skills and productivity, while solving the pressing problems they face every day.These time-saving recipes include vital topics like connecting to data, retrieving and managing data, transforming and analyzing data, modifying data, binding data to .NET user interfaces, optimizing .NET data access, enumerating and maintaining database objects, and maintaining database integrity.The diverse solutions presented here will prove invaluable over and over again, for ADO.NET programmers at all levels, from the relatively inexperienced to the most sophisticated.

Author Notes

Bill Hamilton is a software architect specializing in designing, developing and implementing distributed applications using .NET and J2EE technologies. Over the last ten years, he has provided consulting services in B2B, B2C, B2E, data integration, and portal initiatives for banking, retail, accounting, manufacturing, and financial services. An early technology adopter, he frequently evaluates, recommends, and helps his clients use new technologies effectively. Bill has designed and helped build several award winning software packages. Bill is the co-author of O'Reilly's ADO.NET in a Nutshell and author of ADO.NET Cookbook.

Table of Contents

Prefacep. ix
1. Connecting to Datap. 1
1.1 Connecting to an ODBC Data Sourcep. 4
1.2 Connecting to a Microsoft Excel Workbookp. 6
1.3 Connecting to a Password-Protected Access Databasep. 9
1.4 Connecting to a Secured Access Databasep. 11
1.5 Connecting to an Access Database from ASP.NETp. 13
1.6 Using an IP Address to Connect to SQL Serverp. 14
1.7 Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)p. 17
1.8 Connecting to SQL Server Using Integrated Security from ASP.NETp. 19
1.9 Connecting to an Oracle Databasep. 20
1.10 Connecting to Exchange or Outlookp. 24
1.11 Writing Database-Independent Codep. 26
1.12 Storing Connection Stringsp. 28
1.13 Using the Data Link Properties Dialog Boxp. 33
1.14 Monitoring Connectionsp. 34
1.15 Taking Advantage of Connection Poolingp. 37
1.16 Setting Connection Pooling Optionsp. 40
1.17 Using Transactions with Pooled Connectionsp. 45
1.18 Changing the Database for an Open Connectionp. 45
1.19 Connecting to a Text Filep. 47
2. Retrieving and Managing Datap. 51
2.1 Retrieving Hierarchical Data into a DataSetp. 53
2.2 Building a DataSet Programmaticallyp. 56
2.3 Creating a Strongly Typed DataSetp. 59
2.4 Processing a Batch SQL Statementp. 63
2.5 Using a Web Service as a Data Sourcep. 67
2.6 Accessing Deleted Rows in a DataTablep. 70
2.7 Counting Records in a DataReaderp. 74
2.8 Mapping .NET Data Provider Data Types to .NET Framework Data Typesp. 76
2.9 Returning an Output Parameter Using a DataReaderp. 82
2.10 Raising and Handling Stored Procedure Errorsp. 84
2.11 Testing for No Recordsp. 87
2.12 Retrieving Stored Procedure Return Values Using a DataReaderp. 89
2.13 Executing SQL Server User-Defined Scalar Functionsp. 92
2.14 Passing Null Values to Parametersp. 93
2.15 Retrieving Update Errorsp. 96
2.16 Mapping Table and Column Names Between the Data Source and DataSetp. 100
2.17 Displaying Columns from a Related DataTablep. 102
2.18 Controlling the Names Used in a Strongly Typed DataSetp. 104
2.19 Replacing Null Values in a Strongly Typed DataSetp. 108
2.20 Retrieving Data from an Oracle Packagep. 111
2.21 Using Parameterized SQL Statementsp. 115
2.22 Querying Data Asynchronously with Message Queuingp. 117
3. Searching and Analyzing Datap. 122
3.1 Filtering and Sorting Datap. 123
3.2 Using Expression Columns to Display Calculated Valuesp. 126
3.3 Determining the Differences in Data Between Two DataSet Objectsp. 129
3.4 Navigating Between Parent and Child Records Using a DataRelationp. 134
3.5 Localizing Client-Side Data in a Web Forms Applicationp. 136
3.6 Combining Data in Tables from Heterogeneous Data Sourcesp. 139
3.7 Using Expression Columns to Display Aggregate Valuesp. 140
3.8 Finding Rows in a DataTablep. 142
3.9 Finding Rows in a DataViewp. 145
3.10 Selecting the Top n Rows in a DataTablep. 148
3.11 Getting Typed DataRows from DataViewsp. 151
3.12 Filtering for Null Valuesp. 153
3.13 Executing Queries That Use COMPUTE BYp. 154
3.14 Using the Shape Language to Retrieve Hierarchical Datap. 156
4. Adding and Modifying Datap. 159
4.1 Using Auto-Incrementing Columns Without Causing Conflictsp. 160
4.2 Getting an Identity Column Value from SQL Serverp. 163
4.3 Getting an AutoNumber Value from Microsoft Accessp. 168
4.4 Getting a Sequence Value from Oraclep. 171
4.5 Adding Parent/Child Rows with Auto-Incrementing Keysp. 175
4.6 Adding Records with a GUID Primary Keyp. 177
4.7 Updating a Data Source with Data from a Different Data Sourcep. 180
4.8 Updating a Primary Key Valuep. 183
4.9 Getting Stored Procedure Parameter Information at Runtimep. 186
4.10 Updating a DataSet with a Many-to-Many Relationshipp. 189
4.11 Updating Server Data Using a Web Servicep. 204
4.12 Updating Server Data Using .NET Remotingp. 209
4.13 Updating Data Asynchronously Using Message Queuingp. 216
4.14 Overcoming Keyword Conflicts When Using CommandBuildersp. 220
5. Copying and Transferring Datap. 224
5.1 Copying Rows from One DataTable to Anotherp. 225
5.2 Copying Tables from One DataSet to Anotherp. 227
5.3 Converting a DataReader to a DataSetp. 231
5.4 Serializing Datap. 235
5.5 Deserializing Datap. 239
5.6 Merging Datap. 241
5.7 Transmitting a DataSet Securelyp. 245
5.8 Transferring Login Credentials Securelyp. 255
5.9 Loading an ADO Recordset into a DataSetp. 259
5.10 Converting a DataSet to an ADO Recordsetp. 260
5.11 Exporting the Results of a Query as a Stringp. 273
5.12 Exporting the Results of a Query to an Arrayp. 275
6. Maintaining Database Integrityp. 280
6.1 Creating a Class That Participates in an Automatic Transactionp. 281
6.2 Using Manual Transactionsp. 285
6.3 Nesting Manual Transactions with the SQL Server .NET Data Providerp. 288
6.4 Using ADO.NET and SQL Server DBMS Transactions Togetherp. 291
6.5 Using a Transaction with a DataAdapterp. 295
6.6 Avoiding Referential Integrity Problems When Updating the Data Sourcep. 298
6.7 Enforcing Business Rules with Column Expressionsp. 315
6.8 Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columnsp. 322
6.9 Retrieving Constraints from a SQL Server Databasep. 326
6.10 Checking for Concurrency Violationsp. 332
6.11 Resolving Data Conflictsp. 337
6.12 Using Transaction Isolation Levels to Protect Datap. 343
6.13 Implementing Pessimistic Concurrency Without Using Database Locksp. 347
6.14 Specifying Locking Hints in a SQL Server Databasep. 358
7. Binding Data to .NET User Interfacesp. 362
7.1 Binding Simple Data to Web Forms Controlsp. 363
7.2 Binding Complex Data to Web Forms Controlsp. 365
7.3 Binding Data to a Web Forms DataListp. 367
7.4 Binding Data to a Web Forms DataGridp. 374
7.5 Editing and Updating Data in a Web Forms DataGridp. 378
7.6 Synchronizing Master-Detail Web Forms DataGridsp. 386
7.7 Displaying an Image from a Database in a Web Forms Controlp. 390
7.8 Displaying an Image from a Database in a Windows Forms Controlp. 393
7.9 Binding a Group of Radio Buttons in a Windows Formp. 396
7.10 Creating Custom Columns in a Windows Forms DataGridp. 401
7.11 Populating a Windows Forms ComboBoxp. 404
7.12 Binding a Windows DataGrid to Master-Detail Datap. 409
7.13 Loading a Windows PictureBox with Images Stored by Access as OLE Objectsp. 420
7.14 Using a DataView to Control Edits, Deletions, or Additions in Windows Formsp. 424
7.15 Adding Search Capabilities to Windows Formsp. 426
7.16 Dynamically Creating Crystal Reportsp. 429
7.17 Using ADO.NET Design-Time Features in Classes Without a GUIp. 431
8. Working with XMLp. 434
8.1 Using XSD Schema Files to Load and Save a DataSet Structurep. 435
8.2 Saving and Loading a DataSet from XMLp. 441
8.3 Synchronizing a DataSet with an XML Documentp. 450
8.4 Storing XML to a Database Fieldp. 457
8.5 Reading XML Data Directly from SQL Serverp. 460
8.6 Using XPath to Query Data in a DataSetp. 462
8.7 Transforming a DataSet Using XSLTp. 465
8.8 Creating an XML File That Shows Changes Made to a DataSetp. 468
8.9 Formatting Column Values When Outputting Data as XMLp. 472
8.10 Filling a DataSet Using an XML Template Queryp. 475
8.11 Using a Single Stored Procedure to Update Multiple Changes to a SQL Server Databasep. 481
9. Optimizing .NET Data Accessp. 487
9.1 Filling a DataSet Asynchronouslyp. 489
9.2 Canceling an Asynchronous Queryp. 492
9.3 Caching Datap. 496
9.4 Improving Paging Performancep. 500
9.5 Performing a Bulk Insert with SQL Serverp. 506
9.6 Improving DataReader Performance with Typed Accessorsp. 509
9.7 Improving DataReader Performance with Column Ordinalsp. 513
9.8 Debugging a SQL Server Stored Procedurep. 516
9.9 Improving Performance While Filling a DataSetp. 518
9.10 Retrieving a Single Value from a Queryp. 522
9.11 Reading and Writing Binary Data with SQL Serverp. 524
9.12 Reading and Writing Binary Data with Oraclep. 531
9.13 Performing Batch Updates with a DataAdapterp. 535
9.14 Refreshing a DataSet Automatically Using Extended Propertiesp. 540
10. Enumerating and Maintaining Database Objectsp. 544
10.1 Listing SQL Serversp. 545
10.2 Retrieving Database Schema Information from SQL Serverp. 547
10.3 Retrieving Column Default Values from SQL Serverp. 551
10.4 Determining the Length of Columns in a SQL Server Tablep. 553
10.5 Counting Recordsp. 556
10.6 Creating a New Access Databasep. 557
10.7 Creating a New SQL Server Databasep. 559
10.8 Adding Tables to a Databasep. 561
10.9 Getting a SQL Server Query Planp. 563
10.10 Compacting an Access Databasep. 565
10.11 Creating DataSet Relationships from SQL Server Relationshipsp. 567
10.12 Getting SQL Server Column Metadata Without Returning Datap. 573
10.13 Listing Installed OLE DB Providersp. 574
10.14 Listing Tables in an Access Databasep. 576
10.15 Creating a Table in the Database from a Data Table Schemap. 579
10.16 Listing Installed ODBC Driversp. 583
Appendix Converting from C# to VB Syntaxp. 585
Indexp. 593