Data Points

The Enterprise Library Data Access Application Block, Part 1

John Papa

Contents

Enterprise Library and the Seven Blocks
Connection Management
Configuration Settings
Security
The Factory Pattern
On Deck

A solid data access later (DAL) can benefit an application by hiding redundant tasks, helping handle exceptions more gracefully, helping clean up resources more efficiently, and providing a layer of abstraction from the database. When you want to install a DAL in your architecture, you need to either find or build a data access component. Building one takes precious time away from a project's design, development, and testing timeline. What if you could find one that was already tested, follows best practices, and has full source available? If you are currently considering a DAL, you should take a look at the Enterprise Library Data Access Application Block (DAAB). This Application Block was created by the Microsoft® Patterns and Practices group, and since it ships with all of the source and relevant unit tests, the code can be enhanced or modified to suit different needs.

If you are like me, you are skeptical of any pre-built component but still open to the possibility. I brought my skepticism into an examination of the DAAB last summer when I had the opportunity to get an early glimpse of Enterprise Library. Since then I have tested and used the Enterprise Library DAAB in a variety of situations to see how it holds up as a DAL in applications.

This month I will share how the Enterprise Library DAAB fits into an architecture as well as some best practices in using the block. I will review the Enterprise Library model and dive right into the DAAB and its configuration and cryptography features, when and where the DAAB can be used, and lots more.

Enterprise Library and the Seven Blocks

The Microsoft Patterns and Practices group has developed several application blocks to perform functions ranging from caching to data access. The goal is to create code libraries that most enterprise solutions require. In January 2005 the Patterns and Practices group released the Enterprise Library. This most recent incarnation of the application blocks includes updated versions of many of the older blocks as well as some new ones.

Note: after you install the Enterprise Library, go to the Start menu and select Programs | Microsoft Patterns and Practices | Enterprise Library | Install Services. This will complete the Enterprise Library installation by creating its specific performance counters. Then you will be able to view the Enterprise Library performance counters via PerfMon. Hopefully, future releases of Enterprise Library will include this step in the installation.

All of the Enterprise Library blocks use the Configuration Application Block as well as a common library. The blocks integrate with each other easily but they are also built to stand independently. For example, Enterprise Library's Logging and Instrumentation Application Block can log messages to an event log or to a file. However, it can also log messages to a database by using the DAAB. All seven of the Enterprise Library blocks contain instrumentation using performance counters, Windows® Management Instrumentation (WMI) events, and Event Log messages. This helps administrators monitor the status of applications through tools like the Event Log, Performance Monitor, or Microsoft Operations Manager (MOM). The seven blocks are:

• Caching Application Block
• Configuration Application Block
• Cryptography Application Block
• Data Access Application Block
• Exception Handling Application Block
• Logging and Instrumentation Application Block
• Security Application Block

At a minimum, these seven Enterprise Library blocks rely on the Configuration Application Block (as shown in Figure 1) and the common library, which contains a limited but commonly required set of functionality. For example, it exposes a bit of cryptography so that the Configuration Application Block can optionally encrypt its entries without having to reference the entire Cryptography Application Block (which references the Configuration Application Block, as do all Enterprise Library blocks).

Figure 1** Enterprise Library's Application Blocks **

The beauty of the Enterprise Library blocks is that you can choose which blocks to use without having to develop them from scratch. It is a best practice to use a DAL in an enterprise solution. Developing and testing one that abstracts ADO.NET and specific database provider objects, and which handles common data practices, takes time. The DAABs save you that time.

Connection Management

Far too often I see applications that have problems with connection state and connection pooling as well as inconsistencies in the way connection strings are stored. While the connection object may have a simple purpose (connect to a data provider), it is often the source of scalability and resource problems. As shown in Figure 2, the Enterprise Library DAAB stores connection strings in its own config file, which can be edited using the Enterprise Library Configuration Tool, making editing and maintaining the configuration settings easier than editing an XML configuration file by hand.

Figure 2** Enterprise Library Configuration Tool **

The DAAB also helps manage connection state for you. For example, if you want to fill a DataSet or insert some rows by executing a stored procedure, the DAAB automatically opens and closes the connection when needed. It uses the open late, close early approach so that connection pooling is maximized. Of course, you could write this code yourself, but it is easy to forget to close the connection. If you use a DataReader, the DAAB's methods will keep the connection open as the DataReader requires. This code leaves the connection open after the ExecuteReader method executes so the resulting IDataReader object can still access the data:

string sSql = "SELECT CustomerID, CompanyName, City, Region " +
    "FROM Customers WHERE City LIKE 'M%'";
Database dbNorthwind = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmdCust = dbNorthwind.GetSqlStringCommandWrapper(sSql);
IDataReader rdrCust = dbNorthwind.ExecuteReader(cmdCust);

This code creates a Database object using the DatabaseFactory object's CreateDatabase method. It then creates a DBCommandWrapper object (which represents the SQL statement) and executes the command using the Database object's ExecuteReader method. This returns an IDataReader object. The DAAB keeps the connection open as required. However, if you replaced the last line of code with the following, the DAAB would have opened and closed the connection for you:

DataSet dsCust = dbNorthwind.ExecuteDataSet(cmdCust);

Configuration Settings

The configuration tool that comes with Enterprise Library provides an easy way to adjust and validate database settings for the DAAB. After you install Enterprise Library, this tool is in Program Files\Microsoft Enterprise Library\bin\EntLibConfig.exe. Like the previous version of the DAAB, Enterprise Library uses config files to store its connection string settings. By doing so, the information can be modified without having to recompile the application.

Once you open the Enterprise Library Config tool, choose File | Open Application from the menu. Then navigate to and select the Web.config file or the App.config file for your application. To create a config file for the DAAB, right-click on the Application node and choose New | Data Access Application Block. Two new complex nodes will be added beneath the Application to represent the Configuration Application Block and the DAAB. (The Configuration Application Block is used to help determine how to store and transform the other Enterprise Library blocks' config files.) This also causes XML to be added to the Web.config (or App.config) that links it to a separate config file called dataConfiguration.config. This file can store database connection string information for multiple connections.

The Enterprise Library Config Tool automatically creates entries for connection strings, database instances, and database types. If you are using SQL Server™, then you do not need to add another entry for the database type. If you want to use Oracle, however, you can add a new entry here and point it to the Oracle data provider. The dataConfiguration.config file allows for multiple database types so you can refer to connections to different database providers within the same application. After you create the new database type by right-clicking on Database Types and selecting New | Database Type, you can select the Oracle data provider by clicking on the ellipses on the right side of the TypeName field (as shown in Figure 3). Or, you can add a different data provider by clicking on the Load an Assembly button.

Figure 3** Type Selector **

The Database Instances node is used to associate one of the database types with one of the connection strings. The name of the database instance is what you use to refer to a particular connection string for a particular provider via code. If you have more than one database instance, make sure you set the DefaultInstanceNode attribute under the root DAAB node. (The default is the database instance that is used by the DatabaseFactory.CreateDatabase method if no argument is passed to it.)

You can edit the connection string settings under the Connection Strings node. By default you get a connection string that has a database, server, and integrated security attributes. For example, you can change the SQL Server instance (localhost), the name of the database (Northwind), and the Integrated Security setting (True). If you don't want to use integrated security, you can add the User ID and Password connection string settings. When you right-click on the connection string node and choose New | Parameter, an attribute is created called Parameter, which you can rename User ID and set its value. You can also add a Password attribute by right-clicking the connection string node and selecting New | Password Parameter. I recommend using integrated security, but if you choose to use SQL Security and pass in the User ID and Password attributes, consider using the Enterprise Library cryptography features to encrypt the connection string.

Security

Database credentials must always be protected. The DAAB stores connection string information in a configuration file, which can then be encrypted. When you choose an encryption algorithm, that algorithm applies to all of the blocks. You can, however, tell each block whether or not it should use the encryption.

To set up encryption, right-click the Encryption Settings node and select New | File Key Algorithm Storage Provider. You can then either create a new key algorithm or choose an existing one. Let's assume you want to create a new one and you click the Next button. You can then select the algorithm you want to use, and generate the algorithm's key in the next window of the wizard. Finally, specify a file name for the file that will store the algorithm's key.

At this point I have created an encryption algorithm key file, but I have not yet told the Enterprise Library Config tool to encrypt the dataConfiguration.config file. To apply the encryption to the block, simply navigate to the Configuration Application Block node and select its dataConfiguration child node. Then, set the Encrypt attribute to True and save your changes. If you then try to open the dataConfiguration.config file, you will not be able to read the file as it is now encrypted (see Figure 4). The DAAB uses the algorithm's key file to decrypt the contents of the file.

Figure 4** dataConfiguration.config Before and After Encryption **

It might seem odd that the cryptography features I demonstrated work without having to use Enterprise Library's formal Cryptography Application Block. The reason that the Cryptography Application Block is not needed in this case is that the common library assembly that the blocks refer to contains this limited cryptography feature without all of the overhead of the entire Cryptography Application Block.

If, instead of Windows Authentication, you choose to use SQL authentication (which means using a specific SQL Server user ID and password combination for each user) keep in mind that if the connection string is different for each user, users will not share the same connection pool. If you use SQL Authentication, consider using a fixed SQL Server account so that the connection string stays the same and you can take advantage of connection pooling.

The Factory Pattern

Before you can retrieve or update data you must first establish a connection to a data provider such as SQL Server. In ADO.NET you create a SqlConnection object, passing a connection string to its constructor. Then you can open the connection and fill a DataSet or retrieve data through a DataReader. If you want to connect to Oracle, you need to create the Oracle-specific connection object. If you want to connect to another data provider, you use its specific connection object or you can use the more generic OleDbConnection. One of the nice features of the DAAB is that you can easily abstract your code from a specific data provider. For example, check out the following code:

private void Foo(string sSql)
{
    string sDatabaseInstance = "NorthwindDBInstance";
    Database db = DatabaseFactory.CreateDatabase(sDatabaseInstance);
    DBCommandWrapper cmdCust = db.GetSqlStringCommandWrapper(sSql);
    IDataReader rdrCust = db.ExecuteReader(cmdCust);
    ...
}

This code creates an instance of the Database-derived object, which in this case will be a SqlDatabase object. One line of code could be modified to make the same CreateDatabase method return an instance of an OracleDatabase object as follows:

string sDatabaseInstance = "MyOracleDBInstance";

The CreateDatabase method of the DatabaseFactory accepts an argument that represents a DatabaseInstance entry in the dataConfiguration.config file. So by changing the database instance, I can change the type of Database object that the DatabaseFactory.CreateDatabase method returns. The cool part is that I do not have to declare a SqlDatabase or an OracleDatabase object. Rather, I can use their base class, the Database object, thus keeping my code abstracted from data provider specificity. All of this is made possible through the Factory pattern that the DAAB uses.

I can also build my own provider and derive it from the DAAB's abstract Database class (you cannot create an instance of it directly because it's abstract). If you crack open the code for Enterprise Library's DAAB and look in the Sql folder, you will see the SqlDatabase class. This class inherits and uses most of the methods of the abstract Database class, but it overrides certain properties and methods. The Database class does not refer to the System.Data.SqlClient namespace, but the SqlDatabase class does. For example, the abstract Database class implements an abstract ParameterToken property that is intended to be overridden in any class that inherits from the Database class. The SqlDatabase class overrides this property to return the @ symbol (the SQL Server-specific character that precedes parameter names). Another example of an overridden method is the GetConnection method. From the SqlDatabase class, this method should return an instance of a SqlConnection object as an IDBConnection.

The DatabaseFactory class is sealed, so it cannot be inherited. Its constructor is private, so the DatabaseFactory cannot be directly instantiated either. This allows me to call the static CreateDatabase method without allowing me to create an instance of the class itself. When you call the CreateDatabase method and pass it a database instance name, the DAAB uses reflection to figure out what specific type of Database object to create. This is all handled under the covers inside of the source of the DAAB and Enterprise Library. Way down in the call stack there is a method in the Configuration Application Block ConfigurationFactory class called CreateObject. This method basically takes an instance of the Type class, which represents the Database-derived class you want to create. It gets the constructor of the class by using the following line of code through reflection:

ConstructorInfo constructor = type.GetConstructor(new Type[] {});

Once it has the constructor, it then tries to invoke the constructor by using reflection once again:

createdObject = constructor.Invoke(null);

In this scenario, the createdObject variable then represents the instance of the SqlDatabase object. There is quite a bit of code surrounding this whole process, but the abstract classes, the classes that derive from them and override their methods and properties, and reflection are at the heart of the Database Factory pattern. Unless you are truly intrigued by the inner workings of the DAAB, you don't need to delve deep into its code. However, if you are as curious as I am, you'll likely find it very interesting.

On Deck

The DAAB exposes several ways to retrieve and modify data using features of ADO.NET. For example, you can execute a stored procedure and return a DataSet using the ExecuteDataSet method or you can execute an UPDATE statement and return nothing by using the ExecuteNonQuery method. The Database class has dozens of overloaded methods that do the following:

• Execute stored procedures or SQL statements
• Return a DataSet, a DataReader, a Scalar value, an XmlReader, or nothing at all
• Allow for specific parameters to be created and passed in
• Determine which parameters that it needs, create them, and cache them
• Involve commands in a transaction

In the next installment of Data Points I will explore many of these features, and demonstrate how to set up a project to use the DAAB and retrieve data in a variety of ways. In the final installment of this three-part series, I will demonstrate techniques used to save data as well as how to take advantage of the DAAB's parameter caching. I will also review the Test Driven Development (TDD) practices that Enterprise Library uses and show how to write TDD code and test it using a tool such as NUnit.

Some advantages of using the DAAB as a DAL are that it reduces repetitive lines of ADO.NET code, provides for database abstraction, and provides instrumentation. The configuration tool, the separation of the config files from the Web.config (or App.config), and the general ability to easily store and encrypt database settings are also good features. The Enterprise Library DAAB contains a lot of code, but if you are not going to need database abstraction and your application is small in scale, you might not want to use it. You might want to consider using ADO.NET directly if you have an application that is not tiered or does not warrant a true DAL. However, if you are building an enterprise solution that needs to be scalable, follow best practices, can secure its connection settings, is used by many developers around the world, or includes database abstraction, or if you want to be able to use instrumentation to monitor the health of your application, Enterprise Library's DAAB is probably a better choice.

Send your questions and comments for John to  mmdata@microsoft.com.

John Papa is a Senior .NET Consultant with ASPSOFT and a baseball fanatic who spends most of his summer nights rooting for the Yankees with his family and his faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive or blogging at codebetter.com/blogs/john.papa.