Update a database from code

• Download source code - 6.84 KB

Introduction

Keeping a database's structure up to date can be a rather complicated task. If we are maintaining a web server, then it's at first sight a simple task of updating the database when the server is updated. But what to do when the web server is the actual product delivered to customers? Another problem is perhaps more obvious, the product is an application that gets regular updates with bug fixes or new features. This article describes a design to solve this problem.

Using the code

The solution is rather simple, but it requires a great deal of work from the development team. First of all, each release (public as well as internal) must be tagged with a version number. Then each database change must be independent and associated with a particular version number. Each and every database update must be documented with a SQL update script. This is the rule to follow:

It must be possible to gradually update the database, starting with the first create script, and then applying each update script sequentially.

When the application starts, it checks the database's version and compares it with the known latest version. If the database's version is older than the latest version, then the database is updated until its version matches the latest version. Each individual update (e.g., from "1.2" to "1.3") is represented by a class inheriting a base update class. The purpose of the class is to make structural changes to the database (e.g., add a new table or remove a column from an existing table) or update the data (e.g., due to some repaired bug) and finally set the database version.

The base update class DbUpdate contains two virtual methods that shall be inherited by the classes that implements the update SQL scripts. The base class also includes methods to execute the SQL scripts and a method to change the version of the database.

public class DbUpdate
{
    protected List<string> m_UpdateSQLdata = new List<string>();
    protected string m_ConnectionString = string.Empty;
    SqlConnection m_Connection = null;

    public DbUpdate() { }

    virtual public int PerformUpdate()
    {
        try
        {
            m_Connection = new SqlConnection(m_ConnectionString);
            m_Connection.Open();

            foreach (string SQLstr in m_UpdateSQLdata)
            {
                ExecuteSQLStatement(SQLstr);
            }
        }
        catch(Exception e)
        {
            System.Diagnostics.Debug.WriteLine(e.ToString());
            return -1;
        }
        finally
        {
            if (m_Connection != null)
            {
                m_Connection.Close();
            }
        }

        return 0;
    }

    virtual protected void CollectUpdateData()
    {
        throw new NotImplementedException();
    }

    protected void ExecuteSQLStatement(string sqlStr)
    {
        SqlCommand command = new SqlCommand(sqlStr, m_Connection);
        command.ExecuteNonQuery();
    }

    protected void SetNewDatabaseVersion(string version)
    {
        m_UpdateSQLdata.Add(string.Format(
          "update Settings set [value] = '{0}' where [key] = 'DB_VERSION';", 
          version));
    }
}

The update classes inherit the base class and implements the two virtual methods PerformUpdate() and CollectUpdateData().

public class DbUpdate10 : DbUpdate
{
    public DbUpdate10(string connectionString)
    {
        this.m_ConnectionString = connectionString;
    }

    public override int PerformUpdate()
    {
        System.Diagnostics.Debug.WriteLine("Upgrading database 1.0 to 1.1");
        CollectUpdateData();

        // Here you can create a new connection
        // and make changes to the data in the database.
        // E.g. open a dialog to ask for data or re-calculate data.

        return base.PerformUpdate();
    }

    protected override void CollectUpdateData()
    {
        SetNewDatabaseVersion("1.1");
        // 1.
        m_UpdateSQLdata.Add("alter table table_a add test varchar(10);");
    }
}

The class DbUpdateManager performs the update work and can also be used to check if the current database already is up to date. The method DoUpdate() is called once and takes care of the work to update the database to the latest version. The method can update a database from any state and not only from the latest state, i.e., it can update from version 1.1 to 1.6.

public class DbUpdateManager
{
    private DbUpdateResult m_UpdateResult = DbUpdateResult.NotStarted;
    private string m_ConnectionString;

    public DbUpdateManager(string connectionString)
    {
        m_ConnectionString = connectionString;
    }

    public DbUpdateResult DoUpdate()
    {
        try
        {
            m_UpdateResult = DbUpdateResult.NoUpdateNeeded;
            Version currentVersion = GetDatabaseVersion();
            DbUpdate.DbUpdate updateObject = 
              DbUpdateFactory.GetUpdater(currentVersion, m_ConnectionString);
            while (updateObject != null)
            {
                int res = updateObject.PerformUpdate();
                if (res == -1)
                {
                    // Error
                    m_UpdateResult = DbUpdateResult.Error;
                    break;
                }
                Version newVersion = GetDatabaseVersion();
                updateObject = DbUpdateFactory.GetUpdater(newVersion, m_ConnectionString);
                m_UpdateResult = DbUpdateResult.UpdateDone;
            }
            return m_UpdateResult;
        }
        catch
        {
            m_UpdateResult = DbUpdateResult.Error;
            return m_UpdateResult;
        }
    }

    public bool IsUpdateNeeded()
    {
        try
        {
            m_UpdateResult = DbUpdateResult.NoUpdateNeeded;
            Version currentVersion = GetDatabaseVersion();
            DbUpdate.DbUpdate updateObject = 
              DbUpdateFactory.GetUpdater(currentVersion, m_ConnectionString);
            return updateObject != null;
        }
        catch
        {
            m_UpdateResult = DbUpdateResult.Error;
            return false;
        }
    }

    public Version GetDatabaseVersion()
    {
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(m_ConnectionString);
            connection.Open();
            SqlDataReader reader = null;
            SqlCommand myCommand = new SqlCommand(
              "select [value] from Settings where [key] = 'DB_VERSION'", 
              connection);
            reader = myCommand.ExecuteReader();
            string version = "0.0";
            while (reader.Read())
            {
                version = reader["value"].ToString();
            }
            reader.Close();
            return new Version(version);
        }
        finally
        {
            if (connection != null)
            {
                connection.Close();
            }
        }
    }
}

The only class that knows which version of the database is the latest is the factory, i.e., DbUpdateFactory. The factory returns a new update class until the version number is no longer matched, meaning that the database is up to date.

public class DbUpdateFactory
{
    public static DbUpdate.DbUpdate GetUpdater(Version version, string connectionString)
    {
        if (version.Equals(new Version(1, 0)))
            return new DbUpdate.DbUpdate10(connectionString);
        else if (version.Equals(new Version(1, 1)))
            return new DbUpdate.DbUpdate11(connectionString);
        return null;
    }
}

History

• Initial version: 18 January, 2012.