Asked By
Karoline
230 points
N/A
Posted on - 05/16/2011
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?
Answered By
Stella
0 points
N/A
#98110
Data gets lost when deploying a new program using SQL Express database
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
Karoline
230 points
N/A
#98111
Data gets lost when deploying a new program using SQL Express database
Oh dear! Does this mean I cannot use a data file for storing information ???
Answered By
Stella
0 points
N/A
#98112
Data gets lost when deploying a new program using SQL Express database
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
Karoline
230 points
N/A
#98113
Data gets lost when deploying a new program using SQL Express database
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 ?
Answered By
Stella
0 points
N/A
#98114
Data gets lost when deploying a new program using SQL Express database
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
Karoline
230 points
N/A
#98115
Data gets lost when deploying a new program using SQL Express database
Oh dear! Stella, isn't there any easier way of doing this ? It looks like a lot of work!
Answered By
Stella
0 points
N/A
#98116
Data gets lost when deploying a new program using SQL Express database
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
Karoline
230 points
N/A
#98118
Data gets lost when deploying a new program using SQL Express database
Thank you Stella, your reasoning is logical and valid!
Thank you for your expert advise!
Answered By
Stella
0 points
N/A
#98119
Data gets lost when deploying a new program using SQL Express database
Glad to be of Help! Have a nice day!
