OleDbDataAdapter.Update or OleDbCommand.ExecuteNonQuery ?

[Guest]

Hi all,

I've been wondering which one of these to use
in my application, in terms of efficiency:

the Update method of the DataAdapter or the
ExecuteNonQuery method of the Command?

Using OleDbDataAdapter.Update() is so much
easier than creating a command object with all
the parameters, as it just involves calling the
method and passing it a DataTable object.
(Some great design and elegancy from the side
of the .NET architects ).
I'm trying to keep my application as connected
as possible, so that when I update/insert/delete
a row, the data source would be immediately
updated accordingly.

I wonder if the DataAdapter iterates over the
changed rows only, or over all the rows in the
DataTable... Interesting

Thank you :

 

[William Ryan eMVP]

Hi all,

I've been wondering which one of these to use
in my application, in terms of efficiency:

There's not much difference if you mean performance comparing updating one
row with ExecuteNonQuery vs. Update - remember that Update fires teh update
command one row at a time based on teh Rowstate of the row. In terms of
maintaining the code though, constructing x number of sql statements and
firing ExecuteNonQuery x times is a lot bulkier

the Update method of the DataAdapter or the
ExecuteNonQuery method of the Command?

Using OleDbDataAdapter.Update() is so much
easier than creating a command object with all
the parameters, as it just involves calling the
method and passing it a DataTable object.
(Some great design and elegancy from the side
of the .NET architects ).
I'm trying to keep my application as connected
as possible, so that when I update/insert/delete
a row, the data source would be immediately
updated accordingly.

Don't do this! Close your connections when you are done with them. This
isn't ADO and ADO.NET isn't ADO with a few new features. Architecturally,
it's whole paradigm is such that there's a connected and disconnected mode,
but with either mode, you shouldn't leave connections open after you are
done with them. You can trap concurrency exceptions to see if data has
changed, but making it 'connected' is really not advised. If you want
updates to happen frequently, trap some other event in the UI for instance
and fire update then, send the update, close the connection.

I wonder if the DataAdapter iterates over the
changed rows only, or over all the rows in the
DataTable... Interesting

It only iterates over changed Rows. Moreoever,Calling update only does
something if the DataSet/DataTable .HasChanges(). If it doesn't, call
Update all year, nothing will happen. To prove it, trap the StateChanged
event of the connection. Call DataAdapter.Fill then create a loop to 1000.
Call DataAdapter.Update immediately after the Fill statement (so you know
HasChanges will be false) YOu'll notice the state never changes b/c it
never opens b/c there are no changes so it does nothing.

HTH,

Bill