Data Access Application Block
This page provides an overview of the Enterprise Library Data Access Application Block. An application block is reusable and extensible source code-based guidance that simplifies development of common data access functionality in .NET Framework applications.
The Enterprise Library Data Access Application Block simplifies development tasks that implement common data access functionality. Applications can use this application block in a variety of situations, such as reading data for display, passing data through application layers, and submitting changed data back to the database system. The application block includes support for both stored procedures and in-line SQL. Common housekeeping tasks, such as managing connections and creating and caching parameters, are encapsulated in the application block's methods. In other words, the Data Access Application Block provides access to the most often used features of ADO.NET in simple-to-use classes; this boosts developer productivity.
ADO.NET 2.0 provides classes such as the DbCommand class and the DbConnection class; these classes help to abstract the data provider from any particular database implementation. The Data Access Application Block takes advantage of these classes and provides a model that further supports encapsulation of database type-specific features, such as parameter discovery and type conversions. Because of this, applications can be ported from one database type to another without modifying the client code. The Data Access Application Block includes an abstract base class that defines a common interface and that provides much of the implementation needed by the data access methods available in ADO.NET 2.0.
The application block also includes classes that are specific to Microsoft SQL Server, Microsoft SQL Server CE, and Oracle. These classes perform operations that are particular to the database type. The code for applications written for one type of database, such as SQL Server, looks much the same as the code for applications written for another type of database, such as Oracle.
Another feature of the Data Access Application Block is that application code can refer to particular databases by an ADO.NET connection string name, such as "Customer" or "Inventory." The application code can specify a named instance of a database and pass this parameter to the DatabaseFactory.CreateDatabase method. Each named database has its connection information stored in a configuration file. By changing the settings in the configuration file, developers can use their applications with different database configurations without recompiling their code.
The Data Access Application Block provides the following benefits:
- It uses the functionality provided by ADO.NET 2.0 and with it, you can use ADO.NET functionality along with the application block's functionality.
- It reduces the need to write boilerplate code to perform standard tasks.
- It helps maintain consistent data access practices, both within an application and across the enterprise.
- It reduces difficulties in changing the database type.
- It relieves developers from learning different programming models for different types of databases.
- It reduces the amount of code that developers must write when they port applications to different types of databases.
Developers often write applications that use databases. Because it is so common, developers may find themselves repeatedly writing the same code for each application. In addition, these applications may need to work with different types of databases. Although the tasks are the same, the code must be adapted to suit the programming model of each database. The Data Access Application Block solves these problems by providing the logic to perform the most common data access tasks. Developers only need to do the following:
- Create the database object.
- Supply the parameters for the command, if they are needed.
- Call the appropriate method(s). These methods are optimized for performance. They are also portable.
The Data Access Application Block works transparently with SQL Server, SQL Server CE, and Oracle databases.
Example Application Code
The following code shows how to call a stored procedure that returns a DataSet.
[C#] Database db = DatabaseFactory.CreateDatabase(); DbCommand dbCommand = db.GetStoredProcCommand("GetProductsByCategory"); // Retrieve products from category 7. db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, 7); DataSet productDataSet = db.ExecuteDataSet(dbCommand); [Visual Basic] Dim db As Database = DatabaseFactory.CreateDatabase() Dim dbCommand As DbCommand = db.GetStoredProcCommand("GetProductsByCategory") ' Retrieve products from the category 7. db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, 7) Dim productDataSet As DataSet = db.ExecuteDataSet(dbCommand)
Changes in Enterprise Library 3.1 May 2007
In addition to the changes in the Enterprise Library 3.0 release, connection strings for OLEDB and ODBC connections in the May 2007 release of the Data Access Application Block can now be edited using custom dialog boxes from the configuration tools.
Changes in Enterprise Library 3.0 April 2007
Enterprise Library 3.0 April 2007 supports SQL Server Compact Edition (CE). SQL Server CE provides the essential features of a relational database and is intended for desktop and mobile applications that need a local data store but do not require the full functionality of SQL Server.
There have been changes to some of the Database class methods in order to take advantage of the .NET Framework 2.0 TransactionScope class. This class automatically enlists database calls into an ambient transaction. In this release of the Data Access Application Block, the Database class methods, such as ExecuteDataSet, recognize when a TransactionScope instance is active and they enlist database calls in this transaction
There is a new overload of the Database.UpdateDataSet method that takes an updateBatchSize parameter. Setting the UpdateBatchSize parameter to a positive integer value causes the DataAdapter object to send updates to the database as batches of the specified size. This reduces the number of database round trips.
To run the Data Access Application Block, you need a database server running a database that is supported by a .NET Framework 2.0 data provider (in addition to the system requirements described on the Enterprise Library page). This includes SQL Server 2000 or later and Oracle 9i. The database server can also run a database that is supported by the .NET Framework 2.0 data providers for OLE DB or ODBC.
The application block was designed to achieve the following goals:
- Encapsulate the logic used to perform the most common data access tasks.
- Eliminate common coding errors, such as failing to close connections.
- Relieve developers of the need to write duplicate code for common data access tasks.
- Reduce the need for custom code.
- Incorporate best practices for data access, as described in the .NET Data Access Architecture Guide.
- Ensure that, as far as possible, the application block functions work with different types of databases.
- Ensure that applications written for one type of database are, in terms of data access, the same as applications written for another type of database.
Figure 1 illustrates the interrelationship between the key classes in the Data Access Application Block.
Figure 1. Design of the Data Access Application Block
Assuming that the client code is using the application block configuration information, it calls the static CreateDatabase method on the DatabaseFactory class to create instances of the Database object. The DatabaseFactory class uses configuration information found in the configuration file to determine the specific Database object type to construct and return to the application. The factory uses the following criteria to determine the Database object type to construct:
- If the client code passes a database instance name that identifies a connection string in the configuration file, the factory uses that string to create the Database object.
Note The .NET Framework classes now maintain the connection strings in the connectionStrings section of the configuration file. This means that connection string definitions can be shared among all applications that access the connectionStrings section.
- If the client code does not pass a database instance name, the factory identifies the connection string by the default instance setting in the configuration file. The defaultDatabase attribute in the dataConfiguration configuration section controls the default instance. The following XML fragment from a configuration file shows a default instance that corresponds to the Production connection string.
The <connectionStrings> section in the configuration file maps logical identifiers (names) to connection strings and DbProviderFactory types. The DatabaseFactory object obtains the logical name of the ADO.NET data provider from the connection string. The DatabaseFactory object then calls the .NET Framework DbProviderFactory class to obtain the fully qualified type name for the ADO.NET data provider. The DatabaseFactory object uses this information to determine the type of Database object to construct. Although the .NET Framework considers database provider names in connection strings to be optional, the Data Access Application Block requires them.
The application block configuration code contains default mappings. These map a SqlDatabase object to a System.Data.SqlClient data provider, an OracleDatabase object to a System.Data.OracleClient data provider, and a GenericDatabase object to all other data providers. You can use the configuration console to override the default mappings.
Changing the default mappings or using the configuration console to add new mappings creates a providerMappings section in the configuration file. The providerMappings section maps the DbProviderFactory type to the Database type. You can add your own mapping by creating your own database class and mapping it a DbProviderFactory object.
The abstract base class Database defines the common interface and provides much of the implementation for the data access methods. The SqlDatabase class and OracleDatabase class derive from the Database class. They provide methods to their respective database server systems, which include common functionality that is implemented differently from database to database, as well as functionality unique to that database system. The application block also includes a GenericDatabase class. This class does not provide any database-specific features but operates with any ADO.NET data provider.
The application block supports the dynamic discovery of parameters for stored procedures. This discovery requires a round trip to the database system. The ParameterCache class allows parameter information to be cached, thus avoiding round trips for subsequent invocations of the same stored procedure. (The GenericDatabase class does not support parameter discovery).
The Data Access Application Block has been developed as a result of analyzing common enterprise development challenges and successful solutions to these challenges. However, because each application is unique, you will not find this application block suitable for every application. To evaluate this application block and determine its applicability to your projects, Microsoft suggests you dedicate at least half of a day to explore the application block. The following is a suggested evaluation approach:
- Download Enterprise Library.
- Install Enterprise Library and compile all application blocks and tools.
- Read the "Introduction" and "Scenarios and Goals" sections of the documentation.
- Compile and run the QuickStart samples, and read through the related "QuickStart Walkthroughs" and "Key Scenarios" sections of the documentation.
- If the application block looks like a good fit for your application, try implementing a simple use case in your application or in a throw-away prototype application using the application block.
Enterprise Library, like many patterns & practices deliverables, is associated with a community site. On this community site, you can post questions, provide feedback, or connect with other users for sharing ideas. Community members can also help Microsoft plan and test future deliverables, and download additional content such as extensions and training material.
Questions? Comments? Suggestions? To provide feedback about this application block, or to get help with any problems, please visit the Enterprise Library Community site. The community site is the preferred feedback and support channel because it allows you to share your ideas, questions, and solutions with the entire community.
Enterprise Library is a guidance offering, designed to be reused, customized, and extended. It is not a Microsoft product. Code-based guidance is shipped "as is" and without warranties. Customers can obtain support through Microsoft Support Services for a fee, but the code is considered user-written by Microsoft support staff. For more information about our support policy, see the Enterprise Library home page.
- Application Architecture for .NET: Designing Applications and Services
- .NET Data Access Architecture Guide
- Designing Data Tier Components and Passing Data Through Tiers
- Improving Web Application Security: Threats and Countermeasures
- Improving .NET Application Performance and Scalability
- Enterprise Library