performance on updating a DataTable

  • Thread starter Thread starter john
  • Start date Start date
J

john

If I make a bunch of changes to a DataTable and then update its
contents back to the db, ADO.Net formats my changes into update,
insert, and delete statements and executes them on the DB. I have two
questions about this:

1. Should I manually open the connection before updating the
DataTable? It would make sense for the connection to be open the whole
time that ADO.Net is executing all the SQL statements, but maybe it is
smart enough to keep the connection open the whole time without me
telling it to do so.

2. The SQL statements created by ADO.Net are ugly and verbose compared
to what I would create if I was just building the statements in my
code and executing them myself. I am guessing it takes SQL Server
extra time to parse/figure out the ugly/verbose SQL statements created
by ADO.Net. So do I get better performance if I do all my changes to
the DataTable and then call update() on it, or if I don't make any
changes to the DataTable and build the SQL statements myself and
execute them?

Thanks in advance.
john
 
John:

The dataAdapter takes care of opening and closing connections for you..so if
you are using the dataadapter to send back your updates, it's probably best
to let it take care of it for you. On the other hand, opening a connection
is a 'risky' operation in the sense that many things other than your code
can cause a failure. So, if you want to open it manually and wrap it in a
try catch block and try to respond to an open failure, there's a good case
to be made.

As far as the ugly SQL Code..the answer depends on a few things. A lot of
the verbosity that comes with the DataAdapter Configuration wizard is
actually pretty helpful. Parsing dynamic sql vs passing in typed parameters
to avoid verbosity probably isn't the best idea, but it depends on your
specific scenario. Bill Vaughn has an article here...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/ht
ml/commandbuilder.asp that was really eye opening... Also, the DataAdapter
can use Stored Procs <I'm a blind partisan on this issue> which means much
cleaner client code as well enhanced performance and security.

However, rolling out your own update logic is probably a good idea,
particularly if you know the nuances of your DB.

Good Luck,

Bill
 
Back
Top