You are previewing ADO.NET 3.5 Cookbook, 2nd Edition.
O'Reilly logo
ADO.NET 3.5 Cookbook, 2nd Edition

Book Description

This guide is strikingly different from other books on Microsoft ADO.NET. Rather than load you down with theory, the new edition of ADO.NET 3.5 Cookbook gives you more than 200 coding solutions and best practices for real problems you're likely to face with this technology using Visual Studio 2008 and the .NET 3.5 platform. Organized to help you find the topic and specific recipe you need quickly and easily, this book is more than just a handy compilation of cut-and-paste C# code. ADO.NET 3.5 Cookbook also offers clear explanations of how and why each code solution works, and warns you of potential pitfalls so you can learn to adapt the book's problem-solving techniques to different situations. This collection of timesaving recipes covers vital topics including:

  • 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

  • Maintaining database integrity

Ideal for ADO.NET programmers at all levels, from the relatively inexperienced to the most sophisticated, this new edition covers the significant 3.5 upgrade, including new programming tools such as LINQ. ADO.NET 3.5 Cookbook offers a painless way for those of you who prefer to learn by doing when it comes to expanding your skills and productivity.

Table of Contents

  1. Special Upgrade Offer
  2. A Note Regarding Supplemental Files
  3. Preface
    1. What’s New in the Second Edition
    2. Who This Book Is For
    3. What You Need to Use This Book
    4. How This Book Is Organized
    5. What Was Left Out
    6. Conventions Used in This Book
    7. About the Code
    8. Using Code Examples
    9. Comments and Questions
    10. Safari® Books Online
    11. Acknowledgments
  4. 1. Connecting to Data
    1. 1.0. Introduction
    2. 1.1. Storing Connection Strings
    3. 1.2. Building a Connection String
    4. 1.3. Connecting to SQL Server
    5. 1.4. Connecting to a Named Instance of SQL Server
    6. 1.5. Connecting to SQL Server Using an IP Address
    7. 1.6. Connecting to SQL Server Using Integrated Security from ASP.NET
    8. 1.7. Connecting to an Oracle Database
    9. 1.8. Connecting to an OLE DB Data Source
    10. 1.9. Connecting to an ODBC Data Source
    11. 1.10. Connecting to a Microsoft Access Database
    12. 1.11. Connecting to a Password-Protected Microsoft Access Database
    13. 1.12. Connecting to a Microsoft Access Database from ASP.NET
    14. 1.13. Connecting to a Microsoft Excel Workbook
    15. 1.14. Connecting to a Text File
    16. 1.15. Changing the Database for an Open Connection
    17. 1.16. Setting Connection Pooling Options
    18. 1.17. Taking Advantage of Connection Pooling
    19. 1.18. Using Transactions with Pooled Connections
    20. 1.19. Displaying a Connection Property Dialog Box
    21. 1.20. Displaying the Data Link Properties Dialog Box
    22. 1.21. Monitoring Connections
  5. 2. Working with Disconnected Data Objects
    1. 2.0. Introduction
    2. 2.1. Creating a DataColumn and Adding It to a DataTable
    3. 2.2. Creating a DataTable and Adding It to a DataSet
    4. 2.3. Mapping Table and Column Names Between a Data Source and DataSet
    5. 2.4. Mapping .NET Data Provider Data Types to .NET Framework Data Types
    6. 2.5. Adding a Calculated Column to a DataTable
    7. 2.6. Creating a Unique Constraint
    8. 2.7. Creating Single- and Multi-Column Primary Keys
    9. 2.8. Creating an Autoincrementing Primary Key
    10. 2.9. Creating a Foreign Key Constraint
    11. 2.10. Creating a Data Relation
    12. 2.11. Building a DataSet Programmatically
    13. 2.12. Adding a Column to a Child DataTable That Displays Data from the Parent Table
    14. 2.13. Adding a Column to a Parent DataTable That Aggregates a Child Table’s Column Values
    15. 2.14. Converting Between a DataTable and a DataRow Array
    16. 2.15. Accessing Data Values in a DataRow Array
    17. 2.16. Creating a Strongly Typed DataSet
    18. 2.17. Controlling the Names Used in a Strongly Typed DataSet
    19. 2.18. Replacing Null Values in a Strongly Typed DataSet
  6. 3. Querying and Retrieving Data
    1. 3.0. Introduction
    2. 3.1. Executing a Query That Does Not Return a Result Set
    3. 3.2. Executing a Query That Returns a Single Value
    4. 3.3. Retrieving a Result Set Stream Using a DataReader
    5. 3.4. Accessing Data Values in a DataReader
    6. 3.5. Retrieving a Result Set Using a DataTable or a DataSet
    7. 3.6. Accessing Data Values in a DataTable or DataSet
    8. 3.7. Working with Data in a Strongly Typed DataSet
    9. 3.8. Working with Parent-Child Relations in a Strongly Typed DataSet
    10. 3.9. Using a DataView with a Strongly Typed DataSet
    11. 3.10. Testing Whether a Query Returns an Empty Result Set
    12. 3.11. Counting Records Meeting Criteria
    13. 3.12. Determining the Number of Records Returned in a DataReader
    14. 3.13. Executing a Query That Returns Multiple Result Sets
    15. 3.14. Retrieving Schema and Constraints for a DataSet
    16. 3.15. Retrieving Hierarchical Data
    17. 3.16. Navigating Between Parent and Child Tables in an Untyped DataSet
    18. 3.17. Executing a Parameterized Query
    19. 3.18. Retrieving Data Using a SQL Server Stored Procedure
    20. 3.19. Retrieving Multiple Result Sets Using the Oracle Provider
    21. 3.20. Passing a Null Value to a Query Parameter
    22. 3.21. Using Table-Valued Types As Parameters
    23. 3.22. Retrieving a Return Value from a Stored Procedure
    24. 3.23. Retrieving a Stored Procedure Output Parameter
    25. 3.24. Raising and Handling Stored Procedure Errors
    26. 3.25. Executing a SQL Server Scalar-Valued Function
    27. 3.26. Executing a SQL Server Table-Valued Function
    28. 3.27. Querying a DataSet Using LINQ
    29. 3.28. Querying a SQL Server Database Using LINQ
    30. 3.29. Retrieving Data from a Text File
    31. 3.30. Retrieving Data from a Microsoft Excel Workbook
    32. 3.31. Querying Data Asynchronously with Message Queuing
  7. 4. Searching and Analyzing Data
    1. 4.0. Introduction
    2. 4.1. Determining the Differences in Data Between Two DataSet Objects
    3. 4.2. Combining Data from Heterogeneous Data Sources
    4. 4.3. Filtering Rows in a DataTable or DataView
    5. 4.4. Finding Rows in a DataTable or DataView
    6. 4.5. Filtering and Sorting Data in a DataTable
    7. 4.6. Filtering Null Field Values in a DataTable
    8. 4.7. Accessing Deleted Rows in a DataTable
    9. 4.8. Selecting the Top N Rows in a DataTable
    10. 4.9. Executing Queries That Use COMPUTE BY
    11. 4.10. Using the Shape Language to Retrieve Hierarchical Data
    12. 4.11. Retrieving a Random Sample of Records
    13. 4.12. Using a Common Table Expression (CTE)
    14. 4.13. Executing a Recursive Query
    15. 4.14. Retrieving a Ranked Result Set
    16. 4.15. Retrieving a Pivot and Unpivot Table
    17. 4.16. Invoking a Function for Each Row in a Result Set
  8. 5. Adding and Modifying Data
    1. 5.0. Introduction
    2. 5.1. Using Autoincrementing Columns Without Causing Conflicts
    3. 5.2. Getting an Identity Column Value from SQL Server
    4. 5.3. Getting an AutoNumber Value from Microsoft Access
    5. 5.4. Getting a Sequence Value from Oracle
    6. 5.5. Modifying Data in a Microsoft Excel Workbook
    7. 5.6. Modifying Data in a Text File
    8. 5.7. Retrieving Update Errors
    9. 5.8. Adding Parent/Child Rows with Autoincrementing Keys
    10. 5.9. Adding Records with a GUID Primary Key
    11. 5.10. Inserting Multiple Rows into a Database Table Using T-SQL Row Constructors
    12. 5.11. Updating a Data Source with Data from a Different Data Source
    13. 5.12. Updating a Primary Key Value
    14. 5.13. Getting Stored Procedure Parameter Information at Runtime
    15. 5.14. Updating a DataSet with a Many-to-Many Relationship
    16. 5.15. Updating Data Asynchronously Using Message Queuing
    17. 5.16. Controlling Edits, Deletions, or Additions to Data with a DataView
    18. 5.17. Overcoming Keyword Conflicts When Using a CommandBuilder
    19. 5.18. Capturing Changes to Data in a SQL Server Database
  9. 6. Copying and Transferring Data
    1. 6.0. Introduction
    2. 6.1. Copying Rows from One DataTable to Another
    3. 6.2. Copying Tables from One DataSet to Another
    4. 6.3. Converting a DataReader to a DataTable
    5. 6.4. Converting a DataReader to a DataSet
    6. 6.5. Converting a DataTable to a DataReader
    7. 6.6. Converting a DataSet to a DataReader
    8. 6.7. Serializing Data
    9. 6.8. Deserializing Data
    10. 6.9. Merging Data in Two DataSet Objects
    11. 6.10. Merging Data in Two Database Tables
    12. 6.11. Encrypting a DataSet
    13. 6.12. Securing Login Credentials
    14. 6.13. Exporting the Results of a Query As a String
    15. 6.14. Exporting the Results of a Query to an Array
    16. 6.15. Loading an ADO Recordset into a DataSet
    17. 6.16. Converting a DataSet to an ADO Recordset
  10. 7. Maintaining Database Integrity
    1. 7.0. Introduction
    2. 7.1. Using Distributed Transactions
    3. 7.2. Using Manual Transactions
    4. 7.3. Nesting Manual Transactions with the SQL Server .NET Data Provider
    5. 7.4. Using ADO.NET and SQL Server DBMS Transactions Together
    6. 7.5. Using a Transaction with a DataAdapter
    7. 7.6. Avoiding Referential Integrity Problems when Updating Data in Related Tables
    8. 7.7. Enforcing Business Rules with Column Expressions
    9. 7.8. Retrieving Constraints from a SQL Server Database
    10. 7.9. Checking for Concurrency Violations
    11. 7.10. Resolving Data Conflicts
    12. 7.11. Using Transaction Isolation Levels to Protect Data
    13. 7.12. Specifying Locking Hints in a SQL Server Database
  11. 8. Programmatically Working with Data in .NET Windows and Web Forms User Interfaces
    1. 8.0. Introduction
    2. 8.1. Loading Data into and Binding a Field to a Web Forms Control
    3. 8.2. Binding Data to a Web Forms Control
    4. 8.3. Binding Data to a Web Forms DetailsView Control
    5. 8.4. Binding Data to a Web Forms Repeater Control
    6. 8.5. Binding Data to a Web Forms DataList Control
    7. 8.6. Binding Data to a Web Forms GridView Control
    8. 8.7. Modifying and Updating Data in a Web Forms GridView Control
    9. 8.8. Binding Data to a Web Forms FormView Control
    10. 8.9. Synchronizing Master-Detail Data in a Web Forms Application
    11. 8.10. Displaying an Image from a Database in a Web Forms Control
    12. 8.11. Localizing Client-Side Data in a Web Forms Application
    13. 8.12. Loading Data into and Binding a Field to a Windows Forms Control
    14. 8.13. Binding Data to a Windows Forms Control
    15. 8.14. Binding Data to a Windows Forms DataGridView Control
    16. 8.15. Modifying and Updating Data in a Windows Forms DataGridView Control
    17. 8.16. Using Windows Forms BindingNavigator and BindingSource Controls
    18. 8.17. Synchronizing Master-Detail Data in a Windows Forms Application
    19. 8.18. Displaying an Image from a Database in a Windows Forms Control
    20. 8.19. Binding a Group of Radio Buttons to a Windows Forms Data Field
    21. 8.20. Searching a Windows Forms DataGridView Control
  12. 9. Working with XML Data
    1. 9.0. Introduction
    2. 9.1. Using an XML File to Save and Load a DataSet or a DataTable
    3. 9.2. Using XSD Schema Files to Save and Load a DataSet Structure
    4. 9.3. Formatting Column Values When Saving Data As XML
    5. 9.4. Creating an XML File That Shows Changes Made to a DataSet
    6. 9.5. Synchronizing a DataSet and an XML Document
    7. 9.6. Storing and Retrieving XML with a Non-XML Data Type Column
    8. 9.7. Working with a SQL Server XML Data Type Column
    9. 9.8. Using an XPath Query to Retrieve Data
    10. 9.9. Reading XML Data Directly from SQL Server
    11. 9.10. Transforming a DataSet Using XSLT
    12. 9.11. Filling a DataSet Using an XML Template Query
    13. 9.12. Using OpenXML to Update Multiple Changes to SQL Server
  13. 10. Optimizing .NET Data Access
    1. 10.0. Introduction
    2. 10.1. Executing Multiple Commands on a Single Connection
    3. 10.2. Executing a SQL Statement Asynchronously
    4. 10.3. Executing Simultaneous SQL Statements Asynchronously
    5. 10.4. Creating a DataReader Asynchronously
    6. 10.5. Filling a DataSet Asynchronously
    7. 10.6. Canceling an Asynchronous Query
    8. 10.7. Caching Data
    9. 10.8. Improving Paging Performance
    10. 10.9. XML Bulk Loading with SQL Server
    11. 10.10. Bulk Copying with SQL Server
    12. 10.11. Improving DataReader Performance with Typed Accessors
    13. 10.12. Improving DataReader Performance with Column Ordinals
    14. 10.13. Debugging a SQL Server Stored Procedure
    15. 10.14. Improving Performance While Filling a DataSet
    16. 10.15. Reading and Writing Large-Value Data with SQL Server
    17. 10.16. Reading and Writing a SQL Server User-Defined Type (UDT)
    18. 10.17. Reading and Writing Oracle Large Data
    19. 10.18. Performing Batch Updates with a DataAdapter
    20. 10.19. Automatically Refreshing a DataTable Periodically
    21. 10.20. Automatically Refreshing a DataTable When Underlying Data Changes
    22. 10.21. Retrieving SQL Server Runtime Statistics
    23. 10.22. Writing Provider- and Database-Independent Code
  14. 11. Enumerating and Maintaining Database Objects
    1. 11.0. Introduction
    2. 11.1. Enumerating SQL Servers
    3. 11.2. Retrieving Database Metadata
    4. 11.3. Retrieving Database Schema Information from SQL Server
    5. 11.4. Retrieving Column Default Values from SQL Server
    6. 11.5. Determining the Length of Columns in a SQL Server Table
    7. 11.6. Retrieving a SQL Server Query Plan
    8. 11.7. Retrieving SQL Server Column Metadata Without Returning Data
    9. 11.8. Creating a New SQL Server Database
    10. 11.9. Adding a Table to a SQL Server Database
    11. 11.10. Creating a Database Table from a DataTable Schema
    12. 11.11. Creating DataSet Relationships from SQL Server Relationships
    13. 11.12. Creating a New Microsoft Access Database
    14. 11.13. Listing Tables in an Access Database
    15. 11.14. Enumerating .NET Data Providers
    16. 11.15. Enumerating OLE DB Providers
    17. 11.16. Enumerating ODBC Drivers
    18. 11.17. Changing a SQL Server User Password
  15. 12. SQL Server CLR Integration
    1. 12.0. Introduction
    2. 12.1. Creating a Stored Procedure
    3. 12.2. Creating a Scalar-Valued Function
    4. 12.3. Creating a Table-Valued Function
    5. 12.4. Creating an Aggregate Function
    6. 12.5. Creating a User-Defined Type
    7. 12.6. Creating a DML Trigger
    8. 12.7. Creating a DDL Trigger
  16. A. What’s New in ADO.NET Since Version 1.0
    1. A.1. ADO.NET 2.0
    2. A.2. ADO.NET 3.5
  17. About the Author
  18. Colophon
  19. Special Upgrade Offer
  20. Copyright