ADO.NET 3.5 Cookbook | O'Reilly Media

Tell a friend

ADO.NET 3.5 Cookbook, Second Edition By Bill Hamilton
March 2008
Pages: 980

| Table of Contents | Index | Sample Chapter | Colophon

Chapter 1 Connecting to Data
1. Introduction
2. Storing Connection Strings
3. Building a Connection String
4. Connecting to SQL Server
5. Connecting to a Named Instance of SQL Server
6. Connecting to SQL Server Using an IP Address
7. Connecting to SQL Server Using Integrated Security from ASP.NET
8. Connecting to an Oracle Database
9. Connecting to an OLE DB Data Source
10. Connecting to an ODBC Data Source
11. Connecting to a Microsoft Access Database
12. Connecting to a Password-Protected Microsoft Access Database
13. Connecting to a Microsoft Access Database from ASP.NET
14. Connecting to a Microsoft Excel Workbook
15. Connecting to a Text File
16. Changing the Database for an Open Connection
17. Setting Connection Pooling Options
18. Taking Advantage of Connection Pooling
19. Using Transactions with Pooled Connections
20. Displaying a Connection Property Dialog Box
21. Displaying the Data Link Properties Dialog Box
22. Monitoring Connections
Chapter 2 Working with Disconnected Data Objects
1. Introduction
2. Creating a DataColumn and Adding It to a DataTable
3. Creating a DataTable and Adding It to a DataSet
4. Mapping Table and Column Names Between a Data Source and DataSet
5. Mapping .NET Data Provider Data Types to .NET Framework Data Types
6. Adding a Calculated Column to a DataTable
7. Creating a Unique Constraint
8. Creating Single- and Multi-Column Primary Keys
9. Creating an Autoincrementing Primary Key
10. Creating a Foreign Key Constraint
11. Creating a Data Relation
12. Building a DataSet Programmatically
13. Adding a Column to a Child DataTable That Displays Data from the Parent Table
14. Adding a Column to a Parent DataTable That Aggregates a Child Table's Column Values
15. Converting Between a DataTable and a DataRow Array
16. Accessing Data Values in a DataRow Array
17. Creating a Strongly Typed DataSet
18. Controlling the Names Used in a Strongly Typed DataSet
19. Replacing Null Values in a Strongly Typed DataSet
Chapter 3 Querying and Retrieving Data
1. Introduction
2. Executing a Query That Does Not Return a Result Set
3. Executing a Query That Returns a Single Value
4. Retrieving a Result Set Stream Using a DataReader
5. Accessing Data Values in a DataReader
6. Retrieving a Result Set Using a DataTable or a DataSet
7. Accessing Data Values in a DataTable or DataSet
8. Working with Data in a Strongly Typed DataSet
9. Working with Parent-Child Relations in a Strongly Typed DataSet
10. Using a DataView with a Strongly Typed DataSet
11. Testing Whether a Query Returns an Empty Result Set
12. Counting Records Meeting Criteria
13. Determining the Number of Records Returned in a DataReader
14. Executing a Query That Returns Multiple Result Sets
15. Retrieving Schema and Constraints for a DataSet
16. Retrieving Hierarchical Data
17. Navigating Between Parent and Child Tables in an Untyped DataSet
18. Executing a Parameterized Query
19. Retrieving Data Using a SQL Server Stored Procedure
20. Retrieving Multiple Result Sets Using the Oracle Provider
21. Passing a Null Value to a Query Parameter
22. Using Table-Valued Types As Parameters
23. Retrieving a Return Value from a Stored Procedure
24. Retrieving a Stored Procedure Output Parameter
25. Raising and Handling Stored Procedure Errors
26. Executing a SQL Server Scalar-Valued Function
27. Executing a SQL Server Table-Valued Function
28. Querying a DataSet Using LINQ
29. Querying a SQL Server Database Using LINQ
30. Retrieving Data from a Text File
31. Retrieving Data from a Microsoft Excel Workbook
32. Querying Data Asynchronously with Message Queuing
Chapter 4 Searching and Analyzing Data
1. Introduction
2. Determining the Differences in Data Between Two DataSet Objects
3. Combining Data from Heterogeneous Data Sources
4. Filtering Rows in a DataTable or DataView
5. Finding Rows in a DataTable or DataView
6. Filtering and Sorting Data in a DataTable
7. Filtering Null Field Values in a DataTable
8. Accessing Deleted Rows in a DataTable
9. Selecting the Top N Rows in a DataTable
10. Executing Queries That Use COMPUTE BY
11. Using the Shape Language to Retrieve Hierarchical Data
12. Retrieving a Random Sample of Records
13. Using a Common Table Expression (CTE)
14. Executing a Recursive Query
15. Retrieving a Ranked Result Set
16. Retrieving a Pivot and Unpivot Table
17. Invoking a Function for Each Row in a Result Set
Chapter 5 Adding and Modifying Data
1. Introduction
2. Using Autoincrementing Columns Without Causing Conflicts
3. Getting an Identity Column Value from SQL Server
4. Getting an AutoNumber Value from Microsoft Access
5. Getting a Sequence Value from Oracle
6. Modifying Data in a Microsoft Excel Workbook
7. Modifying Data in a Text File
8. Retrieving Update Errors
9. Adding Parent/Child Rows with Autoincrementing Keys
10. Adding Records with a GUID Primary Key
11. Inserting Multiple Rows into a Database Table Using T-SQL Row Constructors
12. Updating a Data Source with Data from a Different Data Source
13. Updating a Primary Key Value
14. Getting Stored Procedure Parameter Information at Runtime
15. Updating a DataSet with a Many-to-Many Relationship
16. Updating Data Asynchronously Using Message Queuing
17. Controlling Edits, Deletions, or Additions to Data with a DataView
18. Overcoming Keyword Conflicts When Using a CommandBuilder
19. Capturing Changes to Data in a SQL Server Database
Chapter 6 Copying and Transferring Data
1. Introduction
2. Copying Rows from One DataTable to Another
3. Copying Tables from One DataSet to Another
4. Converting a DataReader to a DataTable
5. Converting a DataReader to a DataSet
6. Converting a DataTable to a DataReader
7. Converting a DataSet to a DataReader
8. Serializing Data
9. Deserializing Data
10. Merging Data in Two DataSet Objects
11. Merging Data in Two Database Tables
12. Encrypting a DataSet
13. Securing Login Credentials
14. Exporting the Results of a Query As a String
15. Exporting the Results of a Query to an Array
16. Loading an ADO Recordset into a DataSet
17. Converting a DataSet to an ADO Recordset
Chapter 7 Maintaining Database Integrity
1. Introduction
2. Using Distributed Transactions
3. Using Manual Transactions
4. Nesting Manual Transactions with the SQL Server .NET Data Provider
5. Using ADO.NET and SQL Server DBMS Transactions Together
6. Using a Transaction with a DataAdapter
7. Avoiding Referential Integrity Problems when Updating Data in Related Tables
8. Enforcing Business Rules with Column Expressions
9. Retrieving Constraints from a SQL Server Database
10. Checking for Concurrency Violations
11. Resolving Data Conflicts
12. Using Transaction Isolation Levels to Protect Data
13. Specifying Locking Hints in a SQL Server Database
Chapter 8 Programmatically Working with Data in .NET Windows and Web Forms User Interfaces
1. Introduction
2. Loading Data into and Binding a Field to a Web Forms Control
3. Binding Data to a Web Forms Control
4. Binding Data to a Web Forms DetailsView Control
5. Binding Data to a Web Forms Repeater Control
6. Binding Data to a Web Forms DataList Control
7. Binding Data to a Web Forms GridView Control
8. Modifying and Updating Data in a Web Forms GridView Control
9. Binding Data to a Web Forms FormView Control
10. Synchronizing Master-Detail Data in a Web Forms Application
11. Displaying an Image from a Database in a Web Forms Control
12. Localizing Client-Side Data in a Web Forms Application
13. Loading Data into and Binding a Field to a Windows Forms Control
14. Binding Data to a Windows Forms Control
15. Binding Data to a Windows Forms DataGridView Control
16. Modifying and Updating Data in a Windows Forms DataGridView Control
17. Using Windows Forms BindingNavigator and BindingSource Controls
18. Synchronizing Master-Detail Data in a Windows Forms Application
19. Displaying an Image from a Database in a Windows Forms Control
20. Binding a Group of Radio Buttons to a Windows Forms Data Field
21. Searching a Windows Forms DataGridView Control
Chapter 9 Working with XML Data
1. Introduction
2. Using an XML File to Save and Load a DataSet or a DataTable
3. Using XSD Schema Files to Save and Load a DataSet Structure
4. Formatting Column Values When Saving Data As XML
5. Creating an XML File That Shows Changes Made to a DataSet
6. Synchronizing a DataSet and an XML Document
7. Storing and Retrieving XML with a Non-XML Data Type Column
8. Working with a SQL Server XML Data Type Column
9. Using an XPath Query to Retrieve Data
10. Reading XML Data Directly from SQL Server
11. Transforming a DataSet Using XSLT
12. Filling a DataSet Using an XML Template Query
13. Using OpenXML to Update Multiple Changes to SQL Server
Chapter 10 Optimizing .NET Data Access
1. Introduction
2. Executing Multiple Commands on a Single Connection
3. Executing a SQL Statement Asynchronously
4. Executing Simultaneous SQL Statements Asynchronously
5. Creating a DataReader Asynchronously
6. Filling a DataSet Asynchronously
7. Canceling an Asynchronous Query
8. Caching Data
9. Improving Paging Performance
10. XML Bulk Loading with SQL Server
11. Bulk Copying with SQL Server
12. Improving DataReader Performance with Typed Accessors
13. Improving DataReader Performance with Column Ordinals
14. Debugging a SQL Server Stored Procedure
15. Improving Performance While Filling a DataSet
16. Reading and Writing Large-Value Data with SQL Server
17. Reading and Writing a SQL Server User-Defined Type (UDT)
18. Reading and Writing Oracle Large Data
19. Performing Batch Updates with a DataAdapter
20. Automatically Refreshing a DataTable Periodically
21. Automatically Refreshing a DataTable When Underlying Data Changes
22. Retrieving SQL Server Runtime Statistics
23. Writing Provider- and Database-Independent Code
Chapter 11 Enumerating and Maintaining Database Objects
1. Introduction
2. Enumerating SQL Servers
3. Retrieving Database Metadata
4. Retrieving Database Schema Information from SQL Server
5. Retrieving Column Default Values from SQL Server
6. Determining the Length of Columns in a SQL Server Table
7. Retrieving a SQL Server Query Plan
8. Retrieving SQL Server Column Metadata Without Returning Data
9. Creating a New SQL Server Database
10. Adding a Table to a SQL Server Database
11. Creating a Database Table from a DataTable Schema
12. Creating DataSet Relationships from SQL Server Relationships
13. Creating a New Microsoft Access Database
14. Listing Tables in an Access Database
15. Enumerating .NET Data Providers
16. Enumerating OLE DB Providers
17. Enumerating ODBC Drivers
18. Changing a SQL Server User Password
Chapter 12 SQL Server CLR Integration
1. Introduction
2. Creating a Stored Procedure
3. Creating a Scalar-Valued Function
4. Creating a Table-Valued Function
5. Creating an Aggregate Function
6. Creating a User-Defined Type
7. Creating a DML Trigger
8. Creating a DDL Trigger

Appendix What's New in ADO.NET Since Version 1.0
1. ADO.NET 2.0
2. ADO.NET 3.5
Colophon

Return to ADO.NET 3.5 Cookbook

Table of Contents

Chapter 1 Connecting to Data

Introduction

Storing Connection Strings

Building a Connection String

Connecting to SQL Server

Connecting to a Named Instance of SQL Server

Connecting to SQL Server Using an IP Address

Connecting to SQL Server Using Integrated Security from ASP.NET

Connecting to an Oracle Database

Connecting to an OLE DB Data Source

Connecting to an ODBC Data Source

Connecting to a Microsoft Access Database

Connecting to a Password-Protected Microsoft Access Database

Connecting to a Microsoft Access Database from ASP.NET

Connecting to a Microsoft Excel Workbook

Connecting to a Text File

Changing the Database for an Open Connection

Setting Connection Pooling Options

Taking Advantage of Connection Pooling

Using Transactions with Pooled Connections

Displaying a Connection Property Dialog Box

Displaying the Data Link Properties Dialog Box

Monitoring Connections

Chapter 2 Working with Disconnected Data Objects

Introduction

Creating a DataColumn and Adding It to a DataTable

Creating a DataTable and Adding It to a DataSet

Mapping Table and Column Names Between a Data Source and DataSet

Mapping .NET Data Provider Data Types to .NET Framework Data Types

Adding a Calculated Column to a DataTable

Creating a Unique Constraint

Creating Single- and Multi-Column Primary Keys

Creating an Autoincrementing Primary Key

Creating a Foreign Key Constraint

Creating a Data Relation

Building a DataSet Programmatically

Adding a Column to a Child DataTable That Displays Data from the Parent Table

Adding a Column to a Parent DataTable That Aggregates a Child Table's Column Values

Converting Between a DataTable and a DataRow Array

Accessing Data Values in a DataRow Array

Creating a Strongly Typed DataSet

Controlling the Names Used in a Strongly Typed DataSet

Replacing Null Values in a Strongly Typed DataSet

Chapter 3 Querying and Retrieving Data

Introduction

Executing a Query That Does Not Return a Result Set

Executing a Query That Returns a Single Value

Retrieving a Result Set Stream Using a DataReader

Accessing Data Values in a DataReader

Retrieving a Result Set Using a DataTable or a DataSet

Accessing Data Values in a DataTable or DataSet

Working with Data in a Strongly Typed DataSet

Working with Parent-Child Relations in a Strongly Typed DataSet

Using a DataView with a Strongly Typed DataSet

Testing Whether a Query Returns an Empty Result Set

Counting Records Meeting Criteria

Determining the Number of Records Returned in a DataReader

Executing a Query That Returns Multiple Result Sets

Retrieving Schema and Constraints for a DataSet

Retrieving Hierarchical Data

Navigating Between Parent and Child Tables in an Untyped DataSet

Executing a Parameterized Query

Retrieving Data Using a SQL Server Stored Procedure

Retrieving Multiple Result Sets Using the Oracle Provider

Passing a Null Value to a Query Parameter

Using Table-Valued Types As Parameters

Retrieving a Return Value from a Stored Procedure

Retrieving a Stored Procedure Output Parameter

Raising and Handling Stored Procedure Errors

Executing a SQL Server Scalar-Valued Function

Executing a SQL Server Table-Valued Function

Querying a DataSet Using LINQ

Querying a SQL Server Database Using LINQ

Retrieving Data from a Text File

Retrieving Data from a Microsoft Excel Workbook

Querying Data Asynchronously with Message Queuing

Chapter 4 Searching and Analyzing Data

Introduction

Determining the Differences in Data Between Two DataSet Objects