Automatically update SQL Server database schema

Question

I have deployed a C# app and my customers benefits from new deployed versions by ClickOnce update.

But I have trouble when I change to the application database schema because the customers cannot benefit from auto update database changes, please give me the solution how can I let customer app to check if the new version of DB is available then do the update.

So there are two problems:

how does customer app check for DB schema new version?
how to perform update with avoid to loss customer data just update schema?

What technology do you use to access database? What schema changes do you plan to apply? What database do you use? — Dennis, Nov 27 '13 at 10:22
It is sql DB (sql server 2008 r2). I mean change in field name or adding new tables ,stored proc ... — franchesco totti, Nov 27 '13 at 11:00

score 2 · Accepted Answer · answered Nov 27 '13 at 12:49

I assume each user has it own database. In this case you can do the following:

use Entity Framework with "auto migrations" enabled: http://msdn.microsoft.com/en-us/data/jj554735.aspx. This way as soon as model change is detected, EF will automatically update database.
Write migrations manually and use ClickOnce IsFirstRun feature to detect the first time when new version of the app was executed - and then you can write something like this:

public void MigrateData()
{
  if (ApplicationDeployment.IsNetworkDeployed)
  {
      if (ApplicationDeployment.CurrentDeployment.IsFirstRun)
      {
          try
          {
              NS.Logic.DAL.EventDataAccessor.Create().MigrateFromPreviousVersion();
              NS.Logic.DAL.UserDataAccessor.Create().MigrateFromPreviousVersion();
              // and do the same for all affected accessors
          }
          catch (Exception ex)
          {
              MessageBox.Show("Could not migrate data from previous version. Please contact the developer.",
                  "My app", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  }
}

and then for each accessor in MigrateFromPreviousVersion() you write your own SQL code to update the database for this particular version of the app.

you need to create SQL code which does the migration (i.e. "CREATE PROCEDURE ..." etc) - and execute it inside `MigrateFromPreviousVersion()` call. If you use ADO.Net - this post might help you: http://stackoverflow.com/a/1190832/1246870 — avs099, Nov 27 '13 at 13:09
basically, it's YOUR responsibility now to track all changes you do in database, and keep them in a separate folder - so you can combine them into "deployment" package when releasing new app version. — avs099, Nov 27 '13 at 13:11
re: MainDATASET.xsd - no idea sorry - that was not your initial question - do some SO search for that. — avs099, Nov 27 '13 at 13:11

Automatically update SQL Server database schema

1 Answers1