In Npgsql V2, I could use the following code to update a record, and return the updated record values using a single Npgsql command.
The command.CommandText property contains both an UPDATE statement and also a SELECT statement. The idea being that when command.ExecuteReader is called both commands will run, but the results from the SELECT command will be returned (since it was the last command).
After upgrading to Npgsql version 3.0.3.0 the value in the datareader (from the SELECT statement) is still the original value, and not the updated one (the Return dr("action") line in the code). I have tried every different IsolationLevel and they all give the same results (as though the SELECT statement is not seeing the updated value from the INSERT statement). The value is properly updated in the database (if I re-query the record it has the updated value).
I can split this and use two separate NpgsqlCommand (one for the INSERT, and a second for the SELECT), but I don't want to create a second round-trip to the server.
This is a simplified function, the purpose of the real function is to update a record on the DB server, and then update the object in the application with any other fields that the server updated (such as the "last_updated" timestamp field which is updated on the server each time a record is updated).
Is there a way to make this work with Npgsql V3.0.3.0?
Public Function UpdateRecordExample(id As Guid, newAction As String) As String
Using conn As New NpgsqlConnection("Connection String Here")
Using trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted)
Dim command = conn.CreateCommand
command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
command.CommandText = "Update pm.action_item SET action=@action WHERE id=@id; SELECT * FROM pm.action_item WHERE id=@ID;"
command.Parameters.Add(New NpgsqlParameter("id", id))
command.Parameters.Add(New NpgsqlParameter("action", newAction))
Using dr = command.ExecuteReader
If dr.Read Then
Return dr("action") 'This is still the original value and not "newAction"
Else
Throw New DBConcurrencyException
End If
End Using
End Using
End Using
End Function
