Data Adapter - Separate calls to Db?

[Zorpiedoman]

Up to this point, I have avoided using the Data Adapter to do my updates back
to the database. I found it more straight forward to build my own SQL
statements, and just execute them directly.

I'm starting to see the light and have some good reasons I may start using
the DataAdapter and utilizing it's update functionality.

In reading up on this it APPEARS that EACH row of a data table that has been
changed makes a SEPARATE call back to the database. Therefore, if you had a
1000 row table, and changed a field in every row, DataAdapter.Upd ate would
make 1000 separate runs to the database.

Is this right? I can understand why this might be, as it gives quite a bit
of control to the developer, but from an efficiency point of view, it's crazy.

Should I first test to see how many rows have been updated, and if it is
over a dozen or so, go back to my creating my own SQL statement so it can get
sent all at once?

OR, is there some sort of method I have not yet discovered that allows the
1000 update statements to be created as a single string that I can manipulate
and call in a bulk statement?

Just looking for some comments, feedback, and ideas.

--
--Zorpie

[Marina]

Re: Data Adapter - Separate calls to Db?

Yes, this is known and documented. The Update in the adapter is *not* a bulk
update feature. So it is not too efficient, since you end up with a lot of
round trips to the database server.

The 2.0 framework should have improvements in this area.

"Zorpiedoma n" <nowheremane@be atles.com> wrote in message
news:FF9D8CF7-6A72-4704-B9AD-2862DDAC5BAA@mi crosoft.com...[color=blue]
> Up to this point, I have avoided using the Data Adapter to do my updates
> back
> to the database. I found it more straight forward to build my own SQL
> statements, and just execute them directly.
>
> I'm starting to see the light and have some good reasons I may start using
> the DataAdapter and utilizing it's update functionality.
>
> In reading up on this it APPEARS that EACH row of a data table that has
> been
> changed makes a SEPARATE call back to the database. Therefore, if you had
> a
> 1000 row table, and changed a field in every row, DataAdapter.Upd ate would
> make 1000 separate runs to the database.
>
> Is this right? I can understand why this might be, as it gives quite a
> bit
> of control to the developer, but from an efficiency point of view, it's
> crazy.
>
> Should I first test to see how many rows have been updated, and if it is
> over a dozen or so, go back to my creating my own SQL statement so it can
> get
> sent all at once?
>
> OR, is there some sort of method I have not yet discovered that allows the
> 1000 update statements to be created as a single string that I can
> manipulate
> and call in a bulk statement?
>
> Just looking for some comments, feedback, and ideas.
>
> --
> --Zorpie[/color]

[Kerry Moorman]

RE: Data Adapter - Separate calls to Db?

Zorpie,

I don't see how you can write an SQL statement to change data in several
rows and get it all sent at once. You are going to need to execute an Update
for each changed row, just like the data adapter, right?

What am I missing here?

Kerry Moorman


"Zorpiedoma n" wrote:
[color=blue]
> Should I first test to see how many rows have been updated, and if it is
> over a dozen or so, go back to my creating my own SQL statement so it can get
> sent all at once?
>
>
> Just looking for some comments, feedback, and ideas.
>
> --
> --Zorpie[/color]