What are OleDbParameter names with 'Original_' prefix?

[JoWilliam]

Hi, I'm new to all this OleDb stuff. I'm using VB in Visual Studio .Net
2003.

The Data Adapter wizard for the OleDbDataAdapter control generates the
following OleDbUpdateCommand object for the query "SELECT Reference, Status,
DateCreated FROM Questions":

Me.OleDbUpdateCommand1.CommandText = "UPDATE Questions SET Reference
= ?, Status = ?, DateCreated = ? WHERE (Reference " & _
"= ?) AND (DateCreated = ? OR ? IS NULL AND DateCreated IS NULL) AND
(Status = ? " & _
"OR ? IS NULL AND Status IS NULL)"
Me.OleDbUpdateCommand1.Connection = Me.dbConn
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Reference",
System.Data.OleDb.OleDbType.VarWChar, 13, "Reference"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Status",
System.Data.OleDb.OleDbType.VarWChar, 20, "Status"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("DateCreated",
System.Data.OleDb.OleDbType.DBDate, 0, "DateCreated"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Reference",
System.Data.OleDb.OleDbType.VarWChar, 13,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Reference", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_DateCreated",
System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "DateCreated",
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_DateCreated1",
System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "DateCreated",
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Status",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Status", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Status1",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Status", System.Data.DataRowVersion.Original, Nothing))

What are the 2 parameters with prefix 'Original_' and suffix '1' used for?

What are the other 3 parameters prefixed by 'Original_' used for? Are they
used to hold the previous value of the column in case the update needs to be
rolled back?

The table has one primary key column, Reference. Therefore I can change the
update command to:

UPDATE Questions SET Status = ?, DateCreated = ? WHERE (Reference = ?)

If I do this, can I delete the Original_DateCreated1 and Original_Status1
parameters in the above code?

thanks,

 

[William \(Bill\) Vaughn]

This generated code is used to manage update concurrency--to determine if
the row has changed since last accessed. The "original" values are compared
(in the UPDATE) to see if the current values match the original values. If
they do, the UPDATE succeeds and returns 1 row affected, if not it fails and
the Update method throws an exception.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[JoWilliam]

Hi and thanks very much for your reply.

The Data Adapter wizard generated the following UpdateCommand:

UPDATE Questions SET Reference = ?, Status = ?, DateCreated = ? WHERE
(Reference = ?) AND (DateCreated = ? OR ? IS NULL AND DateCreated IS NULL)
AND (Status = ? OR ? IS NULL AND Status IS NULL)

Since the table has a primary key column (Reference), the UPDATE command
doesn't need to include it in the SET list and the WHERE clause doesn't need
the other columns. Therefore I can simplify the command as:

UPDATE Questions SET Status = ?, DateCreated = ? WHERE (Reference = ?)

Is my reasoning correct, and is there anything else I need to add/change to
support this new UPDATE statement?

thanks,

 

[William \(Bill\) Vaughn]

In a single-user environment you're absolutely correct--well, almost (but
I'll get to that).
In a multi-user environment, the Update method expects to update one and
only one row. The PK reference deals with that issue (as you said). However,
the Update method also expects to update the row you originally read--it is
programmed to fail if the row has changed since it was last read. That's
what the additional WHERE clause elements do. They compare the original row
data with the current contents of the row. This is a crude way to test to
see if any changes have been made. As I discuss at length in my book, there
are better ways to make this test, this approach assumes that you have
rights to the other columns and many other (troubling) factors.

As to the single-user issue: It's entirely possible to open more than one
connection on a database from an application and the app can attempt to
update the same row from more than one part of the logic. This makes the
single user application work like (and need the protections of) a multiuser
app.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[JoWilliam]

Bill, thanks for your further explanation.

In a multi-user environment, the Update method expects to update one and
only one row.

Does that mean I should call the Update method each time I update a row?

Similarly, should I call Update each time I Add or Remove a row from a
DataTable?

I've been adding 20 records at a time to my DataSet/DataTable and then
calling the Update method and it seems to work fine.

thanks,

 

[William \(Bill\) Vaughn]

Well, no, you don't have to call the DataAdapter/TableAdapter Update on each
change--these are cached in memory. When you execute Update, ADO.NET walks
through the DataTable and posts all of the changes (one at a time--unless
you have batch mode enabled)--but you've already figured that out...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------