question

chenqs000 avatar image
0 Votes"
chenqs000 asked DanGuzman commented

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

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 (
...
);

dotnet-csharpazure-sql-database
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What underlying provider/driver are you using? Is it System.Data.SqlClient?

0 Votes 0 ·

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;
}
}

0 Votes 0 ·

My question was what provider are you using. What is the value of connSettings.ProviderName?

0 Votes 0 ·
Show more comments
DanGuzman avatar image
1 Vote"
DanGuzman answered DanGuzman commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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

0 Votes 0 ·

The connection string does not set MultipleActiveResultSets.

0 Votes 0 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered chenqs000 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·