ADO.NET Cookbook

ADO.NET Cookbook

Released September 2003
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596004392

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

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.

Table of contents

  1. ADO.NET Cookbook
    1. Preface
      1. Who This Book Is For
      2. What You Need to Use This Book
      3. How This Book Is Organized
      4. What Was Left Out
      5. Conventions Used in This Book
      6. About the Code
      7. Configuration Files
        1. Web.config
          1. App.config
      8. Platform Notes
      9. Comments and Questions
      10. Acknowledgments
    2. 1. Connecting to Data
      1. Introduction
        1. ADO.NET Overview
        2. Connections, Connection Strings, and Connection Pooling
      2. 1.1. Connecting to an ODBC Data Source
        1. Problem
        2. Solution
        3. Discussion
      3. 1.2. Connecting to a Microsoft Excel Workbook
        1. Problem
        2. Solution
        3. Discussion
          1. Create table
          2. Create data
          3. Retrieve data
          4. Update data
          5. Delete data
      4. 1.3. Connecting to a Password-Protected Access Database
        1. Problem
        2. Solution
        3. Discussion
      5. 1.4. Connecting to a Secured Access Database
        1. Problem
        2. Solution
        3. Discussion
      6. 1.5. Connecting to an Access Databasefrom ASP.NET
        1. Problem
        2. Solution
        3. Discussion
          1. Configure IIS
          2. Configure the Access server
      7. 1.6. Using an IP Address to Connect to SQL Server
        1. Problem
        2. Solution
        3. Discussion
      8. 1.7. Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)
        1. Problem
        2. Solution
        3. Discussion
      9. 1.8. Connecting to SQL Server Using Integrated Security from ASP.NET
        1. Problem
        2. Solution
        3. Discussion
      10. 1.9. Connecting to an Oracle Database
        1. Problem
        2. Solution
        3. Discussion
          1. Native Oracle
          2. OLE DB
          3. ODBC
      11. 1.10. Connecting to Exchange or Outlook
        1. Problem
        2. Solution
        3. Discussion
      12. 1.11. Writing Database-Independent Code
        1. Problem
        2. Solution
        3. Discussion
      13. 1.12. Storing Connection Strings
        1. Problem
        2. Solution
        3. Discussion
          1. Hardcode in the application
          2. Application configuration file
          3. Universal data link (UDL) file
          4. Windows registry
          5. Custom file
      14. 1.13. Using the Data Link Properties Dialog Box
        1. Problem
        2. Solution
        3. Discussion
      15. 1.14. Monitoring Connections
        1. Problem
        2. Solution
        3. Discussion
          1. SQL Server
            1. SQL Server Profiler
            2. Windows Performance Monitor
          2. ODBC
      16. 1.15. Taking Advantage of Connection Pooling
        1. Problem
        2. Solution
        3. Discussion
          1. SQL Server and Oracle
          2. OLE DB
          3. ODBC
      17. 1.16. Setting Connection Pooling Options
        1. Problem
        2. Solution
        3. Discussion
          1. SQL Server
          2. Oracle
          3. OLE DB
          4. ODBC
      18. 1.17. Using Transactions with Pooled Connections
        1. Problem
        2. Solution
        3. Discussion
      19. 1.18. Changing the Database for an Open Connection
        1. Problem
        2. Solution
        3. Discussion
      20. 1.19. Connecting to a Text File
        1. Problem
        2. Solution
        3. Discussion
    3. 2. Retrieving and Managing Data
      1. Introduction
      2. 2.1. Retrieving Hierarchical Data into a DataSet
        1. Problem
        2. Solution
        3. Discussion
      3. 2.2. Building a DataSet Programmatically
        1. Problem
        2. Solution
        3. Discussion
      4. 2.3. Creating a Strongly Typed DataSet
        1. Problem
        2. Solution
        3. Discussion
          1. Using the Visual Studio .NET IDE to generate a typed DataSet
          2. Using the TypedDataSetGenerator class to generate a typed DataSet
          3. Using an XSD schema file to generate a typed DataSet
      5. 2.4. Processing a Batch SQL Statement
        1. Problem
        2. Solution
        3. Discussion
          1. DataSet
          2. DataReader
      6. 2.5. Using a Web Service as a Data Source
        1. Problem
        2. Solution
        3. Discussion
      7. 2.6. Accessing Deleted Rows in a DataTable
        1. Problem
        2. Solution
        3. Discussion
      8. 2.7. Counting Records in a DataReader
        1. Problem
        2. Solution
        3. Discussion
      9. 2.8. Mapping .NET Data Provider Data Types to .NET Framework Data Types
        1. Problem
        2. Solution
        3. Discussion
      10. 2.9. Returning an Output Parameter Using a DataReader
        1. Problem
        2. Solution
        3. Discussion
      11. 2.10. Raising and Handling Stored Procedure Errors
        1. Problem
        2. Solution
        3. Discussion
      12. 2.11. Testing for No Records
        1. Problem
        2. Solution
        3. Discussion
      13. 2.12. Retrieving Stored Procedure Return Values Using a DataReader
        1. Problem
        2. Solution
        3. Discussion
      14. 2.13. Executing SQL Server User-Defined Scalar Functions
        1. Problem
        2. Solution
        3. Discussion
      15. 2.14. Passing Null Values to Parameters
        1. Problem
        2. Solution
        3. Discussion
      16. 2.15. Retrieving Update Errors
        1. Problem
        2. Solution
        3. Discussion
      17. 2.16. Mapping Table and Column Names Between the Data Source and DataSet
        1. Problem
        2. Solution
        3. Discussion
      18. 2.17. Displaying Columns from a Related DataTable
        1. Problem
        2. Solution
        3. Discussion
      19. 2.18. Controlling the Names Used in a Strongly Typed DataSet
        1. Problem
        2. Solution
        3. Discussion
      20. 2.19. Replacing Null Values in a Strongly Typed DataSet
        1. Problem
        2. Solution
        3. Discussion
      21. 2.20. Retrieving Data from an Oracle Package
        1. Problem
        2. Solution
        3. Discussion
      22. 2.21. Using Parameterized SQL Statements
        1. Problem
        2. Solution
        3. Discussion
      23. 2.22. Querying Data Asynchronously with Message Queuing
        1. Problem
        2. Solution
        3. Discussion
    4. 3. Searching and Analyzing Data
      1. Introduction
      2. 3.1. Filtering and Sorting Data
        1. Problem
        2. Solution
        3. Discussion
      3. 3.2. Using Expression Columns to Display Calculated Values
        1. Problem
        2. Solution
        3. Discussion
      4. 3.3. Determining the Differences in Data Between Two DataSet Objects
        1. Problem
        2. Solution
        3. Discussion
      5. 3.4. Navigating Between Parent and Child Records Using a DataRelation
        1. Problem
        2. Solution
        3. Discussion
      6. 3.5. Localizing Client-Side Data in a Web Forms Application
        1. Problem
        2. Solution
        3. Discussion
      7. 3.6. Combining Data in Tables from Heterogeneous Data Sources
        1. Problem
        2. Solution
        3. Discussion
      8. 3.7. Using Expression Columns to Display Aggregate Values
        1. Problem
        2. Solution
        3. Discussion
      9. 3.8. Finding Rows in a DataTable
        1. Problem
        2. Solution
        3. Discussion
      10. 3.9. Finding Rows in a DataView
        1. Problem
        2. Solution
        3. Discussion
      11. 3.10. Selecting the Top n Rows in a DataTable
        1. Problem
        2. Solution
        3. Discussion
      12. 3.11. Getting Typed DataRows from DataViews
        1. Problem
        2. Solution
        3. Discussion
      13. 3.12. Filtering for Null Values
        1. Problem
        2. Solutions
        3. Discussion
      14. 3.13. Executing Queries That Use COMPUTE BY
        1. Problem
        2. Solution
        3. Discussion
      15. 3.14. Using the Shape Language to Retrieve Hierarchical Data
        1. Problem
        2. Solution
        3. Discussion
    5. 4. Adding and Modifying Data
      1. Introduction
      2. 4.1. Using Auto-Incrementing Columns Without Causing Conflicts
        1. Problem
        2. Solution
        3. Discussion
      3. 4.2. Getting an Identity Column Value from SQL Server
        1. Problem
        2. Solution
        3. Discussion
      4. 4.3. Getting an AutoNumber Value from Microsoft Access
        1. Problem
        2. Solution
        3. Discussion
      5. 4.4. Getting a Sequence Value from Oracle
        1. Problem
        2. Solution
        3. Discussion
      6. 4.5. Adding Parent/Child Rows with Auto-Incrementing Keys
        1. Problem
        2. Solution
        3. Discussion
      7. 4.6. Adding Records with a GUID Primary Key
        1. Problem
        2. Solution
        3. Discussion
      8. 4.7. Updating a Data Source with Data from a Different Data Source
        1. Problem
        2. Solution
        3. Discussion
      9. 4.8. Updating a Primary Key Value
        1. Problem
        2. Solution
        3. Discussion
      10. 4.9. Getting Stored Procedure Parameter Information at Runtime
        1. Problem
        2. Solution
        3. Discussion
          1. DeriveParameters( ) method
          2. Microsoft SQL Server System stored procedure: sp_sproc_columns
      11. 4.10. Updating a DataSet with a Many-to-Many Relationship
        1. Problem
        2. Solution
        3. Discussion
      12. 4.11. Updating Server Data Using a Web Service
        1. Problem
        2. Solution
        3. Discussion
      13. 4.12. Updating Server Data Using .NET Remoting
        1. Problem
        2. Solution
        3. Discussion
      14. 4.13. Updating Data Asynchronously Using Message Queuing
        1. Problem
        2. Solution
        3. Discussion
      15. 4.14. Overcoming Keyword Conflicts When Using CommandBuilders
        1. Problem
        2. Solution
        3. Discussion
    6. 5. Copying and Transferring Data
      1. Introduction
      2. 5.1. Copying Rows from One DataTable to Another
        1. Problem
        2. Solution
        3. Discussion
      3. 5.2. Copying Tables from One DataSet to Another
        1. Problem
        2. Solution
        3. Discussion
      4. 5.3. Converting a DataReader to a DataSet
        1. Problem
        2. Solution
        3. Discussion
      5. 5.4. Serializing Data
        1. Problem
        2. Solution
        3. Discussion
      6. 5.5. Deserializing Data
        1. Problem
        2. Solution
        3. Discussion
      7. 5.6. Merging Data
        1. Problem
        2. Solution
        3. Discussion
      8. 5.7. Transmitting a DataSet Securely
        1. Problem
        2. Solution
        3. Discussion
      9. 5.8. Transferring Login Credentials Securely
        1. Problem
        2. Solution
        3. Discussion
      10. 5.9. Loading an ADO Recordset into a DataSet
        1. Problem
        2. Solution
        3. Discussion
      11. 5.10. Converting a DataSet to an ADO Recordset
        1. Problem
        2. Solution
        3. Discussion
      12. 5.11. Exporting the Results of a Query as a String
        1. Problem
        2. Solution
        3. Discussion
          1. Parameters
      13. 5.12. Exporting the Results of a Query to an Array
        1. Problem
        2. Solution
        3. Discussion
          1. Parameters
    7. 6. Maintaining Database Integrity
      1. Introduction
      2. 6.1. Creating a Class That Participates in an Automatic Transaction
        1. Problem
        2. Solution
        3. Discussion
      3. 6.2. Using Manual Transactions
        1. Problem
        2. Solution
        3. Discussion
      4. 6.3. Nesting Manual Transactions with the SQL Server .NET Data Provider
        1. Problem
        2. Solution
        3. Discussion
      5. 6.4. Using ADO.NET and SQL Server DBMS Transactions Together
        1. Problem
        2. Solution
        3. Discussion
      6. 6.5. Using a Transaction with a DataAdapter
        1. Problem
        2. Solution
        3. Discussion
      7. 6.6. Avoiding Referential Integrity Problems When Updating the Data Source
        1. Problem
        2. Solution
        3. Discussion
      8. 6.7. Enforcing Business Rules with Column Expressions
        1. Problem
        2. Solution
        3. Discussion
      9. 6.8. Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columns
        1. Problem
        2. Solution
        3. Discussion
          1. Unique and foreign-key constraints
          2. PrimaryKey
          3. DataRelation
      10. 6.9. Retrieving Constraints from a SQL Server Database
        1. Problem
        2. Solution
        3. Discussion
          1. Primary key constraints
          2. Foreign key constraints
          3. Check constraint
          4. Information schema views used in this solution
      11. 6.10. Checking for Concurrency Violations
        1. Problem
        2. Solution
        3. Discussion
      12. 6.11. Resolving Data Conflicts
        1. Problem
        2. Solution
        3. Discussion
      13. 6.12. Using Transaction Isolation Levels to Protect Data
        1. Problem
        2. Solution
        3. Discussion
      14. 6.13. Implementing Pessimistic Concurrency Without Using Database Locks
        1. Problem
        2. Solution
        3. Discussion
      15. 6.14. Specifying Locking Hints in a SQL Server Database
        1. Problem
        2. Solution
        3. Discussion
    8. 7. Binding Data to .NET User Interfaces
      1. Introduction
      2. 7.1. Binding Simple Data to Web Forms Controls
        1. Problem
        2. Solution
        3. Discussion
      3. 7.2. Binding Complex Data to Web Forms Controls
        1. Problem
        2. Solution
        3. Discussion
      4. 7.3. Binding Data to a Web Forms DataList
        1. Problem
        2. Solution
        3. Discussion
      5. 7.4. Binding Data to a Web Forms DataGrid
        1. Problem
        2. Solution
        3. Discussion
      6. 7.5. Editing and Updating Data in a Web Forms DataGrid
        1. Problem
        2. Solution
        3. Discussion
      7. 7.6. Synchronizing Master-Detail Web Forms DataGrids
        1. Problem
        2. Solution
        3. Discussion
      8. 7.7. Displaying an Image from a Database in a Web Forms Control
        1. Problem
        2. Solution
        3. Discussion
      9. 7.8. Displaying an Image from a Database in a Windows Forms Control
        1. Problem
        2. Solution
        3. Discussion
      10. 7.9. Binding a Group of Radio Buttons in a Windows Form
        1. Problem
        2. Solution
        3. Discussion
      11. 7.10. Creating Custom Columns in a Windows Forms DataGrid
        1. Problem
        2. Solution
        3. Discussion
      12. 7.11. Populating a Windows Forms ComboBox
        1. Problem
        2. Solution
        3. Discussion
      13. 7.12. Binding a Windows DataGrid to Master-Detail Data
        1. Problem
        2. Solution
        3. Discussion
      14. 7.13. Loading a Windows PictureBox with Images Stored by Access as OLE Objects
        1. Problem
        2. Solution
        3. Discussion
      15. 7.14. Using a DataView to Control Edits, Deletions, or Additions in Windows Forms
        1. Problem
        2. Solution
        3. Discussion
      16. 7.15. Adding Search Capabilities to Windows Forms
        1. Problem
        2. Solution
        3. Discussion
      17. 7.16. Dynamically Creating Crystal Reports
        1. Problem
        2. Solution
        3. Discussion
      18. 7.17. Using ADO.NET Design-Time Features in Classes Without a GUI
        1. Problem
        2. Solution
        3. Discussion
    9. 8. Working with XML
      1. Introduction
      2. 8.1. Using XSD Schema Files to Load and Save a DataSet Structure
        1. Problem
        2. Solution
        3. Discussion
      3. 8.2. Saving and Loading a DataSet from XML
        1. Problem
        2. Solution
        3. Discussion
      4. 8.3. Synchronizing a DataSet with an XML Document
        1. Problem
        2. Solution
        3. Discussion
      5. 8.4. Storing XML to a Database Field
        1. Problem
        2. Solution
        3. Discussion
      6. 8.5. Reading XML Data Directly from SQL Server
        1. Problem
        2. Solution
        3. Discussion
      7. 8.6. Using XPath to Query Data in a DataSet
        1. Problem
        2. Solution
        3. Discussion
      8. 8.7. Transforming a DataSet Using XSLT
        1. Problem
        2. Solution
        3. Discussion
      9. 8.8. Creating an XML File That Shows Changes Made to a DataSet
        1. Problem
        2. Solution
        3. Discussion
      10. 8.9. Formatting Column Values When Outputting Data as XML
        1. Problem
        2. Solution
        3. Discussion
      11. 8.10. Filling a DataSet Using an XML Template Query
        1. Problem
        2. Solution
        3. Discussion
          1. SQLXML Managed Classes
          2. Template queries
      12. 8.11. Using a Single Stored Procedure to Update Multiple Changes to a SQL Server Database
        1. Problem
        2. Solution
        3. Discussion
    10. 9. Optimizing .NET Data Access
      1. Introduction
      2. 9.1. Filling a DataSet Asynchronously
        1. Problem
        2. Solution
        3. Discussion
      3. 9.2. Canceling an Asynchronous Query
        1. Problem
        2. Solution
        3. Discussion
      4. 9.3. Caching Data
        1. Problem
        2. Solution
        3. Discussion
      5. 9.4. Improving Paging Performance
        1. Problem
        2. Solution
        3. Discussion
      6. 9.5. Performing a Bulk Insert with SQL Server
        1. Problem
        2. Solution
        3. Discussion
      7. 9.6. Improving DataReader Performance with Typed Accessors
        1. Problem
        2. Solution
        3. Discussion
      8. 9.7. Improving DataReader Performance with Column Ordinals
        1. Problem
        2. Solution
        3. Discussion
      9. 9.8. Debugging a SQL Server Stored Procedure
        1. Problem
        2. Solution
        3. Discussion
          1. Debugging a stored procedure in standalone mode
          2. Debugging a stored procedure from managed code
      10. 9.9. Improving Performance While Filling a DataSet
        1. Problem
        2. Solution
        3. Discussion
      11. 9.10. Retrieving a Single Value from a Query
        1. Problem
        2. Solution
        3. Discussion
      12. 9.11. Reading and Writing Binary Data with SQL Server
        1. Problem
        2. Solution
        3. Discussion
      13. 9.12. Reading and Writing Binary Data with Oracle
        1. Problem
        2. Solution
        3. Discussion
      14. 9.13. Performing Batch Updates with a DataAdapter
        1. Problem
        2. Solution
        3. Discussion
      15. 9.14. Refreshing a DataSet Automatically Using Extended Properties
        1. Problem
        2. Solution
        3. Discussion
    11. 10. Enumerating and Maintaining Database Objects
      1. Introduction
      2. 10.1. Listing SQL Servers
        1. Problem
        2. Solution
        3. Discussion
      3. 10.2. Retrieving Database Schema Information from SQL Server
        1. Problem
        2. Solution
        3. Discussion
      4. 10.3. Retrieving Column Default Values from SQL Server
        1. Problem
        2. Solution
        3. Discussion
      5. 10.4. Determining the Length of Columns in a SQL Server Table
        1. Problem
        2. Solution
        3. Discussion
      6. 10.5. Counting Records
        1. Problem
        2. Solution
        3. Discussion
      7. 10.6. Creating a New Access Database
        1. Problem
        2. Solution
        3. Discussion
      8. 10.7. Creating a New SQL Server Database
        1. Problem
        2. Solution
        3. Discussion
      9. 10.8. Adding Tables to a Database
        1. Problem
        2. Solution
        3. Discussion
      10. 10.9. Getting a SQL Server Query Plan
        1. Problem
        2. Solution
        3. Discussion
      11. 10.10. Compacting an Access Database
        1. Problem
        2. Solution
        3. Discussion
      12. 10.11. Creating DataSet Relationships from SQL Server Relationships
        1. Problem
        2. Solution
        3. Discussion
      13. 10.12. Getting SQL Server Column Metadata Without Returning Data
        1. Problem
        2. Solution
        3. Discussion
      14. 10.13. Listing Installed OLE DB Providers
        1. Problem
        2. Solution
        3. Discussion
      15. 10.14. Listing Tables in an Access Database
        1. Problem
        2. Solution
        3. Discussion
      16. 10.15. Creating a Table in the Database from a DataTable Schema
        1. Problem
        2. Solution
        3. Discussion
      17. 10.16. Listing Installed ODBC Drivers
        1. Problem
        2. Solution
        3. Discussion
    12. A. Converting from C# to VB Syntax
      1. General Considerations
      2. Classes
      3. Structures
      4. Interfaces
      5. Class, Structure, and Interface Members
        1. Fields
        2. Methods
        3. Properties
        4. Events
      6. Delegates
      7. Enumerations
    13. Index
    14. Colophon

Product information

  • Title: ADO.NET Cookbook
  • Author(s):
  • Release date: September 2003
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596004392