Designing for Database-Agnostic Applications

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.

The Data Access Application Block provides an extensible framework for supporting multiple types of relational databases. Applications that use the application block are portable across different database systems.

There are a number of general data access tools—such as Open Database Connectivity (ODBC) or OLE DB—that can provide access to a variety of data sources. One drawback to these tools is that how they are used depends on the target database. That means that programmers need to understand various programming models to access different database types. Moving an application to a different database could require a significant amount of recoding.

Another drawback to the ODBC or OLE DB approach is that performance may suffer. Generic data providers are slower than those optimized for a particular data source. The Data Access Application Block provides a factory-based implementation that features both portability and optimized performance.

Design Implications

These features imply several things about the application block's design:

  • Abstraction of the database system
  • Isolation of features that are specific to the Database base class

The next sections describe these implications.

Abstraction of the Database System

The Data Access Application Block builds on the capabilities provided by ADO.NET 2.0 to create a database-agnostic provider model. The following are some of the features it provides:

  • It standardizes parameter names. For example, it supplies the "@" character for SQL parameter names.
  • It configures the stored procedure package name mapping for Oracle databases.
  • It uses ADO.NET static methods for SQL Server and Oracle to support agnostic parameter discovery.
  • It adds the cursor parameter for results returned by an Oracle stored procedure.

The majority of the data access methods are available through the abstract Database class. Client code can refer to these methods in their code regardless of the actual Database-derived object used. For example, the following code shows how to use the ExecuteDataSet method.

Database db;
Dim db As Database

A factory, DatabaseFactory, creates the specific Database-derived object. It returns an object of type Database, thus allowing the client code to remain generic regarding the actual database type returned. For example, the following code creates the default database object.

Database db = DatabaseFactory.CreateDatabase();
Dim db As Database = DatabaseFactory.CreateDatabase()

The methods available on the Database class require information about the command to be executed as well as any associated parameters. Different database systems handle commands and parameters in different ways. Database-derived classes provide methods that accept parameter information; the specific database systems provide their own derived implementations to handle parameter parameters.

Isolation of Database-Specific Features

Functionality specific to a particular database system is incorporated in the appropriate Database-derived class. For example, ExecuteXmlReader is only available in the SqlDatabase class. Because DatabaseFactory returns an object of type Database, the client code must downcast to the correct type to use any methods that are unique to a particular database. For example, the following code creates a SQL Server database object, which is the only database object that can use the ExecuteXmlReader method. It then returns an XmlReader object.

SqlDatabase dbSQL = (SqlDatabase)
XmlReader xmlResults = dbSQL.ExecuteXmlReader(cmd);
Dim dbSQL As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("EntLibQuickStartsSql"), SqlDatabase)
Dim xmlResults As XmlReader = dbSQL.ExecuteXmlReader(cmd)