dotConnect Universal can be used in conjunction with Microsoft Enterprise Library Data Access Application Block, which allows to boost development productivity in many cases. This article describes what is Enterprise Library, what part of it can be substituted with dotConnect Universal, and how to use the bundle in your applications. The article 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 Universal as Data Access Application Block.
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:
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.
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:
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.
Note that 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.
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\Universal\Samples\ 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.
As usual, dotConnect Universal requires another data provider to perform access to actual data. In the article we will use dotConnect for MySQL for this role. So, the whole architecture looks like this:
Your code <-> Enterprise Library <-> dotConnect Universal <-> dotConnect for MySQL <-> MySQL server
The first thing to do in using dotConnect Universal 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="dataConfiguration"
type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings,
Microsoft.Practices.EnterpriseLibrary.Data" />
</configSections>
<connectionStrings>
<add
name="DataAccessQuickStart"
providerName="Devart.Data.Universal"
connectionString="provider=MySQL
host=localhost;
database=test;
User id = root;
password = root;
port = 3306" />
</connectionStrings>
<dataConfiguration
defaultDatabase="DataAccessQuickStart">
<providerMappings>
<add
databaseType="Devart.Data.Universal.EnterpriseLibrary.UniDatabase,
Devart.Data.Universal.EnterpriseLibrary"
name="dotConnect Universal Data Provider" />
</providerMappings>
</dataConfiguration>
</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 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 to setup encryption for the configuration file.
The following assemblies need to be referenced by the solution: Microsoft.Practices.EnterpriseLibrary.Data and Microsoft.Practices.EnterpriseLibrary.Common. 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 Universal as Data Access Application Block you have to add reference to Devart.Data.Universal.EnterpriseLibrary.dll assembly. dotConnect Universal is shipped with sources of this assembly, so if you need to alter it or add custom functionality, add Devart.Data.Universal.EnterpriseLibrary project to your solution.
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 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.