Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Check And Upgrade Existing Database

4.00/5 (1 vote)
7 Sep 2010CPOL1 min read 20.7K  
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

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:

SQL
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:

SQL
ALTER TABLE customers ADD mob_phone nvarchar(20) 

Sample Code

C#
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;
 }
}

License

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