SqlClient.SqlCommand.ExecuteScalarAsync behaves like a synchronous call
I uninstalled the app to the minimum POC and I still get the same effect. It looks like ExecuteScalarAsync behaves like a synchronous call. I figured that when a wait is encountered, the rest of the code in the asynchronous method pauses and the message pump comes back and receives another message from the message queue, allowing the UI to continue running. When the call to Scalar completes, the remainder of the asynchronous method is then pushed back to the message queue to complete.
When this little application starts up, the TestConnectionAsync method hangs in the UI and no other messages are executed until the ExecuteScalarAsync call has timed out.
Am I doing something wrong, or is this async method behaving like a synchronous method?
The form has two buttons. The first one starts the async method and the second tries to override 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 Connection.Open asynchronous instead of calling Connection.OpenAsync, then the UI suddenly becomes responsive. It's not intuitive, but this is the line I changed:
from
connection.Open();
in
await connection.OpenAsync();
HOWEVER, ExecuteScalarAsync still doesn't cancel when I cancel CancellationTokenSource. Any ideas ???
source to share
ExecuteScalarAsync is indeed an asynchronous method, but your UI stops because you are not calling methods asynchronously. You can see how best to handle asynchronous method calls on this Microsoft page .
You will also need to open the connection asynchronously, as you also learned. The link contains good examples of open connection, getting data and canceling request asynchronously.
EDIT ** from Doug
Yes, Hasan is right. I was hung up to get ExecuteScalarAsync to work when the problem was with Open. As a rule, in the future I will always refer to commands like this:
await connection.OpenAsync(_token);
_command = new SqlCommand(query, connection);
await _command.ExecuteScalarAsync(_token);
This way, if there is a connection issue, the async and cancellation behavior will still work.
Thanks Hassan.
source to share