This topic describes the most common situations developers must address when accessing a database. Each scenario explains the task, describes a real-world situation where such a task might arise, and includes code demonstrating how to use the Data Access Application Block to complete the task. The scenarios are as follows:
- Using a DbDataReader to Retrieve Multiple Rows. This scenario illustrates how you can use the ExecuteReader method to retrieve multiple rows of data from a database for display in tabulated form—without explicitly caching the data, using a DataSet object to manipulate it, or passing it to other components within your application. In other words, it illustrates how to display the results as quickly as possible.
- Using a DataSet to Retrieve Multiple Rows. This scenario illustrates how you can use the ExecuteDataSet method to pass data between the components and the tiers of a multi-tier application. The data consists of one or more data tables and, optionally, the relationships that link the tables together.
- Executing a Command and Accessing Output Parameters. This scenario illustrates how you can use the ExecuteNonQuery method to retrieve a single row that contains multiple column values.
- Executing a Command and Accessing a Single Item Result. This scenario illustrates how you can use the ExecuteScalar method to perform a single-item lookup.
- Performing Multiple Updates Within a Transaction. This scenario illustrates how you can use the ExecuteNonQuery method from within a transaction to perform multiple operations against a database, where it is essential that either all operations succeed or none succeed.
- Using a DataSet to Update a Database. This scenario illustrates how, after changing a DataSet object, you can use the UpdateDataSet method to update the database and make your changes permanent.
- Retrieving Multiple Rows as XML. This scenario illustrates how you can use the ExecuteXmlReader method to retrieve data from a SQL Server and have that data returned in XML format.
- Retrieving Data as Objects. This scenario demonstrates how you can use the data accessors included in the block to retrieve data as a sequence of objects of a specified type from the data store.
- Performing Asynchronous Data Access. This scenario demonstrates how you can use the asynchronous version of the ExecuteReader method to read data from a data store asynchronously, and then access the results using a callback that indicates when the method completes.
This topic helps you implement your approach by using the Data Access Application Block. It does not help you choose the correct approach for your particular situation (for example, it does not help you choose between a DataSet and a DbDataReader). For guidance on approaches to data access, see the following Microsoft patterns & practices guides:
- .NET Data Access Architecture Guide
- INFO: Microsoft Guide for Designing Data Tier Components and Passing Data Through Tiers
- Improving .NET Application Performance and Scalability