Browse DevX
Sign up for e-mail newsletters from DevX


Design Resilient Business Objects with MS DAAB : Page 3

Decrease the level of coupling in the Microsoft Data Access Application Block code by enabling it to use the MySQL database engine. With this technique, you can create highly resilient middle-tier objects.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Ross Lambert is a senior software developer and system architect team member for a telecommunications company. A former editor, columnist, and freelance writer during the roaring 90s, Ross was also president and founder of a small software house that created developer tools. or visit his Web site.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date