System.Data.Common.DbCommand.ExecuteNonQuery() execute “merge into" returns a negative number

Question

System.Data.Common.DbCommand.ExecuteNonQuery() execute “merge into" returns a negative number

陈啟森 1

Database: Azure SQL
Programming language: C#.NET

System.Data.Common.DbCommand.ExecuteNonQuery() executes the merge into statement. It is not clear why the negative number is returned. Has anyone encountered a similar problem?
The program has been running for more than half a year and has not had this problem, and it has only recently appeared suddenly.
This phenomenon cannot reappear locally, and sometimes the same sql returns not a negative number, sometimes it returns a negative number, so I have not been able to find the reason.

The structure of the executed merge into statement is as follows:
MERGE
INTO TABLE_A AS A
USING (
SELECT ...
) AS B
ON (
...
) WHEN MATCHED THEN UPDATE
SET
...
WHEN NOT MATCHED THEN
INSERT (
...
)
VALUES (
...
);

Dan Guzman 9,401 Reputation points

2021-05-27T11:06:31.13+00:00

What underlying provider/driver are you using? Is it System.Data.SqlClient?
陈啟森 1 Reputation point

2021-05-28T02:03:21.507+00:00

using System.Data.Common;

It is roughly as follows:
DbProviderFactory DBProviderFactory = DbProviderFactories.GetFactory(connSettings.ProviderName);
var command = DBProviderFactory.CreateCommand();
public int ExecuteNonQuery(DbCommand command)
{
using (var connection = GetAmbientConnection(true))
{
command.Connection = connection.Connection;
int result = command.ExecuteNonQuery();
return result;
}
}
Dan Guzman 9,401 Reputation points

2021-05-28T10:11:55.47+00:00

My question was what provider are you using. What is the value of connSettings.ProviderName?
陈啟森 1 Reputation point

2021-05-28T10:41:34.997+00:00

System.Data.SqlClient

2 answers

Your answer

Dan Guzman 9,401 Reputation points

2021-05-27T11:06:31.13+00:00

What underlying provider/driver are you using? Is it System.Data.SqlClient?
陈啟森 1 Reputation point

2021-05-28T02:03:21.507+00:00

using System.Data.Common;

It is roughly as follows:
DbProviderFactory DBProviderFactory = DbProviderFactories.GetFactory(connSettings.ProviderName);
var command = DBProviderFactory.CreateCommand();
public int ExecuteNonQuery(DbCommand command)
{
using (var connection = GetAmbientConnection(true))
{
command.Connection = connection.Connection;
int result = command.ExecuteNonQuery();
return result;
}
}
Dan Guzman 9,401 Reputation points

2021-05-28T10:11:55.47+00:00

My question was what provider are you using. What is the value of connSettings.ProviderName?
陈啟森 1 Reputation point

2021-05-28T10:41:34.997+00:00

System.Data.SqlClient

Answer 1

Dan Guzman 9,401

In my experience, System.Data.SqlClient.SqlCommand.ExecuteNonQuery() will return the sum of the rows affected by the batch as long as the session setting is SET NOCOUNT OFF (the default). A negative value, -1, will be returned only when SET NOCOUNT ON is specified. The row count value returned should be the same whether the SqlCommand.ExecuteNonQuery() is invoked directly or via the base type System.Data.Common.DbCommand.ExecuteNonQuery()base type (as in your case).

Are you reusing the same connection for different queries? If so, perhaps a previous command executed SET NOCOUNT ON; and the setting was still effective when the MERGE statement was executed on the same connection. Check for SET NOCOUNT ON; in other queries that might use the same connection.

陈啟森 1 Reputation point

2021-05-31T02:59:13.33+00:00

SET NOCOUNT is the default value and has not been modified. And the same SQL is executed twice, the first return value is greater than 0, the second return value is negative, the interval between the two is very short.
Dan Guzman 9,401 Reputation points

2021-05-31T10:58:06.503+00:00

Do you have MultipleActiveResultSets=True specified in the connection string? If so, and you don't actually use the feature, try removing it.
陈啟森 1 Reputation point

2021-06-01T09:44:21.157+00:00

The connection string does not set MultipleActiveResultSets.
Dan Guzman 9,401 Reputation points

2021-06-03T11:46:20.537+00:00

I tried to reproduce your issue without success. The row count is always non-negative after executing a batch containing a single MERGE statement. Perhaps there is a detail not apparent in the code you've shown that causes in the issue. You mentioned the symptoms occur when the interval between executions is very short so perhaps there is a threading issue? What is the implementation of GetAmbientConnection()?

Without SET NOCOUNT ON, the database engine returns TDS protocol DONE tokens containing row counts for each executed statement. AFAIK, SqlCommand.ExecuteNonQuery() uses those counts for scalar value result after the batch completes without intimate knowledge of the statements in the batch. A network level trace can capture the TDS messages and tokens returned to verify they contain the expected counts. If the counts are correct the issue is on the client side.

Answer 2

Olaf Helper 47,436

Has anyone encountered a similar problem?

What for a problem? It's a documented behavior, see DbCommand.ExecuteNonQuery Method
"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1 ."
MERGE is not one of the mentions DDL commands, even because it's a MS SQL Server specifc command, not directly available in other RDBMS, e.g. in Oracle the similar command is UpSert.

陈啟森 1 Reputation point

2021-05-28T01:55:03.977+00:00

The database used is the azure version of ms sql server.
Moreover, the return value obtained by the program before the problem is always greater than or equal to 0,
It is not clear what caused the occasional return of negative numbers recently.

Share via

System.Data.Common.DbCommand.ExecuteNonQuery() execute “merge into" returns a negative number

2 answers

Your answer