Versioned Database Access

• Download demo project - 23.9 Kb
• Download source - 7.2 Kb

Introduction

This code provides the ability to specify which version of a database a class can access. It uses the abstract factory [Gamma et al, 1995, p87] pattern to create data access objects based on version information retrieved from the database and custom attributes assigned to each class.

Background

Whenever I create a database, one of the first things I do is write a stored procedure that returns version information. That way, as the database changes I can update the major, minor or revision number in the stored procedure to reflect the type of change.

CREATE PROCEDURE dbo.uspVersion(@systemName SYSNAME = NULL OUTPUT, 
    @major INT = NULL OUTPUT,
    @minor INT = NULL OUTPUT, 
    @revision INT = NULL OUTPUT, 
    @showOutput BIT = 1) AS
BEGIN
    SET NOCOUNT ON

    SELECT @systemName = 'Version Database System',
        @major = 1,
        @minor = 0,
        @revision = 0

    IF @showOutput = 1
    BEGIN
        SELECT @systemName AS [System Name], @major AS Major,
            @minor AS Minor, @revision AS Revision
    END
END

In applications that access the database, I check the database version after connecting in order to make sure that the system name is the expected value. Then, using the version number, I decide which class to use to access the data. This has allowed me to release new software that can use upgrades to the database, but only if the database is a particular version or above. If the new software fails for whatever reason, all I have to rollback is the software implementation and not the database as well. Once I'm happy that the new software is working well, I can perform my database upgrade and the new software should start using the new features. In the past, I achieved this using hard-coded switch statements in Delphi. Now that I'm using C#, I think that the .NET platform's strong type information, custom attributes and reflection might provide a more flexible approach.

Using the code

The code works by assigning a custom attribute, VersionAttribute, to data access classes. When you create a data access object using one of the abstract factory MakeXXXX methods, it returns a proxy object. The proxy performs the connection to the database and retrieves the version information. Using this information it then searches through the assembly to find a class with VersionSpecificAttribute for the database version. You can specify a full version number for a class, Major.Minor.Revision, or you can specify that it is the generic class for a particular version, Major.Minor.AnyRevision.

If the search algorithm cannot find a specific class, it will use the generic one. If it can't find a generic class for the Major.Minor version, then an exception will be thrown. This is intentional. If you make a change to the database that requires an update to the major or minor version number, you should at least check your existing code to ensure its functionality. If no change is required, simply add the generic VersionSpecificAttribute for the version and rebuild.

The Versioned.Data project provides the classes to create a versioned data access layer, while the demo projects Rainbow.Data and Rainbow.Data.Sql are the data access layer for the Rainbow database. The VidbTest project is a simple Windows Forms test application to retrieve product information from the fictional Rainbow system.

Versioned DataAccess library: Versioned.Data

The Versioned.Data project provides all of the base functionality to create a versioned data access layer. The first step is to create a versioned connection object specific to your system. Derive from the VersionedDbConnection class and override the ConnectToDatabase and RetrieveDatabaseVersion methods. The DefaultVersion utility class provides methods to retrieve the database version using the store procedure created above. If you use other methods, you'll have to provide the correct implementation.

Secondly, create a data access table class derived from TableProxy and specify the implemented type, usually the data table interface. This is the object you create whenever you want to access the database. Lastly, you create the version-specific data access table derived from the DataTable class and apply VersionSpecificAttribute. Instances of this class perform the actual data access for a specific version of the database.

That's it. I recommend that your base class be the version-generic data table and that the specific ones derive from it and override the methods where the functionality has changed. When the major or minor version changes, I recommend creating a completely new class because major or minor version changes usually break functionality rather than extend it.

The demo project layout

Just a quick word on the demo project: I tend to lay all my data access layers out in the same way. That's how I'm able to use the versioned data access library easily. I have an abstract data layer with an abstract factory object that creates the data access tables. Then I create an implementation project for the data access layer of a specific database or technology, such as ADO.NET.

These separations allow me to easily change the data access layer from, say, Microsoft SQL Server to Oracle by implementing a new concrete data access factory. I plan to post another article describing code to dynamically change the concrete data access factory at runtime, so keep your eyes peeled. Although these separations are not strictly required to use the versioned data access code, I strongly recommend using them because I believe that the design fulfils the OCP (Open Closed Principle [Martin, 2003, p99]).

To use the demo project, you will need to create the Rainbow database. It is an SQL Server database and the scripts are provided in the SqlServer.Scripts filter of the demo solution. I have also provided scripts to upgrade or downgrade to the various database versions. I recommend that you create the database and upgrade/downgrade whilst running the test application to check the results.

Bibliography

• Gamma, Erich and Helm, Richard and Johnson, Ralph and Vlissides John, Design Patterns: Elements of Reusable Object-Oriented Software, Addison-Wesley, Indianapolis, 1995
• Martin, Robert C, Agile Software Development: Principles, Patterns and Practices, Prentice Hall, New Jersey, 2003

History

• 18/07/2007 - Initial version
• 20/07/2007 - Added bibliography