Click here to Skip to main content
16,020,565 members
Articles / Programming Languages / SQL

Check And Upgrade Existing Database

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
7 Sep 2010CPOL1 min read 20.7K   11   5
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;
 }
}
This article was originally posted at http://c-sharpening.blogspot.com/feeds/posts/default

License

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


Written By
Software Developer (Senior) i-BLADES
Thailand Thailand
I'm Android and Full Stack Software Engineer. 28 years in software industry, lots of finished projects. I’m never afraid of learning something new and you can see it by amount of skills in my resume.

I'm working remotely since 2009, self-motivated and self-organized.

There are no impossible projects. I have experience with Android, iOS, Web, Desktop, Embedded applications, VR, AR, XR, Computer vision, Neural networks, Games, IoT, you name it.

Comments and Discussions

 
Generalsysobjects, syscolumns, information_schema , etc.. Pin
sninkovic16-Sep-10 0:34
sninkovic16-Sep-10 0:34 
GeneralRe: sysobjects, syscolumns, information_schema , etc.. Pin
knoami18-Sep-10 7:25
knoami18-Sep-10 7:25 
Generalmaybe i don't understand Pin
EngleA14-Sep-10 3:41
EngleA14-Sep-10 3:41 
What is it you're trying to do here? How is my web application going to be up to date now if you hard code a SQL statement in the application? How does my n-layer or business object layer know now that that column exists in the database?

Seriously? Hard coding a SQL update?

Preferred method: adding the requirement to the next iteration. They can wait.
Generalchange a stored procedure Pin
dg787-Sep-10 23:56
professionaldg787-Sep-10 23:56 
GeneralRe: change a stored procedure Pin
Ernest Poletaev9-Sep-10 8:51
Ernest Poletaev9-Sep-10 8:51 

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.