DataSet against DataReader

Some of the scenarios in which a DataSet is easier to use than a DataReader include the following:

• When you need a convenient package to send the data to another component (for example, if you’re sharing information with other components or distributing it to clients through a web service).


• When you need a convenient file format to serialize the data to disk (the DataSet includes built-in functionality that allows you to save it to an XML file).


• When you want to navigate backward and forward through a large amount of data. For example, you could use a DataSet to support a paged list control that shows a subset of information at a time. The DataReader, on the other hand, can move in only one direction: forward.


• When you want to navigate among several different tables. The DataSet can store all these tables, and information about the relations between them, thereby allowing you to create easy master-detail pages without needing to query the database more than once.


• When you want to use data binding with user interface controls. You can use a DataReader for data binding, but because the DataReader is a forward-only cursor, you can’t bind your data to multiple controls. You also won’t have the ability to apply custom sorting and filtering criteria, like you can with the DataSet.


• When you want to manipulate the data as XML.


• When you want to provide batch updates. For example, you might create a web service that allows a client to download a DataTable full of rows, make multiple changes, and then resubmit it later. At that point, the web service can apply all the changes in a single operation (assuming no conflicts occur).

Basic guidelines to create a database component

Open and close connections quickly: Open the database connection in every method call, and close it before the method ends. Connections should never be held open between client requests, and the client should have no control over how connections are acquired or when they are released. If the client does have this ability, it introduces the possibility that a connection might not be closed as quickly as possible or might be inadvertently left open, which hampers scalability.

Implement error handling: Use error handling to make sure the connection is closed even if the SQL command generates an exception. Remember, connections are a finite resource, and using them for even a few extra seconds can have a major overall effect on performance.

Follow stateless design practices: Accept all the information needed for a method in its parameters, and return all the retrieved data through the return value. If you create a class that maintains state, it cannot be easily implemented as a web service or used in a load-balancing scenario. Also, if the database component is hosted out of the process, each method call has a measurable overhead, and using multiple calls to set properties will take much longer than invoking a single method with all the information as parameters.

Don’t let the client use wide-open queries: Every query should judiciously select only the columns it needs. Also, you should restrict the results with a WHERE clause whenever possible. For example, when retrieving order records, you might impose a minimum date range (or a SQL clause such as TOP 1000). Without these safeguards, your application may work well at first but will slow down as the database grows and clients perform large queries, which can tax both the database and the network.

A good, straightforward design for a database component uses a separate class for every database table (or logically related group of tables). The common database access methods such as inserting, deleting, and modifying a record are all wrapped in separate stateless methods. Finally, every database call uses a dedicated stored procedure.

Some practices for working with transactions

• Keep transactions as short as possible.


• Avoid returning data with a SELECT query in the middle of a transaction. Ideally, you should return the data before the transaction starts. This reduces the amount of data your transaction will lock.


• If you do retrieve records, fetch only the rows that are required so as to reduce the number of locks.


• Wherever possible, write transactions within stored procedures instead of using ADO.NET transactions. This way, your transaction can be started and completed more quickly, because the database server doesn’t need to communicate with the client (the web application).


• Avoid transactions that combine multiple independent batches of work. Put separate batches into separate transactions.


• Avoid updates that affect a large range of records if at all possible.