none
Controlling database update with click once.

    Question

  • Hi,

    I’m planning in controlling database updates with click once. So what I’m thinking in doing is:

     

    0)     System starts up and enters the “Main”.

     

    1)     From code we check to see if there is a newer version on server. E.g.: 1.0.0.6.

     

    2)  We control if the user wants to proceed or not. If he proceeds, click once would go ahead with the update, updating all local files. (At this point all local code is already updated. But we continue in the main, and the first form is yet to show.)

     

    3) If click once has performed an update it checks to see if there are any database updates needed. (I was thinking in separating in two files. One for schema or structure changes and another for data changes.) It checks if a file named: 1_0_0_6_structure.sql or 1_0_0_6_data.sql exist.

     

    4)     If either exist, we know that a database update is about to happen. We continue backing up the existing database from code.

     

    5)     We then execute the structure: 1_0_0_6_structure.sql and following this we execute 1_0_0_6_data.sql.

     

    6)     The application starts with its new database schema, data and updated dlls.

     

    Many things could go wrong, so if a database update fails, I would have to revert to the previous version 1.0.0.5, also I would have to restore the backup, leaving the application exactly as it was before the update.

     

    Would this be a correct way to do things?

    Monday, January 24, 2011 10:21 AM

Answers

  • Hi Raulands,

    I would recommend using automatic updates, but don't publish the updates as required updates. If you do this, the user can skip the update if he wants to. But if you DO ever need to require an update (when you have a database update, for example), you can force an update (by setting the minimum version in the Updates dialog) OR you can use that to keep a minimum version.

    Having said that, I would handle the updating of your database yourself, and not rely on ClickOnce's update policy. If you even so much as OPEN that database in the project, ClickOnce will replace the production one. I recommend that people copy their data to a separate folder the first time the application is run. Here's a blog entry explaining this:

    http://robindotnet.wordpress.com/2009/08/19/where-do-i-put-my-data-to-keep-it-safe-from-clickonce-updates/

    If you do this, you can be very specific about when/if you update the database structure. You could put the SQL statements in the resources, check for them, and execute them if found, that kind of thing. Then you can check the current deployment's IsFirstRun property to see whether to apply them -- this is true when the user does the first install or actually gets an update, but it's false in other cases.

    RobinDotNet


    Click here to visit my ClickOnce blog!
    Microsoft MVP, Client App Dev
    • Proposed as answer by Aspen VJ Friday, January 28, 2011 6:22 AM
    • Marked as answer by Helen Zhou Wednesday, February 02, 2011 7:26 AM
    Wednesday, January 26, 2011 7:55 AM
    Moderator
  • I think InstallShield supports incremental updates, but it seemed prohitibively expensive last time I checked. You could also write your own update method.

    LocalApplicationData is definitely the place to put the backups. (If using SQL Compact Edition, they are just files, and you can just copy them). If you end up reverting, the user will lose any data he has put in. You realize that, right?

    Also, ClickOnce only supports rolling back one version.

    The only way I can think of to force a rollback is to have the clickonce app programmatically call its uninstall string. You could write some fancy code to search for and select the right radio button and hit the OK button. I wrote an article about handling certificate changes that included uninstall/reinstall code that shows how to get the uninstall string and uninstall the application. If you don't use forced updates, the dialog that comes up lets the user choose to back up a version. The article is here if you want to look at the code: http://msdn.microsoft.com/en-us/library/ff369721.aspx

    RobinDotNet

     


    Click here to visit my ClickOnce blog!
    Microsoft MVP, Client App Dev
    • Marked as answer by Helen Zhou Wednesday, February 02, 2011 7:26 AM
    Saturday, January 29, 2011 8:03 PM
    Moderator

All replies

  • Hi raulands,

    Base on your description, it seems that you want to add some logic custom action to do something as you want.

    Base on my understanding, clickonce dowsn;t support custom action like MSI. However, you can run your setup code at the first time the application is running. To determine if it is the first time the application is run, use the ApplicationDeployment.IsFirstRun peroperty.
    http://msdn.microsoft.com/en-us/library/system.deployment.application.applicationdeployment.isfirstrun.aspx

    Sincerely,
    Vin Jin


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 25, 2011 6:19 AM
  • Hi,

    Thanks for your reply. To control from code whether updates are available I created my own start up class (based on tutorials I found)

    Something in the lines of:

     public class TpvProgram : Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase {
    
     	[STAThread]
        public static void Main() {
    
     			Version v = new Version(config.Version_ClickOnce);
    
                UpdateCheckInfo info = ApplicationDeployment.CurrentDeployment.CheckForDetailedUpdate(false);
                if (info.UpdateAvailable) {
                  if (info.AvailableVersion > v) {
                    ApplicationDeployment.CurrentDeployment.Update();
                  }
                }
    	}
    }
    

    This way I can control if a user wants to proceed or not with an update. If he does, click once downloads the latest version. I´m planning in having a folder where I´d store the .sql scripts, and control, database updates.

    If an error happens at some point I´d have to revert everthing as to how it was prior the update. Restore the database backup and revert to the previous click once version.


    Rauland in a land of raul.
    Tuesday, January 25, 2011 12:46 PM
  • Hi raulands,

    Base on my understanding, the clickonce have no function to revert everything as you want. But if you update app through clickonce, if there is something wrong, it will break down update and revert to the prior version automatically. But it is not you want.

    Sincerely,
    Vin Jin


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 26, 2011 7:42 AM
  • Hi Raulands,

    I would recommend using automatic updates, but don't publish the updates as required updates. If you do this, the user can skip the update if he wants to. But if you DO ever need to require an update (when you have a database update, for example), you can force an update (by setting the minimum version in the Updates dialog) OR you can use that to keep a minimum version.

    Having said that, I would handle the updating of your database yourself, and not rely on ClickOnce's update policy. If you even so much as OPEN that database in the project, ClickOnce will replace the production one. I recommend that people copy their data to a separate folder the first time the application is run. Here's a blog entry explaining this:

    http://robindotnet.wordpress.com/2009/08/19/where-do-i-put-my-data-to-keep-it-safe-from-clickonce-updates/

    If you do this, you can be very specific about when/if you update the database structure. You could put the SQL statements in the resources, check for them, and execute them if found, that kind of thing. Then you can check the current deployment's IsFirstRun property to see whether to apply them -- this is true when the user does the first install or actually gets an update, but it's false in other cases.

    RobinDotNet


    Click here to visit my ClickOnce blog!
    Microsoft MVP, Client App Dev
    • Proposed as answer by Aspen VJ Friday, January 28, 2011 6:22 AM
    • Marked as answer by Helen Zhou Wednesday, February 02, 2011 7:26 AM
    Wednesday, January 26, 2011 7:55 AM
    Moderator
  • Hi RobinDotNet,

    Thanks for your reply and time,

    The app we are developing is going to be installed in about 20+ clients, and there is intention in automating application/database updates. If an update fails (as in a non controlled failure), the application will probably crash and there´ll be a few angry phone calls. :) Also at all times there can´t be any risk in data loss.

    At  the moment I have no plans in forcing updates. An update will only happen if a user decides to do so. Thanks for your reply , as I may in the future need to force updates.

    I´m also planning in controlling database updates myself. Thanks for your blog post.  In a previous step before a database update, I´m planning in backing up the database, from code. I would think the "Environment.SpecialFolder.LocalApplicationData" would be a good place  to store the .bak file. (e.g: myDatabaseName_1_0_0_5.bak)

    The way I was thinking in applying database updates is:
    - Have a folder with all database updates. (Called for instance "Database").
    - Inside this folder have script files(as resources) like: 1_0_0_6_structure.sql, 1_0_0_6_data.sql, 1_0_0_5_structure.sql, 1_0_0_5_data.sql etc...
    - So I would pair up the new version to the first part of the name of the script file.
    - If I database update fails for whatever reason, control a rollback, and restore the .bak file (stored in "Environment.SpecialFolder.LocalApplicationData" ).

    Now up to here, I think its achievable. (let me know if I´m over complicating,...please...  as I am going to have to implement this quite soon)

    The only problem is force a code rollback, so if I have updated for instance from 1_0_0_5 to 1_0_0_6 and a database update goes wrong, from code I can restore the .bak file, but how would I restore the code back to 1_0_0_5?

    Should I consider other ways to control/automate application updates? (I mean an alternative to click once). 

    Thanks again


    Rauland in a land of raul.
    Wednesday, January 26, 2011 9:33 PM
  • Hi raulands,

    Base on my understanding, the clickonce have no function to revert everything as you want. But if you update app through clickonce, if there is something wrong, it will break down update and revert to the prior version automatically. But it is not you want.

    Sincerely,
    Vin Jin


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thanks,
    So, If I throw an exception,.... would it roll back to the previous version? (I´ll do a test see what happens)


    Rauland in a land of raul.
    Wednesday, January 26, 2011 9:35 PM
  • Hi raulands,

    Base on my understanding, the clickonce have no function to revert everything as you want. But if you update app through clickonce, if there is something wrong, it will break down update and revert to the prior version automatically. But it is not you want.

    Sincerely,
    Vin Jin


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thanks,
    So, If I throw an exception,.... would it roll back to the previous version? (I´ll do a test see what happens)


    Rauland in a land of raul.

    Sorry, ignore this,... :)
    Rauland in a land of raul.
    Thursday, January 27, 2011 6:13 AM
  • Hi Rauland,

    I think the Robin's relply is help to you. You can refer to his seggestion. Thanks.

    Sincerely,
    Vin Jin


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 28, 2011 6:23 AM
  • I think InstallShield supports incremental updates, but it seemed prohitibively expensive last time I checked. You could also write your own update method.

    LocalApplicationData is definitely the place to put the backups. (If using SQL Compact Edition, they are just files, and you can just copy them). If you end up reverting, the user will lose any data he has put in. You realize that, right?

    Also, ClickOnce only supports rolling back one version.

    The only way I can think of to force a rollback is to have the clickonce app programmatically call its uninstall string. You could write some fancy code to search for and select the right radio button and hit the OK button. I wrote an article about handling certificate changes that included uninstall/reinstall code that shows how to get the uninstall string and uninstall the application. If you don't use forced updates, the dialog that comes up lets the user choose to back up a version. The article is here if you want to look at the code: http://msdn.microsoft.com/en-us/library/ff369721.aspx

    RobinDotNet

     


    Click here to visit my ClickOnce blog!
    Microsoft MVP, Client App Dev
    • Marked as answer by Helen Zhou Wednesday, February 02, 2011 7:26 AM
    Saturday, January 29, 2011 8:03 PM
    Moderator
  • Thanks RobinDotNet, I´ll have a look at the code.

    In the mean time as I was having trouble, trying to rollback an installation, e.g.: from v: 1.0.0.6 to 1.0.0.5. I’ve decided to have a separate click once application altogether which would be responsible of installing the main application.

    So I’d have a click once app (the installer) and the main application. The user wouldn’t necessary know that there are two apps, and on every start up, he would always open the installer. The installer would check for available updates and if there aren’t any available it would open the main application and the close itself down.

    I’m thinking in zipping all main application files and embed them, as a resource file in the installer application. On start up the installer would have the responsibility (from code) of copying, installing, rolling back, backing up database and restoring the database of the main application.

    I’m planning on a first install, to embed an .msi installer file and future updates to unzip and copy the application files.

    So the process would go something like this:

    A new install:

    0) User decides to install the “installer” using click once.
    1) Click once detects that sql server isn’t installed and goes ahead with its installation.
    2) The installer would have, as an embedded resource file, an msi installer for the main application.
    3) The .msi installer would be launched (I think I can do this in silent mode) and the main app installs. (Database scripts and everything)
    4) The updater informs that the installation has completed closes itself down and opens the main application.

    Updates:

    0) The installer starts, and would detect that a new version of the installer is available.
    1) The user is asked if he wants to proceed with the update. (If “no” the installer would open the main application.)
    2) If the user accepts to update instead of executing the .msi installer, it would look for the latest .zip package with the entire updated source files (e.g.: Something like. 1_0_0_5_mainapp.zip). (It would include the application and scripts files).
    3) The installer backs up the database and the current application source files. It then proceeds overwriting all application files with the new ones just downloaded and executes all database scripts.
    4) If an error occurs the installer would restore the database and application source files.
    5) Once finished the updater informs that it has completed and would open the main app.

    Errors:

    If the updater doesn’t detect a new version on the server, before opening the main application, it would detect if a previous installation failed. This could be done by checking a file which would store install statuses. (I’ll have to decide where to put this, maybe in the main apps app.config). I can also control whether to retry the installation or to ignore and open the main app instead.


    Rauland in a land of raul.
    Sunday, January 30, 2011 8:58 PM