The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Save to database file

I need to write an application that will store its data in a local database file (like MS Access MDB or any other embedded database).

I have a requirement to work the same way as Microsoft Office applications: you create a new file or open an existing one, do you changes and then click "Save". The data in memory is then persisted to the filesystem. If you don't click save and close, data is not persisted.

My application has 4 or 5 relational tables.

I am trying to understand what is the best way to accomplish that, in respect of my objects and loading/saving of data.

Should I load the entire database into memory and then, when the user clicks "Save" I persist the memory contents into the DB?

Is there a way to do this with transactions? That is, to issue the DB commands in every operation and then, if the user clicks "Save", I commit. If he closes the app I rollback.

What do you think? What is the solution that you adopt in your application?

I'll probably be using Microsoft SQL Server Compact edition and .NET Windows Forms.
Anon_2007
Saturday, February 10, 2007
 
 
I did something like this a few years ago with the additional requirement that once the system had connectivity to the central db, it would re-sync the relevant data.

If you are working on a mobile device - sounds like you are - they're generally limited in terms of memory, performance, and a bit finicky in terms of power.  Therefore, you don't want to hold/cache much in memory if you can avoid it.

On the database design side of things, this may be one of those times where denormalizing certain tables make sense... less joins can mean less processing time/power required.
KC Send private email
Saturday, February 10, 2007
 
 
I'd do it by creating a temporary copy of the database file on startup, working with that, and copying it over the "real" file on save.
clcr
Saturday, February 10, 2007
 
 
"Should I load the entire database into memory and then, when the user clicks "Save" I persist the memory contents into the DB?"

Why would you load the entire database if you don't need to? Just load what you need into memory and disconnect from the database. When they press save, connect again (probably using an already open connection from the connection pool) and save.

"Is there a way to do this with transactions? That is, to issue the DB commands in every operation and then, if the user clicks "Save", I commit. If he closes the app I rollback."

You don't want to do that. That implies keeping an open connection to the database for a long time which is costly and dangerous. If the program crashes the data that hasn't been specifically committed is lost. Also, if the system hibernates or some other event occurs that causes you to lose the connection that data is also lost.

Open the database, read what you need, and close the connection. When the user presses save, open the database save what you need, and close the connection. Simple to program and much more dependable.
dood mcdoogle
Saturday, February 10, 2007
 
 
If it's Access, you could use the Replica object to create a full replica on startup.

If the user clicks Save, you could sync the replica back to the original and delete the replica.

If the user clicks Save As, save the replica to a new name. I think there's a way to set the replica property to false as well.
Nick Hebb Send private email
Saturday, February 10, 2007
 
 
What is wrong with you people? The transactional commit/rollback method is a slam-dunk for this purpose.
David Aldridge Send private email
Saturday, February 10, 2007
 
 
Commit/rollback is generally a bad idea here.  Locks will be held for far too long and there is a huge probability of lock contention.  If there is guaranteed to be only one user in the system at a time, then it might be fine. 

Most database persistence tools will handle this situation very well.  Even standard .Net DataSets would be a great help in batching the changes until the save button is clicked.
JSmith Send private email
Saturday, February 10, 2007
 
 
Check out SQLite.
Wayne B Send private email
Saturday, February 10, 2007
 
 
> What is wrong with you people? The transactional commit/rollback method is a slam-dunk for this purpose.

You're right. I re-read the OP's post. I had interpreted it wrong. When I first read it, it sounded as if the customer wanted the ability to save to a new database with different data.
Nick Hebb Send private email
Sunday, February 11, 2007
 
 
I second Sqlite.  It has all of the functionality that you will need.

site link: http://www.sqlite.org/
Justin Silverton Send private email
Sunday, February 11, 2007
 
 
>> Commit/rollback is generally a bad idea here.  Locks will be held for far too long and there is a huge probability of lock contention. 

The alternative to locks would be the complexity of a change resolution system. Users A and B both change an existing value to something else and base other work n that value, then what happens when one new value gets overwritten with another?
David Aldridge Send private email
Sunday, February 11, 2007
 
 
The OP said, "application that will store its data in a local database file", so I'm guessing it's single-user.
Christopher Wells Send private email
Sunday, February 11, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz