Designing for Simplified Data Access

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The latest Enterprise Library information can be found at the Enterprise Library site.

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.

Design Implications

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

  • It should expose only a small number of methods that a developer would need to understand.
  • It should encapsulate common housekeeping tasks.
  • It should make it easy to handle parameters.
  • It should provide good performance.
  • It should be simple to create a database object.

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, Database, that defines the set of methods the application block supports. These methods include the following:

  • ExecuteDataSet
  • LoadDataSet
  • ExecuteReader
  • ExecuteScalar
  • ExecuteNonQuery
  • UpdateDataSet

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.

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetStoredProcCommand("GetProductList"); 

DataSet productsDataSet = db.ExecuteDataSet(dbCommand);

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)

Ff648455.note(en-us,PandP.10).gifNote:
The Data Access Application Block transactions are instances of the ADO.NET 2.0 DbTransaction base class. The application block does not use transactions that belong to the ADO.NET 2.0 System.Transaction namespace.

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 is executed using the CommandBehavior.CloseConnection method. This automatically closes the connections when the DataReader is closed.

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetSqlStringCommand("Select Name, Address From Customers");

using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
// 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.

Explicit Parameter Creation

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" in Developing Applications with the Data Access Application Block.

Parameter Discovery

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.

Database db = DatabaseFactory.CreateDatabase();

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

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, and 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.

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The latest Enterprise Library information can be found at the Enterprise Library site.
Show: