Best way of writing to an Access database (C#/.net)

Best way of writing to an Access database (C#/.net)

LennyLen

Member #5,313

December 2004

I had another chat with my client today to discuss the last few reports they want, so I'll be finishing up that project this weekend, and they've also asked me to write another program to keep track of membership subscriptions for them (if they'd asked me to do that originally, I would have done it all in one program, but oh well).

I did kind of hack my way through writing my first program as I didn't think I had enough time to learn C# before starting, so I just approached it the way I would a C/C++ program and then googled the C# way of doing the equivalent when I got stuck. Because of this the way I stored the data I read from my Access database was by first creating a class to hold the data from each table and then stored the data in lists.

I've since learned about datasets, and that's what I'll use to represent the database in the second program. The way I wrote the data back to the database was by creating an event handler that fired every time a field on my form lost focus, and if the data in the field was different from that stored in memory, I altered the memory version then created a oledb connection and wrote the data back to the database.

This time I'll use a DataAdapter to write to the database, but I'd like some advice on when to write the data to the database. The client doesn't want the user to need to manually save anything so the process needs to be done automatically.

So I can either do what I'm already doing, and update everytime the user changes the contents of a field, or I was thinking I can also set up a timer to update in the background every few seconds, and then do a last update on exit if they clsoe the application between timed updates.

Which of these two methods would you advise, or is there a third method that I haven't considered?

Nb. Using Access to store the data is a client requirement, so I can't change that.

bamccaig

Member #7,536

July 2006

I honestly haven't worked enough with data adapters to appreciate them. It's a bit of black magic to me. If it helps, there's nothing particularly wrong with loading the data into a data structure to manipulate it. That's how I've usually done it, and how I've seen it done in a rather large mill as well. The only real problem with it (as I alluded to in the other thread) is that when you need to make a change it's more difficult because you have to maintain extra code for the data structures and interfacing between the data structure and data (though reflection can also be used here to ease the troubles).

I believe the last time I toyed with it you still end up needing to write custom SQL to do the insert/update/delete of a data row so I'm not sure what the value of it really is. I'll try not to dissuade you from trying it though. Hopefully somebody else has used it to good effect and can recommend it.

As far as when to save the data, I think that it's a fair thing to do whenever the user changes the data. I can't think of a better way to do it myself. You probably wouldn't want to write the data every time the user typed a character, but if they've stopped typing for a few seconds (or left the field, exited the application, etc.) then you could save the data then. You probably want to avoid hammering the database, especially if the database is just an Access database file.. Then said, if it's not causing the UI to lock up or anything of that nature then you probably don't need to worry about it.

A timer could certainly work too, but doesn't sound ideal. It would make it more difficult to understand when the data should be saved. And depending on which thread it's running in it could potentially cause multiple connections to the Access file which I'm unsure how it will handle... I imagine it will be safe, but one of them might fail or something. Or they might succeed. I'm not too sure. It's likely that it'll be on the same thread as other event handlers though so probably not a big deal. Maybe I'm over-thinking it... Either way, on blur/leave or change is usually good enough.

I'm not sure if Access locks the file when it loads it up in MSO. You may want to test how your application responds when the file is open in Access, or doesn't exist at all. Ideally, your application should fail gracefully, alert the user of the problem, and avoid crashing (warn the user if data will be lost).

Sounds like you have everything under control though.

--
I mean the best with what I say. It doesn't always sound that way.