Retrieving and Managing Data

Introduction

The DataSet is a disconnected, in-memory relational database that provides sophisticated navigational capabilities. It acts as a container for other objects including DataTable , DataColumn , DataRow , and DataRelation . The DataAdapter works with the DataSet to update the data source with changes made offline to the DataSet . You can also data bind a DataSet to a variety of Windows Forms and Web Forms controls, in particular, any control that supports the IList interface. The DataSet maintains both current and original versions of its data. Although data appears to be changed, it is not permanently changed until the AcceptChanges( ) method is called either explicitly or implicitly to commit the changes. Recipe 2.6 shows how to access rows marked for deletion.

The DataReader provides forward-only, read-only access to a result set. The DataReader offers the best performance for accessing data by avoiding the overhead associated with the DataSet . The Connection object underlying a DataReader remains open and cannot be used for any other purpose while data is being accessed. This makes the DataReader unsuitable for communicating data remotely between application tiers, or interacting with the data dynamically. If you want to discard a result set in a DataReader before the entire result set has been read, call the Cancel( ) method of the DataReader before calling the Close( ) method. This discards the results on the server so they are not transmitted to the client. Simply calling Close( ) causes the remaining results to be pulled to the client to empty the stream. Since the DataReader reads a result set stream directly from a connection, there is no way to know the number of records in a DataReader . Recipe 2.7 demonstrates techniques that simulate a record count for a DataReader and discusses limitations of the techniques.

You can define DataSet and DataReader object schemas programmatically or infer them from a database schema. Retrieving schema information from the database has its limitations. For example, data relations cannot be created in a DataSet from the database schema.

The DataSet is data source independent and uses .NET Framework data types to define column schema in tables. These data types are not the same as .NET data provider types; the provider data types are mapped to .NET Framework data types. Recipe 2.8 shows the mappings for SQL Server, Oracle, OLE DB, and ODBC .NET data providers to .NET Framework data types as well as the DataReader type accessors for each.

The DataAdapter can map table and column names in the database to tables and columns with different names in a DataTable . This allows the application to use different column and table names than are used by the database. The DataAdapter automatically maps names when retrieving and updating data. Recipe 2.16 demonstrates table and column mapping.

A variety of error information is available when a DataAdapter fails while updating data. Recipe 2.15 shows what error information is available and how to work with it.

In addition to recipes for working with the DataSet , DataReader , and DataAdapter classes, this chapter covers the following:

Strongly typed DataSets

A strongly typed DataSet is a collection of classes that inherit from and extend the DataSet , DataTable , and DataRow classes, providing additional properties, methods , and events, and making them easier to use. Because they are typed, you'll get type mismatch and other errors at compilation rather than at runtime (also, strongly typed DataSet s work with Visual Studio .NET's IntelliSense). They are, however, slightly slower than untyped DataSet objects because of extra overhead. Because they are typed, they can make maintaining interfaces in distributed applications more complicated and difficult to administer. Recipe 2.3 discusses the different ways to create and use a strongly typed DataSet . Recipe 2.18 and Recipe 2.19 show how to override the default naming used by and behavior of a strongly typed DataSet .

Stored procedures

Stored procedure output parameters are generally used to return results from a single row and are slightly faster than a DataReader for this purpose with connection pooling enabled; without connection pooling, the opposite is true. Additionally, the DataReader is capable of returning metadata for the row. Accessing output parameters is straightforward with disconnected classes and a data adapter, but not when using the DataReader . Recipe 2.9 shows how. Stored procedures can also return a return value parameter, which usually returns status or error information. Recipe 2.12 shows how get a stored procedure return value.

Passing input arguments to a stored procedure is straightforward. Recipe 2.14 shows how to pass null values.

SQL has a RAISERROR function that lets you generate custom errors from the stored procedure and return them to the caller. Recipe 2.10 shows how to raise and handle stored procedure errors.

In addition to stored procedures, Oracle has packages that serve as containers for stored procedures and functions. Recipe 2.20 shows how to use Oracle packages from ADO.NET.

Scalar functions are routines that take one or more parameters and return a single value. Recipe 2.13 shows how to execute a scalar function and get the return value.

In addition to using parameters with stored procedures, ADO.NET allows you to execute parameterized SQL statements. Recipe 2.21 shows how to create and execute these statements.

Queries

SQL Server supports batch queries that return multiple result sets from a single request to the server. Recipe 2.4 shows how to process the result sets using both a DataSet and a DataReader .

Sometimes it's useful to know whether a query returned any records; therefore, Recipe 2.11 shows you how to find out when using both a DataTable and a DataReader .

Web services and messaging

Web services allow distributed applications running on disparate platforms to communicate using open standards and protocols. Recipe 2.5 shows how to create a web service that processes a query request, and how to call the web service from a .NET application.

Messaging allows applications running on disparate platforms to communicate whether they are connected or disconnected. Recipe 2.22 shows how to use messaging to query a database.