SQL Updates with WYBuild

This is an interesting question. There's no one right way (there are many, many options). Here's what I would do:

The critical thing you have to do is to store the schema version in the database. Then everything else become relatively easy. So when you write your SQL script to update the database you can have a simple WHERE statement that aborts the process if the schema version is not X.

2. Once the M/S SQL database is updated, all of the client systems need to be forced to update the application before they are run again. The old application will not work with the new schema.

Again, this is where the schema version in the database comes in. When your application starts (or maybe everytime your app makes a SQL query) check that schema version value. If the client is not compatible with schema version X you can forcefully check for updates and tell the user that they must update before continuing to use the app.

Does this help?

Sam:

Thanks for your repsonse. We can implement your suggestion for Question 1, since when we create the SQL scripts we know what the new SQL schema version should be. However, with Question 2 we still have the following questions:

a. How does the application know that the correct schema version should be? Our only thought would be to store it as an application or configuration setting, since we don't want to code it in the application. Is there another better way?

b. Is there any way to just force the application to automatically run the update if the schema version is not up-to-date? Ideally at application startup we would want the update automatically execute and the restart the application.

a. How does the application know that the correct schema version should be? Our only thought would be to store it as an application or configuration setting, since we don't want to code it in the application. Is there another better way?

It's really up to you how you version your schemas and how your clients reacts to different versions. The easiest thing would be to use a major / minor version for the schema. So a client that talks to the database can only use the data if the "major" version is X.

Does that make sense?

b. Is there any way to just force the application to automatically run the update if the schema version is not up-to-date? Ideally at application startup we would want the update automatically execute and the restart the application.

Yes. Are you using the AutomaticUpdater or wyUpdate as a standalone updater?

Sam,

We are using the AutoUpdater, not the standalone version

In that case if there is a database schema mismatch then the first thing you need to do is block further access to the program. Give them a notice explaining what is happening. Also, make the AutomaticUpdater control "front and center".

If there are no pending updates with the AutomaticUpdater -- then the first thing you need to do is ForceCheckForUpdate(). Then when the ReadyToBeInstalled event is called call the function Install().

Sam:

Your reponses have answered all of our questions for now. Thanks for all of your help.