Visual Basic

Data Access Layer

The most common question I get asked by Visual Basic developers is, "What type of DAL should I use? Data Access Objects (DAO), Open Database Connectivity (ODBC) API, ODBCDirect, Remote Data Objects (RDO), ActiveX Data Objects (ADO), VBSQL, vendor-specific library, smoke signals, semaphores, Morse code, underwater subsonic communications?" The answer is never easy. (Although in windier climates I would advise against smoke signals.)

What I do think is a good approach to data access is to create a simple abstract data interface for your applications. This way, you can change your data access method without affecting the other components in your application. Also, a simple data interface means that developers have a much lower learning curve and can become productive more quickly.

Remember that we are trying to create a design pattern for business object development in corporate database-centric applications. These applications typically either send or receive data or they request a particular action to be performed; as such, a simple, straightforward approach is required. I like simplicity-it leads to high-quality code.

So am I saying don't use ADO? No, not at all. Most Microsoft-based solutions are well suited to using ADO inside the DAL. But by concentrating your ADO code (or any database connection library for that matter) into one component, you achieve a more maintainable, cohesive approach. Besides, ADO is still a complex beast. If you take into account that many of ADO's features are restricted by the type of database source (such as handling text fields and locking methodologies), you'll find that the benefits of a stable, easily understood DAL far outweigh the cost of slightly reinventing the wheel.

Not only is abstracting the data access methodology important, but abstracting the input and output from this component are important as well. The DAL should return the same data construct regardless of the database source.

But enough theoretical waffling-let's look at something real.

Data Access Layer Specifics

The DAL presented here is essentially the same DAL that TMS uses in enterprise applications for its clients. I'll describe the operation of the DAL in detail here, but I'll concentrate only on using a DAL, not on building one. Remember that we want to concentrate on business object development, so this is the data object that our business objects will interface with.

The DAL below consists of only six methods. All other aspects of database operation (for instance, cursor types, locking models, parameter information, and type translation) are encapsulated and managed by the DAL itself.

cDAL Interface

Member Description
OpenRecordset Returns a recordset of data
UpdateRecordset Updates a recordset of data
ExecuteCommand Executes a command that doesn't involve a recordset
BeginTransaction Starts a transaction
CommitTransaction Commits a transaction
RollbackTransaction Rolls back a transaction

What's all this recordset malarkey? Well, this is (surprise, surprise) an abstracted custom recordset (cRecordset). We must investigate this recordset carefully before we look at the operation of the DAL itself.