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 learn.microsoft.com/en-us/dotnet/framework/data/adonet/… — Hassan
– Hassan, Commented Jul 24, 2017 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
– dtaylor, Commented Jul 24, 2017 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, learn.microsoft.com/en-us/dotnet/api/… — Hassan
– Hassan, Commented Jul 24, 2017 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
– dtaylor, Commented Jul 24, 2017 at 18:15
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
– dtaylor, Commented Jul 24, 2017 at 18:56

dtaylor · Accepted Answer · 2017-07-24 19:27:26Z

5

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.

edited Jul 24, 2017 at 19:27

dtaylor

1,0174 gold badges11 silver badges26 bronze badges

answered Jul 24, 2017 at 19:09

Hassan

1,03215 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlClient.SqlCommand.ExecuteScalarAsync behaving like synchronous call

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related