Designing for Simplified Data Access

Developers face many implementation choices and requirements when they build data access solutions. They must access the data in a variety of ways, and their solutions must work with different types of databases, each of which handles data access differently. As a result, developers may find themselves duplicating code that performs common tasks, such as managing connections and assigning parameters to commands.

Another challenge is maintaining a consistent approach in how data access operations are implemented. It may be necessary to maintain this consistency across single projects, multiple projects, or enterprise-scale solutions. Uniform methods of data access make the code easier to understand, more predictable, and easier to maintain.

The Data Access Application Block simplifies data access by encapsulating the logic that performs common database operations. These methods also handle common housekeeping tasks such as opening and closing connections. They are database-agnostic, which means that they work with SQL Server and Oracle databases and do not require modification to do so. Applications written for one type of database use the same methods as those written for another type of database. This means that applications are consistent in the ways that they access data. In addition, the GenericDatabase class supports many of these same features across ADO.NET data providers.

The application block was designed to simplify the task of accessing data. Therefore, it required the following design decisions:

The next sections describe these decisions.

Limited Set of Operations

The application block supports a small number of operations that simplify the most common data access tasks. It provides an abstract base class named Database that defines the set of methods the application block supports.

Each of these methods has multiple overloads. The overloads allow varying degrees of control over the information that each method passes, and they accommodate different styles of programming. One class of overloads allows you to pass objects of ADO.NET type DbCommand, as shown in the following example. This assumes that you have resolved an instance of the Database class and stored a reference in the variable named db. For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

DbCommand cmd = db.GetStoredProcCommand("GetProductList"); 
DataSet productsDataSet = db.ExecuteDataSet(cmd);

For each of the methods available on the Database class, there are two overloads for the versions that accept a DbCommand object: one overload for execution outside a transaction and one overload for execution within a transaction. For example, the following code samples are the two overloads for the ExecuteDataSet method. The first has no transaction and the second has a transaction.

public virtual DataSet ExecuteDataSet(DbCommand command) 

public virtual DataSet ExecuteDataSet(DbCommand command, 
                                      IDbTransaction transaction)

For developers who prefer to simply pass all required information to a Database class method, there are overloads for each of the methods that allow you to supply the required information in a single call. For example, the ExecuteDataSet method includes the following overload, which allows the developer to pass a stored procedure name and a collection of parameters to be used.

public virtual DataSet ExecuteDataSet(string storedProcedureName, 
                                      params object[] parameterValues)

Encapsulation of Connection Lifetime

One of the most common tasks developers must consider is how to manage connections to the database. Whenever possible, the application block handles connection management. An application block method opens a connection and closes it prior to returning. This reduces both the amount of client code required and the possibility of leaving connections open.

In the case of the ExecuteDataReader method, the DataReader object can be specified with the CommandBehavior.CloseConnection property set. This automatically closes the connection when the DataReader is closed. However, when using the Data Access Application Block, you do not need to specify a CommandBehavior parameter. By default, the method will automatically set the CommandBehavior property on the underlying reader to CloseConnection unless you specify a transaction when you call the method. If you do specify a transaction, it does not set the CommandBehavior property.

DbCommand cmd = db.GetSqlStringCommand("Select Name, Address From Customers");
using (IDataReader reader = db.ExecuteReader(cmd))
  // Process results

Convenient Parameter Handling

The application block makes it easy to write data access code that handles stored procedure parameters. Developers can either explicitly create parameters or use the method overloads that support parameter discovery.

The Database class includes methods that associate parameters with a DbCommand object. Developers must pass the DbCommand object, the parameter name, and the parameter type in their code. For information about these methods and code examples, see Handling Parameters.

The call to GetStoredProcCommand allows developers to specify values to be used as parameters when the specified stored procedure is called. The Database class uses dynamic discovery of the parameter information if it has the parameter values. Because of this, the client code does not need to specify each parameter type, as shown in the following example. The example assumes that you have resolved an instance of the Database class and stored a reference in the variable named db. For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory", 2); 
DataSet productsDataSet = db.ExecuteDataSet(cmd);

Dynamic discovery is convenient for developers because they can simply pass values without having to look up information, such as the value names and types. (Dynamic discovery of parameters is not supported by the GenericDatabase class.)

Performance Considerations

Developers must consider performance when writing code that accesses data; the design of the Data Access Application Block reflects this in a number of ways:

  • Specific classes exist for each database type. These classes represent the code a developer would have added. They do not impose additional layers of code on the application. Deriving methods from the common abstract base class, Database, ensures conformance to a common interface. A common implementation is kept in a single location, without sacrificing performance.
  • The ParameterCache class provides a cache that stores the parameter information for each procedure call. Because dynamic discovery of parameters requires a round trip to the database, using a cache means that subsequent calls to the same procedure do not incur additional trips after the parameters are obtained.