Check And Upgrade Existing Database

How to change database schema, adding or deleting field or table, to be sure of new version of application will work correctly and existing users not lose their data?

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

What about programmatically change 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, only need to care if database schema already up to date.

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

More about databases in .NET: Pro ADO.NET 2.0 (Expert's Voice)