CODEWITHSUNDEEP
.netpostgresqlnpgsql
Casey
Casey

Reputation: 510

Npgsql Command with Multiple Statements

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

Upvotes: 5

Views: 4260

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16692

Note that this issue was resolved in Npgsql 3.1.

Upvotes: 2

Related Questions