Please explain VS.NET created DataAdapter commands

[groston]

The syntax of the SQL commands created by VS.NET puzzle me. Hopefully you can offer a rational explanation.

Here is an example table, name myTable:
ValID (int, Identity, primary key)
Value (varchar(50))

From VS.NET toolbox, I select SqlDataAdapter. I enter the connection and ask it to generate SQL commands in the program. The SQL command I provide is:

Select * From myTable

Here are the SQL commands generated:

SelectCommand
SELECT ValID, Value FROM myTable

InsertCommand
INSERT INTO myTable(Value) VALUES (@Value);
SELECT ValID, Value FROM myTable WHERE (ValID = @@IDENTITY)

UpdateCommand
UPDATE myTable SET Value = @Value
WHERE (ValID = @Original_ValID) AND
(Value = @Original_Value OR @Original_Value IS NULL AND Value IS NULL);
SELECT ValID, Value FROM myTable WHERE (ValID = @ValID)

The Select command is perfectly understandable.

For the Insert command, what is the purpose of the Select statement?

For the Update command:
- Since ValID is guarenteed to be unique, why the complex WHERE clause?
- What is the purpose of the Select statement?

Thanks!

----

Gerry Roston
gerry@pairofdocs.net

 

[dragonwell]

For the Insert command, what is the purpose of the Select statement?

When you insert a new row, you would like to get back the value of the Identity column to know that a new row was actually inserted and to assign to your object. The @@identity operator in T-SQL gets the value of the last generated Identity value. Interesting that they didn't choose to use use SCOPE_IDENTITY(), which is generally the preferred method since @@identity could possibly return the wrong value if another row is inserted between the time your INSERT statement gets executed and the SELECT @@identity is executed.

For the Update command:
- Since ValID is guarenteed to be unique, why the complex WHERE clause?
- What is the purpose of the Select statement?

The complex where clause is to enforce optimistic concurrency. If another user has updated the same row after you originally selected the row into your dataset, your update should not occur. This is done by checking each column in the original record to see that it matches what you are submitting as original_value - if it finds that one of the column's value does not match what your original version was, you know that another update has already occured and it violates concurreny rules.

The select statement... not sure about that!

HTH



David