Npgsql Team with Multiple Applications

Question

In Npgsql V2, I can use the following code to update a record and return updated record values ​​with a single Npgsql command.

The property command.CommandTextcontains both an UPDATE statement and a SELECT statement. The idea is that when the call is command.ExecuteReaderexecuted, both commands are executed, but the results of the SELECT command will be returned (since this was the last command).

After upgrading to Npgsql 3.0.3.0, the value in the datareader (from the SELECT statement) is still the original value, not the updated value (line Return dr("action")in code). I tried all different IsolationLevel and they all give the same results (as if the SELECT statement does not see the updated value from the INSERT statement). The value is updated correctly in the database (if I re-request the record, it has an updated value).

I can separate this and use two separate NpgsqlCommand (one for INSERT and one for SELECT), but I don't want to create a second round on the server.

This is a simplified function, the purpose of the real function is to update the record on the database server, and then update the object in the application with any other fields that the server updated (for example, the "last_updated" timestamp field that is updated on the server every time the 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

Answer 1

Please note that this problem was resolved in Npgsql 3.1.