one DataAdapter vs. many DbCommands

[Guest]

Hi

I have a design decision to make. I must choose between a DataAdapter.Update() and many DbCommands (actually SqlCommands)
Let's assume that a small percentange of the columns need to be modified on average. This would suggest that long strings of SQL will be traveling to the server and back (assuming we have corresponding SELECT statements appended to the UPDATE statements) unnecessarily. Issuing taylor made commands that only send the necessary SQL seems at first sight to be much more efficient. However, I do not know what optimizations are done by ADO.NET behind the scenes when using DataAdapters. It occurs to me that at least, the UpdateCommand's string will be prepared and compiled at the server significantly increasing the overall efficiency

So, is there a clear answer as to which mechanism I should use? Would someone care to share their experience and/or insight in this matter

Thanks
Juan Dent

 

[William Ryan eMVP]

Why not used Stored Procedures? Also, if you use Params, you'll get a lot
of built in optimizations b/c it can cache the execution plan.

Hi,

I have a design decision to make. I must choose between a

DataAdapter.Update() and many DbCommands (actually SqlCommands).

Let's assume that a small percentange of the columns need to be modified

on average. This would suggest that long strings of SQL will be traveling to
the server and back (assuming we have corresponding SELECT statements
appended to the UPDATE statements) unnecessarily. Issuing taylor made
commands that only send the necessary SQL seems at first sight to be much
more efficient. However, I do not know what optimizations are done by
ADO.NET behind the scenes when using DataAdapters. It occurs to me that at
least, the UpdateCommand's string will be prepared and compiled at the
server significantly increasing the overall efficiency.

So, is there a clear answer as to which mechanism I should use? Would

someone care to share their experience and/or insight in this matter?

 

[William Ryan eMVP]

Hi,

Thanks for answering so quickly.

What do you mean by using "Params"?

SqlParameters.. cmd.Parameters.Add("@Whatevr", SqlDbType.VarChar, 50).Value
= "Whatever";

Where does your answer fit in the DataAdapter vs. DbCommands dimension?
The DataAdapter uses SqlDbCommands so it's really not a big distinction or a
distinction at all. It's simply a matter of do you want to set the commands
once and do everything you need wtih two statements, Update and Fill, or do
you want to itterate the table and build commands firing them over and over
with different variables in the parameters.

What do you think about my considerations regarding the impact of long SQL

strings going back and forth when the updated columns are usually less than
50% of total updatable columns?
It's the cost of doing business if you don't want to use Procs. Using
parameters reduces this burden somewhat, but doesn't eliminate it.

In answering above questions, assume I am unable to use Stored Procs.

That's unfortunate, it complicates things greatly

In your suggestion that I use stored procs, are you saying to use

DataAdapters whose commands call stored procedures, or are you saying to use
DbCommands that call stored procedures?
Use DataAdapters whose commands call stored procs, which are in fact
DBCOmmand that call stored procs. Using Procs is safer, faster, easier to
maintain, easier to build etc etc etc.

Head over to www.betav.com and under his Articles section , in MSDN you ll
find an article, Weaning Developers off of the command builder..it will
address most of this.

HTH,

Bill