SqlDataAdapter.Update vs. SqlCommand.ExecuteNonQuery

[Ryan Jacops]

To issue update commands, should I use the Update method
of the DataAdapter or use ExecuteNonQuery method of the
Command object?

Originally I thought the Update method would only update
records in the dataset/datatable. I then learned that you
first had to write an update command and assign it to the
UpdateCommand of the DataAdapter. Further experimentation
led me to find out that UNLESS you explicitly define the
update command WITH THE SAME FILTERS as your select
command that filled the datatable/dataset, the Update
method can potentially update more than just the
datatable/dataset.

That being the case, why not save yourself the extra code
and just simply issue the explicit update command with the
ExecuteNonQuery???? Am I missing something here?

 

[Ryan Jacops]

Thanks for your reply.

Concerning the CommandBuilder and Configuration Wizard
benefits, I see your point there. However, to me those
are NOT benefits since I prefer to avoid wizards and auto-
generatored code (I've found from experience that in
general as you advance, you ultimately must abandon the
wizards and go with a custom code-only solution that you
wrote yourself and fully understand.)

Concerning the disconnected mode benefits, as far as I
know this is what I'm using. I'm using Datasets (not
DataReaders) that I pass back and forth between different
layers. But what I don't like is that if you're not
careful with the updatecommand property of the
dataadapter, it has the potential to update EVERYTHING in
the underlying table, not just what's in the disconnected
dataset. I think that since you are passing a
disconnected dataset or datatable to the Update method of
the DataAdapter, it should ONLY update those records in
the dataset or datatable!

In my case, I don't need to iterate through every record
to decide what to update. I just need to set a bit column
to 1 for all records in the dataset after I'm done
processing them (UPDATE Table1 Set Column1 = 1 WHERE
Column2 BETWEEN 'MM/DD/YYYY' AND 'MM/DD/YYYY'). Maybe if
I didn't know ahead of time what would be changed (like
user-edited data), then maybe I could see the benefit of a
parameterized update command.

Any other thoughts?

Thanks,
Ryan

 

[Ryan Jacops]

Thank You Michael. This is good to know. Choice #2
surprises me a little since it seems quite a bit more
coding than I expected (compared to the UpdateBatch method
in ADO which magically somehow knew what records to
update), but I'm sure it's for good reason. Thanks!

 

[Michael Lang]

Choice #2 wouldn't be good for your particular situation... What I meant
is, it is how you do it when you are making different changes to individual
records, not a replacement for a batch update. Sorry that was a little
unclear.