How to use SQLite for windows forms application file?

Question

I'm writing a new Windows Forms 2.0 application. This application is going to have a data file. The user will be able to open the data file, do some work and save it to disk. Then he'll be able to open it later, to continue working. Same thing as Microsoft Word and .doc files.

I'd like to use SQLite for the data file. However, I don't want changes to be written immediately to the data file. I want the user to change the data, but the file on disk remaining unchanged. Only when the user clicks "save" the data file on disk will be changed.

In your opinion, what's the best way to do this?

Should I load the contents of the SQLite DB into memory, maybe mapping it to objects, and write back to it when the user clicks "save"?

Or... is there a way to work with the data file with SQL commands (no objects), but leaving the changes in memory only, until "save" is clicked?

I'm a bit confused, I'd appreciate any idea regarding a best practice for this.

Cheers

Answer 1

Load the data when the user enters the edit-form. If he clicks save then Update/Insert. If not then just close the Form without any database action.

Answer 2

Temp files.

Use XML or some other easy to parse format, save the data to a temp file on disk. When user saves the file, dump that date into your db.

This gives you the save-when-user-hits-save feature plus some redundancy if the machine dies or program crashes.

Answer 3

1) On startup, make a temporary copy of the Sqlite file. This will be your "working file".

2) Run all the INSERTs/UPDATEs/DELETEs against working file. When the user clicks "Save", copy the working file over the original.

3) Delete the working file and goto step #1.

Answer 4

Depending on your scenario, you can also wrap your SQL in a transaction (BEGIN TRANSACTION;), and when the user click save, commit the transaction; if user click cancel, rollback the transaction.