Is there a setting to regenerate the UpdateCommand of tableadapter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm not sure whether I'm missing something or not. I reviewed the SQL
Statements generated by tableadapter when the selectcommand is select *. It
seems they are extremely bad. The updatecommand is updating every columns
and the where condition is also every column. Is there a setting or
something to regenerate the update statement during runtime so that only the
changed columns are being updated and the where condition is using the
primary key or some specific columns?

Is it realistic to use tableadaper without extending its functionalities in
real application?
 
Peter,

Keep in mind that the Where clause looks like that because that is how
concurrency is being handled.

Kerry Moorman
 
Hi Kerry,

Thanks. Is there a way to set concurrency to check only columns changed?
It seems that the tableadapter only offers optimistic concurrency or
pessimistic concurrency.


Peter
 
Peter,

I wonder if you can use a commandbuilder with a tableadapter.

I think the commandbuilder builds the kind of dynamic sql you are talking
about. A commandbuilder is usually associated with a dataadapter, which a
tableadapter tries to hide.

Kerry Moorman
 
Peter said:
I'm not sure whether I'm missing something or not. I reviewed the SQL
Statements generated by tableadapter when the selectcommand is select *.
It
seems they are extremely bad. The updatecommand is updating every columns
and the where condition is also every column. Is there a setting or
something to regenerate the update statement during runtime so that only
the
changed columns are being updated and the where condition is using the
primary key or some specific columns?

Is it realistic to use tableadaper without extending its functionalities
in
real application?
Click to expand...

Remember that what the wizard is doing is finding the "best way" to find a
row to update it. If you don't have primary keys then its best guess is to
compare all the fields original values to the database. Otherwise the
wizard has no idea of what you intend. If you don't have primary key but
know how to get the original row from the data using several of the fields
then manually change the update SQL.

Hope this helps
Lloyd Sheen
 
Lloyd,

No, the wizard knows the primary key.

The wizard is using all the columns in the Where clause to handle
concurrency issues, not to find the correct row to update.

Kerry Moorman
 
Hi Kerry,

I want to implement the Optimistic Concurrency on Update Strategy #3 - Check
Only Changed Fields or Optimistic Concurrency on Update Strategy #4 -
Implement Timestamp stated in this article
http://davidhayden.com/blog/dave/archive/2005/10/05/2503.aspx

Does .net have any prebuilt functionality I can use or I need to hardcode
these type of concurrency solution? I think both solutions are very common.


Peter
 
Peter,

Whenever possible I use option #4, a timestamp column. I think it allows for
the cleanest and most readable code.

I never use tableadapters and only rarely use updatable datasets. Instead, I
map a database row to a business object using the active record pattern.

So I control my data access code and use ado.net command objects to insert,
update and delete rows. This makes it very straightforward to use timestamps
to deal with concurrency issues.

All the Visual Studio wizards end up using tableadapters or
dataadapters/datasets. I don't have much experience with the available
concurrency options in those scenarios.

That being said, it seems to me that there is an option to use a timestamp
column for handling concurrency in the 2005 versions of the Visual Studio
wizards. You might want to look into that if you are going in that direction.

Kerry Moorman
 
Back
Top