Check And Upgrade Existing Database

Introduction

This post discusses how to change database schema, add or delete a field or table, be sure that new version of application will work correctly and existing users will not lose their data?

Background

You are working on an application and have customers. One wonderful day, a customer asks you for new functionality. But you see it needs to change database scheme. You made local changes, but many customers have an old database. How to upgrade existing database, not harming customers' data. Make a migration script and copy data from the old database to the new one? Not such a good way.

Solution

What about programmatically changing database schema on fly? For example, you have table 'customers' and need to add field 'mob_phone'; You can use SQL statement to add field, you only need to care if database schema is already up to date.

Check Database Schema

First you need to be sure that database needs to be upgraded. I tried to make a special table to store the database version, but it can produce errors if some exception is generated by the way and database version will not be promoted, but part of the changes are already put in the database. Of course you can use transactions, if your SQL server can do. But it is much simpler to trust what you see.

I check required fields, trying to access them. For example, you have table customers and need to add field mob_phone. You can check the existence of field by simple access. SELECT COUNT(*) is an effective way:

SELECT COUNT(*) FROM customers WHERE mob_phone == NULL 

Insert Field into Table

When you check if database schema has to be upgraded, you can use SQL statement to change the database schema. In this case, we add field to table using ALTER TABLE:

ALTER TABLE customers ADD mob_phone nvarchar(20) 

Sample Code

class DatabaseChecker
{
 private static void ExecuteCommand(string queryString,
  SqlCeConnection connection)
 {
  SqlCeCommand command = new SqlCeCommand(queryString, connection);
  command.ExecuteNonQuery();
 }

 static bool SuccessQuery(string queryString,
  SqlCeConnection connection)
 {
  try
  {
   ExecuteCommand(queryString, connection);
  }
  catch (Exception e)
  {
   return false;
  }
  return true;
 }

 private static bool UpgradeDatabase( string connection_string )
 {
  using (SqlCeConnection connection = new SqlCeConnection(connection_string))
  {
   connection.Open();

   // Check if we already have this field
   if (!SuccessQuery("SELECT COUNT(*) FROM customers WHERE mob_phone=''", connection))
   {
    // Add field to table
    ExecuteCommand("ALTER TABLE customers ADD mob_phone nvarchar(20)", connection);
   }

   connection.Close();
  }
  return true;
 }
}