Click here to Skip to main content
Click here to Skip to main content

Check And Upgrade Existing Database

, 7 Sep 2010
Rate this:
Please Sign up or sign in to vote.
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:

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

License

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

About the Author

Ernest Poletaev
Software Developer (Senior) Enterprise Information Systems, Plc
Thailand Thailand
Senior Software Developer in Enterprise Information Systems, an ERP / CRM / Docflow Software Solution Provider for Russian Local Market.
 
Professional Developer (C, C++, C#), since 1984. Experienced in many programming technologies.
 
Now i resides in Kalasin, Thailand, working remotely for my company

Comments and Discussions

 
Generalsysobjects, syscolumns, information_schema , etc.. Pinmembersninkovic16-Sep-10 0:34 
GeneralRe: sysobjects, syscolumns, information_schema , etc.. Pinmemberknoami18-Sep-10 7:25 
Generalmaybe i don't understand PinmemberEngleA14-Sep-10 3:41 
Generalchange a stored procedure Pinmemberdg787-Sep-10 23:56 
GeneralRe: change a stored procedure PinmemberErnest Poletaev9-Sep-10 8:51 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 7 Sep 2010
Article Copyright 2010 by Ernest Poletaev
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid