Bug in SqlCommand.ExecuteNonQueryAsync()

[christian-ruud]

When calling await command.ExecuteReturnValueAsync() inside a using block I get an exception saying a task was cancelled.

If I call await Task.Delay(100) before the end of the using scope, the call succeeds.

Similarly, if the call is to a stored procedure with a return value, I get a NullReferenceException if I attempt to read the return value without delaying first.

[Wraith2]

ExecuteReturnValueAsync isn't a method this library provides. Is it a user extension you've written or do you have the wrong name? Can you provide a simple example piece of code to repro the error?

[christian-ruud]

Sorry, seems I was a bit quick and typed the name of the wrong method. The problem is with ExecuteNonQueryAsync.

The following code examples are shortened versions of code taken from an IDisposable class. The containing class is instatiated with a using scope, and disposes all disposable objects in its Dispose method.

In the example below an exception is thrown stating that a task was cancelled:

 try
 {
      return await command.ExecuteNonQueryAsync();
 }
 catch (Exception exception)
 {
      // omitted for brevity
      throw;
 }
 finally
 {
       // omitted for brevity
       await connection.CloseAsync();
 }

If I do this instead it works as expected:

var result = await command.ExecuteNonQueryAsync();
await Task.Delay(100);
return result;

The method ExecuteReturnValueAsync is, quite correctly, a method I've written that resides in a differerent class, but this method also experiences a consequence of what I believe is the same bug:

await command.ExecuteNonQueryAsync();
try
{
    // the below line throws a NullReferenceException unless a delay is introduced.
    return Convert.ToInt32(command.Parameters["RETURN_VALUE"].Value);
}
finally
{
    await connection.CloseAsync();
}

[cheenamalhotra]

Hi @christian-ruud

We'll investigate and get back to you soon on this issue.

[christian-ruud]

Hi,

I recently discovered the Microsoft.Data.SqlClient implementation and decided to test if this library has the same bug.
It does; with a slight variation. I only tested one of the scenarios, but I got an InvalidOperationException instead, stating that the connection is closed.

The underlying problem seems to be the same: When calling await ExecuteNonQueryAsync it proceeds to the next instruction before the awaited operation is actually complete. Obviously switching await ExecuteNonQueryAsync(); for just ExecuteNonQuery(); works fine in both libraries.

[cheenamalhotra]

cc @DavoudEshtehari

[christian-ruud]

Is there any progress on this one?

[DavoudEshtehari]

Hi Christian,
I didn't see any exception with your sample code.
We have a work in progress for a similar exception at PR #579. Can I ask you to give it a try with your source code if your project is under .NetCore framework?
If It doesn't work for you, please provide a repro app with its stack trace for more investigations.

[christian-ruud]

This is getting really weird.

I copied the failing code into LinqPad to create a simple piece of code you could test, but so far I've been unable to reproduce the error in LinqPad.

However, I still get the exception in the original codebase . In this case that's
Blazor Server -> .net standard 2.1 dll -> .net standard 2.1 dll. I haven't tried the PR, as I want to predicably reproduce with a code snippet first.

Essentially

command.ExecuteNonQuery() works both in production code and code snippet
await commandExecuteNonQueryAsync() works in code snippet but fails in production code.

The code is otherwise identical.

It seems like it could be dependant on load, perhaps? Or maybe the database? How much data is in the table? Maybe it's different for a web project somehow?

I'll keep trying to figure out the circumstances under which the error is triggered.
I can think of the following things to try, would be great if you have any suggestions:

• I'll try making a console app instead of using LinqPad, in case it gives me different results.
• I'll try using a blazor app to reproduce in case it gives me different result
• I'll try making the calls against the actual production database.

[cheenamalhotra]

@christian-ruud

You may also take a glance at some of the open issues if they are related to what you are experiencing.
Let us know when you get the issue reproduced in an isolated environment.

[cheenamalhotra]

@christian-ruud

Do you have any update on this issue?

[christian-ruud]

Yes - The problem seemingly disappeared on its own. I haven't been able to figure out why - the exact same code that failed previously suddenly worked with no changes. This was true without updating the packages. I don't know if it was some weird condition on the server that caused it, or if it was something else, maybe a platform update. Anyway, I am no longer able to reproduce, and I've actively tried to reproduce it. Previously I could reproduce at will. Skaff deg Outlook for Android<https://aka.ms/ghei36>

…

________________________________ From: Cheena Malhotra <notifications@github.com> Sent: Wednesday, January 13, 2021 9:30:07 PM To: dotnet/SqlClient <SqlClient@noreply.github.com> Cc: Christian Ruud <cru@develop.no>; Mention <mention@noreply.github.com> Subject: Re: [dotnet/SqlClient] Bug in SqlCommand.ExecuteNonQueryAsync() (#536) @christian-ruud<https://github.com/christian-ruud> Do you have any update on this issue? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#536 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AO2MAHA4KPTDXXL3S6CY62LSZX7E7ANCNFSM4MO4X3AA>.

[cheenamalhotra]

Closing as confirmed above.