A Relational Database Management System (RDBMS) is the most common and preferred storage mechanism for enterprise applications. ADO.NET is the cornerstone while working with databases on the .NET platform; it provides the framework and implementations for several databases. Developers leveraging ADO.NET often have to write boilerplate code over and over again. While performing database operations, this might lead to lower productivity, inefficient code, connection leakage, and so on.
The Data Access Application Block abstracts developers from the underlying database technology by providing a common interface to perform database operations. It simplifies common data access functionality by taking care of the mundane tasks like creating a connection object, opening and closing a connection, parameter caching, and so on. The Data Access block supports all the features supported by ADO.NET; it goes a step further by bringing consistency and simplifying the common database tasks.
The benefits of the Data Access block are as follows:
- It reduces the plumbing code to perform common tasks.
- It builds on top of the functionality provided by ADO.NET, so both ADO.NET's and application block's functionality are available at our disposal.
- It allows changing the database type without changing or re-compiling the application code.
- It provides parameter caching for all databases and implements simple connection pooling for SQL CE as well.
- It increases developer productivity through its rich set of methods, which reduces the data access code considerably.
In this chapter, you will:
- Get to know the key elements of the Data Access block
- Reference the required and optional assemblies
- Configure Data Access settings using the configuration editor
- Add a namespace for convenience
- Create Data Access block objects
- Retrieve records using
ExecuteReaderandExecuteDataSet - Retrieve a record as an Object
- Retrieve multiple records as an Object Collection
- Retrieve records as XML using
ExecuteXmlReader - Execute a command using
ExecuteNonQuery - Leverage output parameters
- Access a scalar result using
ExecuteScalar - Update records using
DataSet - Work with Transactions
It takes two to tango, and in this case it takes configuration and application code. We configure the database connection string and set the provider name attribute; this attribute is mandatory for the Data Access block to work. Optionally, we may also set the default database instance attribute part of the Database Settings. In the application code, there are several elements involved in making the Data Access block work, but it all begins with a class called Database. The Database class abstracts us from the underlying database technology and provides us with a simple model to perform various actions against the configured database. It internally leverages the ADO.NET provider factory model (DbProviderFactory); an instance of Database contains a reference to a concrete DbProviderFactory object, which exposes common ADO.NET objects such as DbConnection, DbCommand, DbDataAdapter, DbDataReader, and so on. The DbProviderFactory class is an abstract class, part of the System.Data.Common namespace.
The following is a partial list of concrete implementations of DbProviderFactory.
|
Provider name |
Provider Factory Implementation |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ever noticed an attribute called providerName in the connection string entry? This information is used to construct the appropriate provider factory object and that's the reason it is a required attribute as far the Data Access block is concerned. Data Access block configuration code contains default mappings for the data providers. System.Data.SqlClient data provider maps with SqlDatabase, System.Data.OracleClient data provider maps with OracleDatabase, and GenericDatabase is used for all other data providers.
Note
There is an active community called "EntLib Contrib" developing a library of extensions for Enterprise Library. Many third-party providers are available such as SQLite, Oracle (ODP.NET), MySql, IBM DB2, and PostgreSQL databases. Visit http://entlibcontrib.codeplex.com for more information.