Design question

[Jack]

Hello,
VB6. I have a class. The class receives a bunch of individual items from
an asp page, then updates the database. My question is what is the best way
you have found to update the database, one record at a time -or- send every
that needs to get written to the database to the class, then execute a
function to update the tables.
Here are the two scenarios.
-1-
For each record I need to insert into the db, Call a method on a class,
passing the items. In the method, execute an insert statement.

-or-
-2-
Have a method, like 'additem'. This method would add the stuff to a
collection in the class. Then, when the asp page is finished adding all its
items, call a method to take each item in the collection and insert it to
the database.

I have done it both ways in the past, but was curious to know the way you
guys do something like this. Thanks for your time.

[Dikkie Dik]

Re: Design question

Personally, I am in favour of putting database handling in its own
classes or even a separate layer. It helps you switch databases if you
ever want to, and I usually do want to in my unit tests.
No method is "absolutely correct". You can represent the whole database
structure in an object layer, but in some cases that could be too much
work with no gain.
If you find yourself typing (or pasting) duplicate code, like database
handling code, you can refactor that code into its own class and refer
to an instance of that class.
In general, you can always refactor a working design into a better
designed situation, so don't fix yourself on starting perfectly.

Lots of databases have optimizations, but they can be different. Know
your database. I know MS-Access and SQL server can handle repetative
simple commands well (but in different ways). Especially if you are
writing for different database types, you'd best stick to simple
commands or abstract database commands to specific objects for the
database types.

Best regards

Jack wrote:[color=blue]
> Hello,
> VB6. I have a class. The class receives a bunch of individual items from
> an asp page, then updates the database. My question is what is the best way
> you have found to update the database, one record at a time -or- send every
> that needs to get written to the database to the class, then execute a
> function to update the tables.
> Here are the two scenarios.
> -1-
> For each record I need to insert into the db, Call a method on a class,
> passing the items. In the method, execute an insert statement.
>
> -or-
> -2-
> Have a method, like 'additem'. This method would add the stuff to a
> collection in the class. Then, when the asp page is finished adding all its
> items, call a method to take each item in the collection and insert it to
> the database.
>
> I have done it both ways in the past, but was curious to know the way you
> guys do something like this. Thanks for your time.
>
>
>[/color]

[Steve Gerrard]

Re: Design question


"Jack" <jack@jack.ne t> wrote in message news:H5efe.3199 $EC6.3128@trndn y06...[color=blue]
> Hello,
> Here are the two scenarios.
> -1-
> For each record I need to insert into the db, Call a method on a class,
> passing the items. In the method, execute an insert statement.
>
> -or-
> -2-
> Have a method, like 'additem'. This method would add the stuff to a
> collection in the class. Then, when the asp page is finished adding all its
> items, call a method to take each item in the collection and insert it to the
> database.
>
> I have done it both ways in the past, but was curious to know the way you guys
> do something like this. Thanks for your time.
>[/color]

It sounds to me like you end up with one insert statement per item either way.
In scenario 1, you pass one item, and it gets inserted.
In scenario 2, the items you pass are collected, and then each one gets
inserted.

The second approach is more flexible. If you called its AddItem method just
once, and then called its DoInserts method, you would get the result of the
first approach. So setting it up that way lets you have it both ways, and you
can use whichever works best in each situation.

With the second approach, you could also create a procedure in the database that
would accept an array of data and do the multiple inserts directly in the
database.