SqlDataReader.RecordsAffected incorrectly returns -1 when using INSERT...OUTPUT.inserted

We rely on SqlDataReader.RecordsAffected to count the number of rows modified by a stored procedure.

MSDN states its definition as:

The number of rows changed, inserted, or deleted; 0 if no rows were affected or the statement failed; and -1 for SELECT statements... The value of this property is cumulative. For example, if two records are inserted in batch mode, the value of RecordsAffected will be two.

It appears that ADO.NET misinterprets any statement using the OUTPUT clause as a SELECT statement and returns -1 for RecordsAffected instead of the actual count of modified rows.

For example:

CREATE TABLE dbo.example (
      a INT
    , b VARCHAR(10)
    , c DATETIME2(3)    DEFAULT(SYSUTCDATETIME())
);

INSERT INTO dbo.example (
     a
   , b
)
OUTPUT inserted.c -- Comment out this line and RecordsAffected goes from -1 to 1.
VALUES (
     1
   , 'blah'
);

Is this ADO.NET behavior by design or by error?

For the record, we plan to change our code to explicitly capture counts of modified rows using @@ROWCOUNT and return them as OUTPUT开发者_如何学运维 parameters in our stored procedures.

Well, it certainly helps to pay close attention to the documentation.

Again, from MSDN:

The RecordsAffected property is not set until all rows are read and you close the SqlDataReader.

This is somewhat of a lie.

RecordsAffected is set before you close SqlDataReader, but not all the time. We were querying it before closing the object and that always worked fine--until we starting using OUTPUT inserted in our T-SQL.

Querying RecordsAffected after closing your SqlDataReader yields the correct modified row count, with or without the OUTPUT clause.