ike most DevX readers, anticipating change is an important part of my job. As a software developer and system architect, it is particularly important that I make sure our middle-tier objects can survive changes in the database-access layer. Although no software design can offer limitless flexibility, the design concepts of Separation of Concerns and Loose Coupling?both discovered during the prehistoric era of structured programming?make designing resilient middle tier classes far easier.

Microsoft incorporates these concepts, along with .NET-specific best practices, in libraries called application blocks. Its Data Access Application Block (DAAB) is an excellent starting point for an examination of the practical advantages that Separation of Concerns and Loose Coupling provide. This article utilizes these two “mega-concepts” in developing an enhanced DAAB, enabling it to not only use a new open-source .NET data provider for the MySQL database engine but also C# delegates to build maximum flexibility into your business objects. In short, you get some practical source code to go with the theory.

Separation of Concerns
In practice, Separation of Concerns really just means congregating all code that pertains to a given task or task domain in the same place. A classic example is the task domain of database access. Consider this all-too-familiar scenario: One of my first tasks at a new job was to search through every single line of the company’s Java code for mishandled database connections. The company’s servers had to be rebooted every 48 hours or so when they ran out of connections.

String sql = "SELECT CustomerID, LastName FROM Customers";

1. Opening and closing the connection
2. Creating queries with inline SQL
3. Managing the returned database objects

The first two steps are downright tragic, and avoiding the last step completely could have created a considerable time benefit.

To demonstrate the benefits of Separation of Concerns and Loose Coupling in DAAB, this article examines a fairly typical middle-tier business object called “Product” (see Listing 1). Although greatly simplified for brevity, it nevertheless clearly illustrates the advantages of both the original DAAB code as well as improvements you can make to it. By virtue of factoring out database access to an isolated data access layer (SqlHelper.cs) , the Product class is clean, simple, and quite resilient in the face of change.

As simple as it is, the Product class nevertheless has a couple of interesting attributes. First, its simplicity is partly due to the fact that it has no database-access code at all. Normal database CRUD operations (create, update, and delete) are reduced to simple calls to SqlHelper methods. Since SqlHelper is a static method library, you don’t even need an instance of the class to use it.

Second, the Product class is extremely resilient. Changes to the database-access layer will not require any changes to the Product class. This was accomplished by doing the following:

• Using the SqlHelper.SetSqlParameter method to create database command parameters. This method returns a database provider-independent reference to the parameter interface, IDbDataParameter. No matter what specific parameter is being created on the other end, your business object dutifully passes it back to SqlHelper when needed.
• Providing a delegate method that is passed an IDataReader reference (in this case, the GetInstance and ProcessReader methods). IDataReader is the interface that both MySqlDataReader and SqlDataReader must implement.

  The delegate method, ProcessReader, contains only the logic that must be in the Product class definition. That logic includes the columns that must be read using the IDataReader reference. This is really the only thing that SqlHelper cannot do on its own. Nevertheless, using an IDataReader reference instead of SqlDataReader greatly reduces the level of coupling. This small feature allows the Product class to be used by virtually any .NET-compatible data provider instead of just SQL Server (See Sidebar 1: Why Even True-blue Microsoft Shops Should Care About Loose Coupling).

The Product class code should raise a couple questions, including, “Where’s the base class, BusinessBase, and why does Product need it?” Listing 2 shows the code for BusinessBase.

Although BusinessBase applies some business logic regarding the care and feeding of connection strings, why would you want every business object instance to maintain its own connection string? The simple answer is Loose Coupling. If business objects pick up their database connection information from keys in config files (a most common practice), then their implementations are tightly coupled to the database connection pools that the config file keys indicate.

This is usually not a problem, but in large, robust systems several database connection pools are often in use at once, each pool having different attributes?and maybe even pointing to different servers. It would be efficient if any given instance of your Product class could be used for any connection pool. Making the connection string a property of the base class enables that.

A Little DAAB Will Do Ya
Before tweaking the DAAB to accommodate the MySQL database engine, download and install the open-source .NET data provider for MySQL from SourceForge.

As good as MySQL is, it is definitely not a drop-in replacement for SQL Server. For example, the current version does not yet have stored procedures, although they are slated for the next major release (version 5.0). Even so, you can make your DAAB modifications “fake” stored procedures in MySQL (see the ReadMe file in the source code download for details). This lets your business object code look the same regardless of which database you’re using, and should also greatly ease the pain of upgrading to MySQL 5.0. It also lets you get raw SQL out of your C# code, thereby reducing the degree of coupling there as well.

The SqlHelper class in the sample project is not a complete rewrite of the SqlHelper.cs from DAAB. Only the methods that are required to support the business objects in the middle-tier are altered (see Listing 3).

The following items in SqlHelper are worth noting:

• It catches and then re-throws exceptions. It catches them so that the connection, command, and data objects can be properly closed or disposed. It re-throws them because the action to take in the event of a database error is context-specific. If user-interface code calls the middle-tier, SqlHelper likely should respond differently than if a Windows service running a daily task calls it.

  That having been said, if you have a policy of logging all database errors (in the Windows Event Log, for example), you may want to include code for that in the exception catch handlers for ExecuteReader and ExecuteScalar.

• The method for simulating stored procedures is to move the actual SQL for each command to the project’s configuration file. The config key name becomes the stored procedure name. When MySQL supports stored procedures, you can move the SQL to the database. You then have to modify only the ExecuteReader and ExecuteScalar methods; the calling business objects are completely insulated from the change.
• Declare a delegate prototype called DataReaderDelegate:

  public delegate object DataReaderDelegate(string connStr, IDataReader reader);

  Instantiate this delegate in business objects in methods that need to read the object’s data from a returned row.

  Don’t forget that the underlying query can return columns from multiple tables. As such, you can create quite complex compound objects using the same simple code that the Product class employs.

The sample project in the source code download creates a console application that exercises the CRUD functions of the Product class. Your effort results in some startlingly high-level code. To get a Product instance and populate it with data (given the product ID), you need only input the following:

Product p = Product.GetInstance(connStr, productID);	

To create a new product and save it to the database is just as simple:

Product p = new Product(connStr, "New Product", "Product Description",                         5.50f);p.Add();

Architect for Change
By enabling Microsoft’s Data Access Application Block to use the MySQL database engine, you decrease the level of coupling in the DAAB code itself. You also enable the creation of highly resilient middle-tier objects that adapt well to changes in the database-access layer.