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

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?

• Status: Open
• Question Views: 979
• Answer Count: 9
• Vote Up 23 Vote Down

• Answer Accepted: Yes
• Question Category: .NET Programming

Best Answer by Stella

Go To Solution

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!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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 ???

About Karoline

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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 ?

About Karoline

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

Best Answer

Best Answer

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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!

About Karoline

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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!

About Karoline

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming

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!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: .NET Programming