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="dotConnect for 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