Encapsulates physical data access in a separate component, exposing only logical operations. Application code is decoupled from data access operations.
Consider a server-side component that is used to update an application's database from a central warehouse - data for each table needs to be extracted from the data warehouse and updated/inserted into the application's database. The database code requires multiple physical database operations and the management of corresponding resources. If you mix this code with the application logic, it quickly becomes messy and difficult to maintain:
public void UpdateProducts()
{
// Get database connection string from
somewhere
// Get data warehouse connection string from somewhere
// Open database connection
// If connection failed to open, throw an exception
// Open database warehouse connection
// If connection failed to open, throw an exception
// Get products data from the data warehouse
// Loop through each product and update the database
// If update fails, log error and process to next product
// Close data warehouse connection
// Close database connection
}
Bigger problems arise if you need to support multiple database platforms (SQL Server, Oracle, Sybase ) or incorporate optimizations such as pool connections. The Data Accessor solves this problem by building an abstraction of logical operations that hides low-level data access details from the rest of the application code. The logical operations that the Data Accessor exposes depend on the requirements of the application. Here are some recommendations for encapsulating physical data access details:
The IDataAccessor interface defines the data access abstraction in terms of logical operations that the application code will use. While one interface is shown, it is typical to group logically-related operations in separate interfaces - for example IDataAccessorQuery for query operations, IDataAccessTransaction for transaction-based operations, and so on. OracleDataAccessor and SQLServerDataAccessor are concrete classes that provide actual implementations of logical operations in terms of physical database operations. Each such concrete class therefore depends on a specific database technology.
/* IDataAccessor interface declaration.
Each logical operation (i.e., ExecuteNonQuery) supports single and multiple SQL
statements */
public interface IDataAccessor
{
// ExecuteNonQuery
Int32 ExecuteNonQuery(String strCS, CommandType cmdType, String strCommandText );
Int32[] ExecuteNonQuery(String strCS, CommandType[] acmdType, String[]
astrCommandText );
// ExecuteDataset
DataSet ExecuteDataset(String strCS, CommandType cmdType, String strCommandText );
DataSet[] ExecuteDataset(String strCS, CommandType[] acmdType, String[]
astrCommandText );
// ExecuteDataReader
IDataReader ExecuteDataReader(String strCS, CommandType cmdType, String strCommandText );
IDataReader[] ExecuteDataReader(String strCS, CommandType[] acmdType, String[]
astrCommandText );
// ExecuteScalar
Object ExecuteScalar(String strCS, CommandType cmdType, String strCommandText );
Object[] ExecuteScalar(String strCS, CommandType[] acmdType, String[]
astrCommandText );
}
public class OracleDataAccessor : IDataAccessor
{
...
/* Oracle-implementation of
IDataAccessor */
// ExecuteNonQuery
Int32 ExecuteNonQuery(String strCS, CommandType
cmdType, String strCommandText )
{
/* Use
Oracle-specific ADO.NET classes such as OracleCommand, OracleDataAdapter, etc.
*/
}
Int32[] ExecuteNonQuery(String strCS, CommandType[]
acmdType, String[] astrCommandText, ref Array[] asqlParam )
{
/* Use
Oracle-specific ADO.NET classes such as OracleCommand, OracleDataAdapter, etc.
*/
}
...
}
public class SQLServerDataAccessor : IDataAccessor
{
...
/* SQL Server-implementation of
IDataAccessor */
// ExecuteNonQuery
Int32 ExecuteNonQuery(String strCS, CommandType
cmdType, String strCommandText )
{
/* Use SQL
Server-specific ADO.NET classes such as SqlCommand, SqlDataAdapter, etc. */
}
Int32[] ExecuteNonQuery(String strCS, CommandType[]
acmdType, String[] astrCommandText, ref Array[] asqlParam )
{
/* Use SQL
Server-specific ADO.NET classes such as SqlCommand, SqlDataAdapter, etc. */
}
...
}
// Use a factory (not shown) to
create a SQL Server data accessor
IDataAccessor obSQLServer = new DBAccessFactory( eAccessor.SQLServer);
// Access data
obSQLServer.ExecuteNonQuery( strConn, CommandType.Text, "select * from
T1" );
// Use a factory (not shown) to
create an Oracle data accessor
IDataAccessor obOracle = new DBAccessFactory( eAccessor.Oracle);
// Access data
obOracle.ExecuteNonQuery( strConn, CommandType.Text, "select * from
T2" );
Note: certain operations common to both concrete classes SQLServerDataAccessor and OracleDataAccessor can be factored out to a base class from which both data accessors will have to inherit.
Use this pattern when:
Consider these strategies when designing a data accessor interface.