Enterprise Library Data Access Block

dotConnect for Oracle can be used in conjunction with Microsoft Enterprise Library Data Access Application Block, which boosts development productivity in many cases. This topic describes what is Enterprise Library, what part of it can be substituted with dotConnect for Oracle, and how to use the bundle in your applications. The topic consists of following sections:

The first three sections contain general information on Enterprise Library and its benefits. You can safely skip it if you are acquainted with the matter. The last section practically demonstrates how to start using dotConnect for Oracle as Data Access Application Block.

This feature is available in Professional and Developer Editions only.

Enterprise Library Overview

Enterprise Library is a set of application blocks - reusable software components designed to help developers with usual enterprise development tasks.

Enterprise Library is designed for:

  • Consistency. All Enterprise Library application blocks feature consistent design patterns and implementation approaches.
  • Extensibility. All application blocks allow developers to customize the behavior by adding own code.
  • Ease of use. Graphical configuration tool, simpler installation procedure, clearer and more complete documentation and samples.
  • Integration. Application blocks are designed to work well together and are tested to make sure that they do.

Application blocks help solve common problems that developers face from one project to another. They have been designed to encapsulate the Microsoft recommended best practices for .NET applications. Application blocks can be added to applications quickly and easily.

Data Access Application Block

The Enterprise Library Data Access Application Block simplifies 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 supports both stored procedures and in-line SQL. Common maintenance 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. The Block exposes a model that supports encapsulation of database-specific features, which allows applications to be ported from one database type to another without modifying the client code.

The Data Access Application Block provides the following benefits:

  • It leverages the functionality provided by ADO.NET and allows you to use ADO.NET together with the application block.
  • 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.

When to Use the Data Access Application Block

The Data Access Application Block includes a small number of methods that simplify the most common methods of accessing a database. Each method encapsulates the logic required to retrieve the data and manage the connection to the database. You should consider using the application block if your application uses standard data access techniques.

The application block supplements the code in ADO.NET that allows you to use the same code with different database types. The GenericDatabase class allows you to use the application block with any configured ADO.NET DbProviderFactory object.

The Data Access Application Block is a complement to ADO.NET; it is not a replacement. The application block provides simplicity and convenience while helping developers use ADO.NET with best practices. If your application needs to retrieve data in specialized way, or if your code needs customization to take advantage of features specific to Oracle, using dotConnect for Oracle might suit you better.

Configuring dotConnect for Oracle as Data Access Block

The rest of the article demonstrates configuring and using Enterprise Library. In this article we will consider parts of the DataAccessQuickStart sample found in Program Files\Devart\dotConnect\Oracle\EnterpriseLibrary\ folder. You can study it in conjunction with this article. The sample represents integral project that demonstrates primary techniques, while the article explains most important points.

The first thing to do in using dotConnect for Oracle as Data Access Block is adjusting application settings. All settings are stored in single file, App.config. No other configuration file is needed. This file (App.config) in your sources should look like the following example:

<configuration>
  <configSections>
    <section
      name="dataConfiguration"
      type="
        Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
        Microsoft.Practices.EnterpriseLibrary.Data"/>
  </configSections>
  <connectionStrings>
    <add
      name="DataAccessQuickStart"
      providerName="Devart.Data.Oracle"
      connectionString="server=localhost;
                        User id = scott;
                        password = tiger; />
  </connectionStrings>
  <dataConfiguration
    defaultDatabase="DataAccessQuickStart">
    <providerMappings>
      <add
      databaseType="Devart.Data.Oracle.EnterpriseLibrary.OracleDatabase,
                    Devart.Data.Oracle.EnterpriseLibrary"
      name="dotConnect for Oracle" />
    </providerMappings>
  </dataConfiguration>
</configuration>

The following assemblies need to be referenced by the solution: Microsoft.Practices.EnterpriseLibrary.Data and Microsoft.Practices.EnterpriseLibrary.Common.

After compilation App.config is renamed to YourAppName.exe.config where YourAppName.exe is name of the executable file, and placed next to the file. Application reads the file when it is loaded. In the fragment presented the XML tree contains some mandatory general information (file types and schemas), and controls whether encryption should be used (disabled in this case).

Note that for web applications and web services metaconfiguration file is web.config in the root directory of your web site or virtual directory.

The most important line in metaconfiguration file is <storageProvider ... path="dataConfiguration.config" />. It points to configuration file that controls Data Access Application Block, which, in turn, should be something like this:

<?xml version="1.0" encoding="utf-8"?>
<dataConfiguration>
  <xmlSerializerSection
    type="
      Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
      Microsoft.Practices.EnterpriseLibrary.Data">
    <enterpriseLibrary.databaseSettings
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/data">
      defaultInstance="DataAccessQuickStart"
      <databaseTypes>
        <databaseType
          name="Oracle"
          type="Devart.Data.Oracle.EnterpriseLibrary.OracleDatabase, 
		        Devart.Data.Oracle.EnterpriseLibrary" />
      </databaseTypes>
      <instances>
        <instance
          name="DataAccessQuickStart"
          type="Oracle"
          connectionString="LocalQuickStart" />
      </instances>
      <connectionStrings>
        <connectionString name="LocalQuickStart">
          <parameters>
            <parameter name="server" value="localhost" isSensitive="false" />
            <parameter name="user id" value="scott" isSensitive="false" />
            <parameter name="password" value="tiger" isSensitive="false" />
          </parameters>
        </connectionString>
      </connectionStrings>
    </enterpriseLibrary.databaseSettings>
  </xmlSerializerSection>
</dataConfiguration>

The latter XML is of much more interest to database developer because it actually contains data used to connect to database. In Enterprise Library you handle databases with Microsoft.Practices.EnterpriseLibrary.Data.Database class, which is initialized according to this configuration file. The file describes possible database instances that can be created in application.

In Data Access Application Block obtaining handle to database is as simple as following statement:
Database db = DatabaseFactory.CreateDatabase();
If an argument was not supplied to the method it searches configuration XML for defaultInstance attribute of enterpriseLibrary.databaseSettings node. Its value is name of the instance to use. This instance must be described in instances node. Type of the instance determines what provider to use (enumerated in databaseTypes) and name of connection string. The connection string itself is defined in connectionStrings node. Its arguments are self-explanatory.

This is how Data Access Application Block initializes database connections. Single configuration file can describe multiple data providers, connection strings and databases. You can refer to certain database in your code directly:
Database db = DatabaseFactory.CreateDatabase("myInstanceName");

Microsoft provides a GUI tool to create and configure visually the files App.config and dataConfiguration.config. You can find in Start menu shortcut Enterprise Library Configuration to the file EntLibConfig.exe. This tool also allows you to setup encryption for the configuration file.

Note that unlike App.config, dataConfiguration.config file is not copied automatically to output directory. You can do it manually, or run appropriate command line in a Post-build event of your project. Another way to make sure that configuration is up to date is to hardcode full path in App.config file.

The following assemblies need to be referenced by the solution: Microsoft.Practices.EnterpriseLibrary.Data, Microsoft.Practices.EnterpriseLibrary.Common and Microsoft.Practices.EnterpriseLibrary.Configuration. They are typically located in bin folder of Enterprise Library like \Program Files\Microsoft Enterprise Library\bin\. Add namespace Microsoft.Practices.EnterpriseLibrary.Data to using (Imports in Visual Basic) clause of your sources to have important classes visible (for example, Database and DBCommandWrapper).

To use dotConnect for Oracle as Data Access Application Block you have to add reference to Devart.Data.Oracle.EnterpriseLibrary.dll assembly. dotConnect for Oracle is shipped with sources of this assembly, so if you need to alter it or add custom functionality, add Devart.Data.Oracle.EnterpriseLibrary project to your solution.

Examples

The following routine returns list of customers from corresponding table. It returns the list as string object where individual customers are separated with line breaks.

[C#]
public string GetCustomerList() {

  // Create the Database object, using the default 
  // database service as described above.
  Database db = DatabaseFactory.CreateDatabase();
  // Define SQL query to retrieve the data.
  string sqlCommand = "Select Name From Customers";
  // Create ADO.NET DbCommand object
  DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
  // Create intermediate data holder
  StringBuilder readerData = new StringBuilder();
  // DataReader that will hold the returned results
  // The ExecuteReader call will request the connection to be closed upon
  // the closing of the DataReader. The DataReader will be closed
  // automatically when it is disposed.
  using (IDataReader dataReader = db.ExecuteReader(dbCommand)) {
    // Iterate through DataReader
    while (dataReader.Read()) {
      // Get the value of the 'Name' column in the DataReader
      readerData.Append(dataReader["Name"]);
      readerData.Append(Environment.NewLine);
    }
  }
  return readerData.ToString();
}

The next example demonstrates usage of parameters. The routine returns DataSet that contains all products within given category.

[C#]
public DataSet GetProductsInCategory(int Category) {

  Database db = DatabaseFactory.CreateDatabase();
  string sqlCommand = "Select * From Products Where CategoryID = :CategoryID";
  DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
  // Create and add integer input parameter
  db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);
  // DataSet that will hold the returned results
  DataSet productsDataSet = null;
  // Open connection, execute the query and close connection
  productsDataSet = db.ExecuteDataSet(dbCommand);
  return productsDataSet;
}

The next example shows how to modify a DataSet. The routine retrieves data, inserts new row into table and updates changes. You can alter or delete existing rows in same way.

[C#]
public int UpdateProducts() {

  Database db = DatabaseFactory.CreateDatabase();
  // Create DataSet that receives initial data
  DataSet productsDataSet = new DataSet();
  string sqlCommand = "Select ProductID, ProductName, CategoryID, 
                       UnitPrice, LastUpdate From Products";
  DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
  // Declare table name to refer later
  string productsTable = "Products";
  // Retrieve the initial data
  db.LoadDataSet(dbCommand, productsDataSet, productsTable);
  // Get the table that will be modified
  DataTable table = productsDataSet.Tables[productsTable];
  // Add a new product to existing DataSet
  DataRow addedRow = table.Rows.Add(new object[] { 
                       DBNull.Value, "New product", 11, 25 });
  // Setup Insert command
  DbCommand insertCommand = db.GetSqlStringCommand("Insert Into Products " +
    "(ProductName, CategoryID, UnitPrice) Values (:ProductName, 
	                                          :CategoryID, :UnitPrice)");
  db.AddInParameter(insertCommand, "ProductName", DbType.String, 
                                   "ProductName", DataRowVersion.Current);
  db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, 
                                   "CategoryID", DataRowVersion.Current);
  db.AddInParameter(insertCommand, "UnitPrice", DbType.Decimal, 
                                   "UnitPrice", DataRowVersion.Current);
  // Update the DataSet, capturing the number of rows that were affected
  int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", 
                                      insertCommand, null,
                                      null, UpdateBehavior.Standard);
  return rowsAffected;
}

More examples can be found in DataAccessQuickStart demo project. You can open it in your IDE and explore other features of Enterprise Library.

Informational Resources

Back to list