SqlClient.SqlCommand.ExecuteScalarAsync behaving like synchronous call

Question

I've stripped my application down to a minimal POC and I'm still getting the same effect. It appears that ExecuteScalarAsync is behaving like a synchronous call. I thought that when the await is encountered the remainder of the code in the async method is paused and the message pump goes back and gets another message from the message queue allowing the UI to continue. When the Scalar call completes, the remainder of the async method is then put back in the message queue so that it will complete.

When this little application runs, the TestConnectionAsync method hangs the UI and no other messages execute until the ExecuteScalarAsync call times out.

Am I doing something wrong, or is this async method behaving like a synchronous method?

The form has two buttons. The first runs the async method and the second tries to cancel the async method with a token. I never get a chance to click the second button.

Form1.cs

public partial class Form1 : Form
{
    private DB _db = new DB();
    private string _nl = Environment.NewLine;

    public Form1()
    {
        InitializeComponent();
    }

    private async void button1_Click(object sender, EventArgs e)
    {
        textBox1.Text = "Starting" + _nl;
        string resultString
            = (string) await _db.TestConnectionAsync();
        textBox1.AppendText(resultString + _nl);
        textBox1.AppendText("Done" + _nl);
    }

    private void button2_Click(object sender, EventArgs e)
    {
        textBox1.AppendText("Cancelling..." + _nl);
        _db.CancelTest();
        textBox1.AppendText("Submitted Cancel Token" + _nl);
    }
}

DB.cs

public class DB
{
    private SqlCommand _command = null;
    private CancellationTokenSource _tokenSource
        = new CancellationTokenSource();
    private CancellationToken _token;

    public async Task<string> TestConnectionAsync()
    {
        _token = _tokenSource.Token;
        string query = "SELECT COUNT(*) FROM tblDintData";

        try
        {
            using (SqlConnection connection
                 = new SqlConnection(BuildConnectionString()))
            {
                connection.Open();
                _command = new SqlCommand(query, connection);
                await _command.ExecuteScalarAsync(_token);

                return "Successful Database Connection";
            }
        }
        catch (Exception ex)
        {
            return "Connection Failed:"
                + Environment.NewLine + ex.Message;
        }
    }

    public void CancelTest()
    {
        _tokenSource.Cancel();
    }

    private string BuildConnectionString()
    {
        string ret = "";

            ret = "Server=NotARealServer;"
                + "Database=NoSuchDatabase;"
                + "Trusted_Connection=True;";

        return ret;
    }
}

EDIT ***

Ok, I discovered something just by trial-and-error. If I also make the Connection.Open asynchronous by instead calling Connection.OpenAsync then the UI suddenly becomes responsive. This is not intuitive, but this is the line I changed:

from:

                connection.Open();

to:

                await connection.OpenAsync();

HOWEVER, the ExecuteScalarAsync is still not cancelling when I cancel the CancellationTokenSource. Any ideas???

This might help you move into the right direction https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/asynchronous-programming — Hassan, Jul 24 '17 at 17:39
Thank you Hassan. I've looked at this page, but it doesn't seem to answer my basic question. Is ExecuteScalarAsync truly asynchronous? — dtaylor, Jul 24 '17 at 18:01
Indeed its asynchronous. The link I mentioned above was to help you how to handle calling Async methods so they remain asynchronous. As for the method itself, https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalarasync?view=netframework-4.7 — Hassan, Jul 24 '17 at 18:10
Hassan, yes I can now see that it is indeed asynchronous. To me it was not intuitive that you had to also open the connection asynchronously with an await to make the command work. Now I have the problem with the cancel being ignored. — dtaylor, Jul 24 '17 at 18:15
If I look at the first link, there is a section named 'Cancelling an Asynchronous Operation' which shows another command being executed with the token (_tokenSource in your case). You should try that and see if that works for you. Keep in mind there might be a delay. — Hassan, Jul 24 '17 at 18:23
Hassan, When I put the token in the OpenAsync as well as the ExecuteScalarAsync, it started working perfectly. This makes sense because it was the Open that was timing out, not the ExecuteScalar. Put this in an answer and I will give you credit for it. — dtaylor, Jul 24 '17 at 18:56
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150022/discussion-between-hassan-and-dtaylor). — Hassan, Jul 24 '17 at 19:10

score 5 · Accepted Answer · edited Jul 24 '17 at 19:27

ExecuteScalarAsync is indeed an async method but your UI halts because you are not calling the methods asynchronously. You can see how you can better handle asynchronous method calls on this Microsoft page.

You will also need to open the connection asynchronously, as you also found out. The link contains good examples of open connection, getting data and cancelling the query asynchronously.

EDIT ** from Doug

Yes, Hassan is correct. I was hung up on getting the ExecuteScalarAsync to work when all along the problem was with the Open. As a rule of thumb in the future I will always call commands like this:

                await connection.OpenAsync(_token);
                _command = new SqlCommand(query, connection);
                await _command.ExecuteScalarAsync(_token);

In this way, if there is a connection problem, the asynchronous and cancelling behavior will still work.

Thank you Hassan.

SqlClient.SqlCommand.ExecuteScalarAsync behaving like synchronous call

1 Answers1