CODEWITHSUNDEEP
c#sql-serverclickoncedatabase-schemaauto-update
franchesco totti
franchesco totti

Reputation: 644

Automatically update SQL Server database schema

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:

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

Upvotes: 3

Views: 4031

Answers (1)

avs099
avs099

Reputation: 11227

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

  1. 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.

  2. 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.

Upvotes: 2

Related Questions