For David Scheppa or others who have read his ADO.NET book

  • Thread starter Thread starter John Sitka
  • Start date Start date
J

John Sitka

Page 428 and the other near pages.

What is really going on when a parameter.SourceVersion is set to
DataRowVersion.Original
and why are they needed across the whole range of colums for the update
procedure.
If a row has a primary key why all the extra checks in a where clause, what
is gained?

Same with the Delete

ie

DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID AND Quantity = @Quantity
AND UnitPrice = @UnitPrice

I see this almost univerally now and I don't understand it.

how can the below be any different

DELETE FROM [Order Details]
WHERE OrderID = @OrderID
 
oops I meant


DELETE FROM [Order Details]
WHERE OrderID = @OrderID
AND
ProductID = @OrderID

the PK is composite on Order Details

same Idea though.
 
Darn it...

DELETE FROM [Order Details]
WHERE OrderID = @OrderID
AND
ProductID = @ ProductID

forgive me, I always get scatter brained in the verge of understanding
something.


John Sitka said:
oops I meant


DELETE FROM [Order Details]
WHERE OrderID = @OrderID
AND
ProductID = @OrderID

the PK is composite on Order Details

same Idea though.





John Sitka said:
Page 428 and the other near pages.

What is really going on when a parameter.SourceVersion is set to
DataRowVersion.Original
and why are they needed across the whole range of colums for the update
procedure.
If a row has a primary key why all the extra checks in a where clause, what
is gained?

Same with the Delete

ie

DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID AND Quantity = @Quantity
AND UnitPrice = @UnitPrice

I see this almost univerally now and I don't understand it.

how can the below be any different

DELETE FROM [Order Details]
WHERE OrderID = @OrderID
Click to expand...
Click to expand...
 
If you look at the proc, there are _new parameters and _Orig parameters.
You are Update/Delete ing the old stuff based on the Original Values in the
DataRow, and Replacing them with the Current Values of the DataRow. Since
you have to supply both values in this scenario, you need to know what they
both are..hence, the Params that end in _Orig have a sourceversion of
..Original, and the ones that are _new don't because Current is the default.

This may help...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataOleDbOleDbParameterClassSourceVersionTopic.asp

HTH,

Bill
 
I am assuming you are referring to the way the commandbuilder creates
update/insert/delete queries.

The reason for the checks, is just the .NET concurrency model. This says,
that the row should be updated, only if no one else has updated it since you
retrieved it (otherwise you will be overwriting someone else's changes). The
only way to make sure the row has not been updated in the mean time, is to
compare the values in the row of all the columns, to the value you currently
have. If any of them are different, then the update won't take place.

If this is not what you want, then you can roll your own
update/delete/insert statement and check just the primary key.
 
P.S. His last name is Sceppa - we must correctly spell the last names of
the ADO.NET Gurus.
 
Man, it sure is a good book.

So into it I can't type, spell or code.

But I do apologize to Mr. Sceppa for the mistake.

Marina
Thanks, Do you know why every topic shows it this way; does it really
reflect the real
world? Say a person updates a fairly wide table, 20 columns.
Are folks out there coding these 40 parameters?
And the idea of others changing values I don't understand.
No interface is 100 percent real time.

William Ryan
You are Update/Delete ing the old stuff based on the Original Values in the
DataRow, and Replacing them with the Current Values of the DataRow.
Click to expand...

Ok but I don't think I routinely update a row with a column of "price"
containing a value of $50.00
based on the fact that it is $50.00, (well once in a while). In general I
update the row based on what it represents,
an Order detail. If that detail now needs to be $75.00, why is there a
concern that it was $50.00.

But Marina says go ahead and build Commands based on primary key so I will
do that.
Just working in isolation here I sure would like to know how the rest of the
world can "think" of the default
way as correct, 'cause I can't, and feel like I'm missing out.

J.







William Ryan said:
P.S. His last name is Sceppa - we must correctly spell the last names of
the ADO.NET Gurus.
John Sitka said:
Page 428 and the other near pages.

What is really going on when a parameter.SourceVersion is set to
DataRowVersion.Original
and why are they needed across the whole range of colums for the update
procedure.
If a row has a primary key why all the extra checks in a where clause, what
is gained?

Same with the Delete

ie

DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID AND Quantity = @Quantity
AND UnitPrice = @UnitPrice

I see this almost univerally now and I don't understand it.

how can the below be any different

DELETE FROM [Order Details]
WHERE OrderID = @OrderID
Click to expand...
Click to expand...
 
John,

Thanks for the kind words further down in the thread.

Why include original values of columns when submitting
changes? It's all about the concurrency checks you want to
employ when submitting a pending change. Say a user wants to
change the CompanyName column for a row in the Customers table.
Do you want the update to succeed even if another use has changed
the value of a different column?

The section entitled Optimistic Concurrency Options,
starting at the bottom of 441, should explain your options when
building your own updating logic.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.
 
Do you want the update to succeed even if another user has changed
the value of a different column?
Click to expand...

In general yes cause what if the sale team is responsible for contact info
and CMS
and Administrative Assistants are responsible for the Revenue Items that
make up Sales Orders. The could both be editing aspects of a single
customer. And should be able to
as long as they don't break something, which is gaurenteed by Declarative Ri
and Stored proc
edits.
Company A gets sold and evolves into Company B both sales team and
administration
spring into action and update their info. One dosen't step on the other but
every
example I see assumes that they do.

Now if Company Name was a primary key and administration got a hold of that
while sales was
making their updates and that Company Name they were working on gets changed
or deleted during
the edit that is a process problem not database. Maybe the concept that is
eluding me is
on lengthy disconnects why force a nonupdatedable condition on columns that
have nothing
to do with the final state of the row.

Anyways can't wait to get to page 441 maybe I will see what eveybody else
sees.
Cause this is really bugging me. It's like the sky is blue but everybody is
saying it's red,
but their red is my blue or something like that.

Thanks all.

Recordset's have Update methods??!!! Really!
 
Yesirree,
Should have kept reading, all was answered, by 455,
but I am still really greatful to those that helped.
The sky is purple now. Nice.
 
John,
what if the sale team is responsible for contact info
and CMS and Administrative Assistants are responsible
for the Revenue Items that make up Sales Orders. The
could both be editing aspects of a single customer.
And should be able to as long as they don't break
something, which is gaurenteed by Declarative Ri and
Stored proc edits.
Click to expand...

Great example. Take a long look at the updating logic
you're using to submit pending changes. Think about a scenario
where a salesperson and an administrative assistant each retrieve
information about the same customer. Say they both make changes
to a column or two of data and submit those changes. One update
attempt will occur first and succeed. How will the second update
attempt try to change the contents of the row? Under what
circumstances do you want the update attempt to succeed/fail? Do
the UPDATE queries that each user executes include any common
columns in the SET clause?

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.
 
The automatic where clauses generated by the default adapter are designed
with belt-and-braces to be as likely as possible to work, without
necessarily considering efficiency.

All the extra checks in the whereclause guarantee that the row has not been
altered by someone else since you read it. Because of the possibility of
nulls those checks are complicated, requiring 3(?) comparisons per column.

This is fine in some applications, but not in others. for example with a
wide table the size and complexity of the whereclause will slow the server
down (Though I have not tested this to see how large the effect is on eg a
50 column table)

If all users of the data agree to a "contract" to always increment a
particular column called something like "UpdateGeneration" if they update
the table (which you can enforce through subclassed data adapter design as
long as no other application will be updating the data) then you can check
the value of that column in the whereclause of deletes and updates and drop
all of the other "surplus" column checks. From memory I think this option is
considered in Sceppa.

Tom
 
Thanks Heinz,
It's starting to click now,
I have been a little obsessed with this default behaviour and everytime I
see it the brain just goes into a deep freeze. I didn't understand the
emphasis
and why everyone seemed to be on board with it. Now after digesting
some more of Chapter 10 in ADO.NET I see it is just the jumping off point,
nothing has changed and a person is free to view the habituation of these
"all column wheres" or enthusiastic concurrency checking however they wish.

J.




Heinz Kiosk said:
The automatic where clauses generated by the default adapter are designed
with belt-and-braces to be as likely as possible to work, without
necessarily considering efficiency.

All the extra checks in the whereclause guarantee that the row has not been
altered by someone else since you read it. Because of the possibility of
nulls those checks are complicated, requiring 3(?) comparisons per column.

This is fine in some applications, but not in others. for example with a
wide table the size and complexity of the whereclause will slow the server
down (Though I have not tested this to see how large the effect is on eg a
50 column table)

If all users of the data agree to a "contract" to always increment a
particular column called something like "UpdateGeneration" if they update
the table (which you can enforce through subclassed data adapter design as
long as no other application will be updating the data) then you can check
the value of that column in the whereclause of deletes and updates and drop
all of the other "surplus" column checks. From memory I think this option is
considered in Sceppa.

Tom

John Sitka said:
Page 428 and the other near pages.

What is really going on when a parameter.SourceVersion is set to
DataRowVersion.Original
and why are they needed across the whole range of colums for the update
procedure.
If a row has a primary key why all the extra checks in a where clause, what
is gained?

Same with the Delete

ie

DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID AND Quantity = @Quantity
AND UnitPrice = @UnitPrice

I see this almost univerally now and I don't understand it.

how can the below be any different

DELETE FROM [Order Details]
WHERE OrderID = @OrderID
Click to expand...
Click to expand...
 
Back
Top