Click here to Skip to main content
14,122,160 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

20K views
240 downloads
17 bookmarked
Posted 19 Jan 2012
Licenced CPOL

Update a database from code

, 19 Jan 2012
Rate this:
Please Sign up or sign in to vote.
How to update a database when the application starts.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Patrick Ribbing
Architect
Sweden Sweden
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
QuestionNot invented here syndrome Pin
Grootjans19-Jan-12 5:56
memberGrootjans19-Jan-12 5:56 
AnswerRe: Not invented here syndrome Pin
Patrick Ribbing19-Jan-12 8:32
memberPatrick Ribbing19-Jan-12 8:32 
GeneralMy vote of 4 Pin
John Brett19-Jan-12 3:02
memberJohn Brett19-Jan-12 3:02 
GeneralRe: My vote of 4 Pin
Patrick Ribbing19-Jan-12 8:28
memberPatrick Ribbing19-Jan-12 8:28 
QuestionInteresting, but perhaps you could refactor it a little. Pin
Pete O'Hanlon19-Jan-12 2:06
protectorPete O'Hanlon19-Jan-12 2:06 
AnswerRe: Interesting, but perhaps you could refactor it a little. Pin
Patrick Ribbing19-Jan-12 8:19
memberPatrick Ribbing19-Jan-12 8:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.190518.1 | Last Updated 19 Jan 2012
Article Copyright 2012 by Patrick Ribbing
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid