Data gets lost when deploying a new program using SQL Express database

Asked By 230 points N/A Posted on -
qa-featured

This is making me go nuts. I am developing an onClick deployment desktop application. The application uses a local SQL Express data file.

Whenever I do a new build and release it, the old data gets obliterated! How can I save the data and then still use the program?

SHARE
Best Answer by Stella
Answered By 0 points N/A #98110

Data gets lost when deploying a new program using SQL Express database

qa-featured

In click-once programs built using .Net,  you need to be careful if you are using an attached database file for storing information.

This is because when you deploy a new version, the client file gets overwritten and all data will be lost. "One-Click" deployment files do not have the capability of preserving old data or files. It is just a one shot replacement!

Answered By 230 points N/A #98111

Data gets lost when deploying a new program using SQL Express database

qa-featured

Oh dear! Does this mean I cannot use a data file for storing information ???

Answered By 0 points N/A #98112

Data gets lost when deploying a new program using SQL Express database

qa-featured

What you could do is to install SQLEXPRESS and a database in the client machine as a one time process and use a connection string to connect to it.

This why you are not bundling the data with your application, rather you are dependent on the client to have SQLEXPRESS software installed.

This is the preferred method if your "One Click" program requires to use a database.

Answered By 230 points N/A #98113

Data gets lost when deploying a new program using SQL Express database

qa-featured

That is some reassurance! At least a ray of hope for me!

I thought the deployment programs did database changes if attached to it automatically without overwriting it!

Assuming I set up the client with SQLEXPRESS, how do I then make changes to the database? For example add new columns and tables ?

Best Answer
Best Answer
Answered By 0 points N/A #98114

Data gets lost when deploying a new program using SQL Express database

qa-featured

You will need to write update scripts and execute them on application startup.

I would recommend having a especial table called TBL_UPDATE with two columns, UPDATE_NAME and UPDATE_DATE

Then you can do the following algorithm

  • On application startup check if the TBL_UPDATES has an entry for 'UPDATE_ONE'
  • If not execute the update script
  • Then insert a row into the TBL_UPDATE
  • Next check if the TBL_UPDATES has an entry for 'UPDATE_TWO'
  • If not execute the update script
  • Then insert a row into the TBL_UPDATE
  • continue on until all updates are done

When you prepare a new build, create separate methods for the update process and execute them in a loop.

This way the client database can be "upgraded" incrementally.

Answered By 230 points N/A #98115

Data gets lost when deploying a new program using SQL Express database

qa-featured

Oh dear! Stella, isn't there any easier way of doing this ? It looks like a lot of work!

Answered By 0 points N/A #98116

Data gets lost when deploying a new program using SQL Express database

qa-featured

Not to my knowledge Karoline!

What I posted is what I did in practice for one of my clients. It is a bit hard at first, but when you get used to it, its a breeze!

Besides the benefit is that your program size is smaller and the SQLEXPRESS will run in a separate process. This gives better performance.

And the client can always take an independent backup of the database as and when required.

Answered By 230 points N/A #98118

Data gets lost when deploying a new program using SQL Express database

qa-featured

Thank you Stella, your reasoning is logical and valid!

Thank you for your expert advise!

Answered By 0 points N/A #98119

Data gets lost when deploying a new program using SQL Express database

qa-featured

Glad to be of Help! Have a nice day!

Login/Register to Answer

Related Questions