Enterprise Library Data Access Block

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

• Enterprise Library overview
• Data Access Application Block
• When to use the Data Access Application Block
• Enterprise Library for Different Frameworks
• Configuring dotConnect for MySQL as Data Access Block
• .NET Framework 2 Notes
• Examples For .NET Framework 2.0
• Informational Resources

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 MySQL as Data Access Application Block.

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 MySQL, using dotConnect for MySQL might suit you better.

Enterprise Library for Different Frameworks

The Enterprise Library was initially developed to work with .NET Framework 1.1. Main principles introduced in this version are consistent across different releases of the Library. However, the Enterprise Library for .NET Framework 2.0, released in January, 2006, was significantly redesigned. It now takes advantage of the .NET Framework 2.0 functionality. For example, configuration is now based on the .NET Framework System.Configuration namespace, DbCommandWrapper class disappeared in favor of ADO.NET 2.0 DbCommand class.

Configuring dotConnect for MySQL as Data Access Block

The rest of the article demonstrates configuring and using Enterprise Library. The basics are explained as suitable for .NET Framework 1.1 first; difference for .NET Framework 2.0 is explained a bit later. In this article we will consider parts of the DataAccessQuickStart sample found in Program Files\Devart\dotConnect\MySQL\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 MySQL as Data Access Block is adjusting application settings. The file App.config in your sources should look like the following example:

<configuration>
  <configSections>
    <section name="enterpriselibrary.configurationSettings"
             type="Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationManagerSectionHandler,
                   Microsoft.Practices.EnterpriseLibrary.Configuration" />
  </configSections>
  <enterpriselibrary.configurationSettings
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    defaultSection=""
    applicationName="Application"
    xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/configuration">
  <configurationSections>
    <configurationSection
      name="dataConfiguration"
      encrypt="false">
      <storageProvider
        xsi:type="XmlFileStorageProviderData"
        name="XML File Storage Provider"
        path="dataConfiguration.config" />
      <dataTransformer
        xsi:type="XmlSerializerTransformerData"
        name="Xml Serializer Transformer">
        <includeTypes />
      </dataTransformer>
    </configurationSection>
  </configurationSections>
  <keyAlgorithmStorageProvider xsi:nil="true" />
</enterpriselibrary.configurationSettings>
</configuration>

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="MySql"
          type="Devart.Data.MySql.EnterpriseLibrary.MySqlDatabase, Devart.Data.MySql.EnterpriseLibrary" />
      </databaseTypes>
      <instances>
        <instance
          name="DataAccessQuickStart"
          type="MySql"
          connectionString="LocalQuickStart" />
      </instances>
      <connectionStrings>
        <connectionString name="LocalQuickStart">
          <parameters>
            <parameter name="host" value="localhost" isSensitive="false" />
            <parameter name="user id" value="root" isSensitive="false" />
            <parameter name="password" value="root" isSensitive="false" />
            <parameter name="port" value="3306" isSensitive="false" />
            <parameter name="database" value="test" 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 MySQL as Data Access Application Block you have to add reference to Devart.Data.MySql.EnterpriseLibrary.dll assembly. dotConnect for MySQL is shipped with sources of this assembly, so if you need to alter it or add custom functionality, add Devart.Data.MySql.EnterpriseLibrary project to your solution.

.NET Framework 2 Notes

With Enterprise Library for .NET Framework 2.0, the configuration is simplified. All settings are stored in single file, App.config. No other configuration file is needed.

<configuration>
  <configSections>
    <section
      name="dataConfiguration"
      type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
            Microsoft.Practices.EnterpriseLibrary.Data" />
  </configSections>
  <connectionStrings>
    <add
      name="DataAccessQuickStart"
      providerName="dotConnect for MySQL"
      connectionString="host=localhost;
                        database=test;
                        User id = root;
                        password = root;
                        port = 3306" />
  </connectionStrings>
  <dataConfiguration
    defaultDatabase="DataAccessQuickStart">
    <providerMappings>
      <add
      databaseType="Devart.Data.MySql.EnterpriseLibrary.MySqlDatabase,
                    Devart.Data.MySql.EnterpriseLibrary"
      name="dotConnect for MySQL" />
    </providerMappings>
  </dataConfiguration>
</configuration>

The following assemblies need to be referenced by the solution: Microsoft.Practices.EnterpriseLibrary.Data and Microsoft.Practices.EnterpriseLibrary.Common. The assembly EnterpriseLibrary.Configuration is no longer required. The rest of configuring is similar to .NET Framework 1.1.

Examples For .NET Framework 1.1

In this section we will analyze adapted fragments of DataAccessQuickStart sample.

Depending on configuration, when you first open a database connection you may experience long timeouts with application not responding. You may also get errors or event log messages like these:
  Failed to create instances of performance counter...
  Failed to fire the WMI event 'DataConnectionOpenedEvent'...
  Requested registry access is not allowed...
This happens because Enterprise Library tries to use WMI events, Event Log events and performance counters. To disable this behavior you need to recompile Enterprise Library with USEWMI, USEEVENTLOG and USEPERFORMANCECOUNTER constants removed from Configuration Properties\Build\Conditional Constants property page. Note that in Enterprise Library for .NET Framework 2 all instrumentation is disabled by default.

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.

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 helper object
  DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(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(dbCommandWrapper)) {
    // 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 combined string
  return readerData.ToString();
}

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

public DataSet GetProductsInCategory(int Category) {

  Database db = DatabaseFactory.CreateDatabase();
  string sqlCommand = "Select * From Products Where CategoryID = :CategoryID";
  DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
  // Create and add integer input parameter
  dbCommandWrapper.AddInParameter("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(dbCommandWrapper);
  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.

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";
  DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
  // Declare table name to refer later
  string productsTable = "Products";
  // Retrieve the initial data
  db.LoadDataSet(dbCommandWrapper, 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
  DBCommandWrapper insertCommandWrapper = db.GetSqlStringCommandWrapper("Insert Into Products "+
    + "(ProductName, CategoryID, UnitPrice) Values (:ProductName, :CategoryID, :UnitPrice)");
  insertCommandWrapper.AddInParameter("ProductName", DbType.String, "ProductName", DataRowVersion.Current);
  insertCommandWrapper.AddInParameter("CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
  insertCommandWrapper.AddInParameter("UnitPrice", DbType.Decimal, "UnitPrice", DataRowVersion.Current);
  // Update the DataSet, capturing the number of rows that were affected
  int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", insertCommandWrapper, 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.

Examples For .NET Framework 2.0

Same examples have different implementation in Enterprise Library for .NET Framework 2.0

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.

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.

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.

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

• The homepage to start from is http://msdn.microsoft.com/practices/. It contains download information and links to other useful locations.
• In July, 2005 MSDN Magazine published an article dedicated to Enterprise Library: http://msdn.microsoft.com/msdnmag/issues/05/07/DataPoints/.
• If you are going to use Enterprise Library with .NET Framework 2, take a look through corresponding page published in January, 2006: http://msdn.microsoft.com/library/en-us/dnpag2/html/EntLib2.asp.
• Finally, after you install the library, refer to Visual Studio Integrated Help Collection for detailed information on every aspect of the product.
• If you encounter problems using it, make sure you are acquainted with FAQ: http://channel9.msdn.com/wiki/default.aspx/Channel9.EnterpriseLibraryFAQ.

 

 

---

© 2002-2008 Devart. All rights reserved.