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.

How do I work with data from database?

Is there any general rule how to work with data that I read for a database? At the moment, I work with local Access database and I want to show tables, graphs etc, modify some records, make calculations and so forth.

1) Should I work directly with data that I get in OleDbDataReader using the SELECT command.

2) Or should I always create a class with fields correcponding to the record structure? Then a list of these classes/structures would correspond to the whole returned query. I would read data from the DB using the SELECT command, go trough all the returned records in this query and save them to the local list of objects and then work work with the data in this local set of classes/structures? After I make all the changes, I update the DB from the structures again?

I am not very sure how to do it. I've always used the first method with Paradox tables, but I feel somehow that the second method is maybe better? I am also not sure if creating thousands of objects (corresponding to thousands returned rows in a table) is a good idea, when I already have all the data in the query.

Thanks,
Petr
Petr Veit Send private email
Thursday, August 02, 2007
 
 
Let the urinating contest begin....

I mostly do 2) for complex data.  I'll do 1) if it's more of a search & review type of query, or if data manipulation is VERY basic.

But then again I'm not an expert on much of anything.
fair to middlin
Thursday, August 02, 2007
 
 
Read the entire database into memory at startup. RAM and harddrive space are very cheap these days. What you get, really, is data that is organized inefficiently in database rows (good for long-term storage) loaded into Maps (aka Objects) that are usable by your application, and which for the most part will reside in a swap space managed by the operating system.

On shutdown, re-persist the entire structure back into the database.
my name is here
Thursday, August 02, 2007
 
 
No there isn't a general rule.  It depends on your language and environment and the complexity of the application you are creating.  And even then you will get a lot of different opinions.

It sounds like you are working in .net (c# or vb I presume).  If so you would also consider the third option of using using data sets.
Bill Hamaker Send private email
Thursday, August 02, 2007
 
 
This is really an age old question driven by the differences in programming models for in-memory structures/object and traditional data (result sets with columns & rows).

In some applications it does not make sense to pay the computing cost of "shaping" data into objects, and also sometimes the query constructs or "table" shapes are a better fit (grid views, reports, simple iteration...).  Also, for some situations, the strongly typed aspects of having your data in objects don't fit the "late-binding" nature of your problem (think integration scenarios, data browsing tools, etc.)

Datasets are a decent middle ground in the .NET world as they let you load the data into in memory structures but still use query constructs over that data.  Typed datasets added an object veneer to these in-memory structures that can bring the programming models back in line to some degree.

What datasets do not solve is the problem of "querying" across traditional data AND other in memory object or structures and the fact that "querying" is inconsistent across data providers, embedded in stings, and unknown to compilers and languages to do things like compile-time checking.  This is part of what Language Integrated Query (LINQ) in .NET 3.5 will bring.

LINQ will add query constructs as core language features and let you do things like join a collection of objects with a result set from a database query, or use the same query syntax to gather appropriate data from both an array of items and a XML document.

Here is a good webcast intro to LINQ from TechEd:

http://www.microsoft.com/events/EventDetails.aspx?CMTYSvcSource=MSCOMMedia&Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22ID%22+Value%3d%221032340926%22%2f%5e%7earg+Name%3d%22ProviderID%22+Value%3d%22A6B43178-497C-4225-BA42-DF595171F04C%22%2f%5e%7earg+Name%3d%22lang%22+Value%3d%22en%22%2f%5e%7earg+Name%3d%22cr%22+Value%3d%22US%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e

or download VS 2008 Beta 2 & try it out.
Chris Brooks Send private email
Thursday, August 02, 2007
 
 
"Read the entire database into memory at startup. (...) On shutdown, re-persist the entire structure back into the database."

OMG, this is really a crappy idea. What if there is more than a single user of this application?
OracleDude
Friday, August 03, 2007
 
 
Thank you for all your suggestions.

I understand that if I do some basic work with data, using the result sets is convenient and if I want to make more calculations/work with the data, it is useful to move data from columns/rows in the result set into my own data structures.

I have some additional questions:

1) If I only want to display the table, this is an ideal case for the direct use of result set. Now, what if the user makes changes to some records here and there in the table? Should I update the DB immediately after each record is edited or should I remember all the changed records, wait until the user closes the window and then update all the records at once?

2) If I go the second way (my own in-memory structures) and the user changes some record, do I have some "Modified" property in each object and then update the records at once or again, update each record separately?

Thanks,
Petr
Petr Veit Send private email
Friday, August 03, 2007
 
 
>> "Read the entire database into memory at startup. (...) On shutdown, re-persist the entire structure back into the database."

OMG, this is really a crappy idea. What if there is more than a single user of this application?  <<

You mock, but that's what we did at a previous job.
:-)

Add on a publish-subscribe bus, and you then have a way to push updates out to clients without polling the DB for changes.  Yes, some databases have "events", but ours runs on just about any database.

This wouldn't have been my first choice either, but given the very specific and very firm requirements we had for response time, it was the only way we could find to meet them.


>> Should I update the DB immediately after each record is edited or should I remember all the changed records, wait until the user closes the window and then update all the records at once? <<

You can batch your updates, if that's a more natural way for the user to work with your app.  I probably wouldn't wait until they close the window, though -- I would just put a Save button on there.  You still need to decide what to do when they close the window without clicking Save -- do you abandon their changes, save them, or ask them to confirm?  We can't answer that -- you need to know what behavior your users expect.

>> If I go the second way (my own in-memory structures) and the user changes some record, do I have some "Modified" property in each object <<

Yes, you'll need a "IsDirty" flag to let your data access code know that it has to write the changes back to the DB.  Moreso, the IsDirty flag would be set as the user is editting the record, so that you know they touched it (and thus needs saving).
xampl Send private email
Friday, August 03, 2007
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz