Execute SQL Script with .net executable

Question

I have a c# .net application that has been distibuted separately to several clients, Once in a while we add some new features or fix some bugs and frequently make changes in the DB. I use click once deployment approach to update exe to my client pcs. SQL server instance has been installed on their system.

I want to know how could i update their sql database with each exe updation.

Kindly keep in mind this is possible that a client is working on version 2 and never updated to version 3 but when version 4 is released, then he updates the application. In such case i need to execute two scripts on his database. One for version 3 and other for current version which is 4.

Thanks

Answer 1

Assuming you have a stored procedure/script that can update one version to another, and assuming you can't load any other exe that will run it, why don't you just include a call to run it at the start of the new version? Have there be a a check when the software loads that looks to see if they're using the correct version of the database, if not, update it. To handle issues like going from version 2 up to 4, just make sure the database itself keeps track of what version it currently is.