UI still freezing when using async/await

Question

I'm no expert in terms of asychronous operations and hoping that someone can help me point out the problem.

In one of my methods I have written a wrapper,

public static async Task<int> ExecuteNonQueryRawSQLAsync(string sqlStatement)
{
    int _returnValue = -1;
    using (SqlConnection _conn = new SqlConnection("connectionString"))
    {
        using (SqlCommand _comm = new SqlCommand())
        {
            _comm.Connection = _conn;
            _comm.CommandText = sqlStatement;
            _comm.CommandType = CommandType.Text;

            // other codes on setting parameter

            try
            {
                await _conn.OpenAsync();
                _returnValue = Convert.ToInt32(await _comm.ExecuteNonQueryAsync());
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
    return _returnValue;
}

in my UI, I call the method like this,

int _recordsAffected = await MyClass.ExecuteNonQueryRawSQLAsync("INSERT....");

To test if it's really working, i tried to supply an invalid database server address in the connection string so it keeps on searching until it throws an exception.

While the program is still connecting, the UI freezes. What are the things that are missing in my wrapper? or are there any other needed initializations that I need to do?

The method that you call the `ExecuteNonQueryRawSQLAsync` in - is that `async`? — Chris Mantle, Oct 08 '13 at 08:20
@ChrisMantle it is the name of my wrapper. Inside the wrapper, is an async method `ExecuteNonQueryAsync()`. — Rossana, Oct 08 '13 at 08:21

score 5 · Accepted Answer · answered Oct 08 '13 at 08:33

5

From bitter memory, TCP sockets have a very similar issue - basically, the name resolution is performed synchronously, even for the async operations. There are two ways around this:

use an IP address instead of a name
make sure you start the Open* / Connect* / whatever from a worker thread - perhaps Task.StartNew

Untested, but presumably:

await Task.StartNew(_conn.Open);

answered Oct 08 '13 at 08:33

Marc Gravell

1,026,079
266
2,566
2,900

Is there any documentation for this marc? or you found it by experience? – Sriram Sakthivel Oct 08 '13 at 08:36
1

@SriramSakthivel I haven't looked; basically, I found this the hard way by hitting a very similar issue - IIRC I then peeked in reflector – Marc Gravell Oct 08 '13 at 08:36
@SriramSakthivel: The problem is not actually with sockets (which have a fully asynchronous Win32 API), but with Microsoft's wrappers. The problem for HTTP connections is documented [here](http://msdn.microsoft.com/en-us/library/system.net.httpwebrequest.begingetresponse.aspx) and [here](http://msdn.microsoft.com/en-us/library/system.net.httpwebrequest.begingetrequeststream.aspx), and I believe that Marc is correct that this same problem exists for SQL connections as well. – Stephen Cleary Oct 08 '13 at 12:10
@StephenCleary Am aware of the `HTTP` does some synchronous work to initiate an async operation. but can't find any resource for Sql and sockets as well – Sriram Sakthivel Oct 08 '13 at 12:23
+1, though I encourage the use of `TaskRun` over `Task.Factory.StartNew` (explained [on my blog](http://blog.stephencleary.com/2013/08/startnew-is-dangerous.html)). – Stephen Cleary Oct 08 '13 at 12:30
@StephenCleary [is this what you mean about `TaskRun`](http://stackoverflow.com/a/14963064/491243)? sorry but i didn't understand all of this. Both of you and Marc says that I need to rewrite my `ExecuteNonQueryRawSQLAsync` wrapper? – Rossana Oct 08 '13 at 20:25
2

@Rossana: My previous comment has a link to my blog where I explain why I don't recommend `StartNew`. I recommend you change your wrapper the way Marc suggested but using `Run` instead of `Factory.StartNew`. – Stephen Cleary Oct 08 '13 at 20:32
@StephenCleary if you don't mind, can you please help me rewrite the code? this is my first time writing C# codes :( – Rossana Oct 08 '13 at 20:40
@StephenCleary is this what you mean? `await Task.Run(() => _conn.Open());` – Rossana Oct 08 '13 at 20:43
1

@Rossana: Yes, that is what I mean. – Stephen Cleary Oct 08 '13 at 20:44
@StephenCleary let me check where i messed up because basically when I rewrite the code, the UI still freezes when I stop my Sql Server instance. – Rossana Oct 08 '13 at 20:47
@StephenCleary OMG it works, in some part of the code ia have this line, `_varList = await _repo.GetAsync(_schoolYear, _schoolterm, _courseCode);` but when i changed it to `_varList = await Task.Run(() => _repo.GetAsync(_schoolYear, _schoolterm, _courseCode));` but I have still question, why is `_varList = await _repo.GetAsync(..,..,..);` not enough? inside `GetAsync` called my `ExecuteNonQueryRawSQLAsync()` wrapper. is msdn's `.ExecuteNonQueryAsync()` not working? – Rossana Oct 08 '13 at 20:51
2

@Rossana: It's the same problem that Marc described in his answer. To summarize, a lot of .NET network calls are not *actually* asynchronous. – Stephen Cleary Oct 08 '13 at 20:57
@StephenCleary that perhaps is unfair; perhaps more reasonably: they are not *quite* as asynchronous *as they could be* – Marc Gravell Oct 09 '13 at 06:48

score 0 · Answer 2 · answered Oct 08 '13 at 08:53

0

await "somemethod" doesn't mean that the method is called asynchronosly. You are still doing a regular method call that will run like any other until the method being called decides to return a task representing an asynchronous operation, which you then await.

If the method does a bunch of time consuming stuff before returning the awaitable task, you can call it using TaskFactory.Startnew().

answered Oct 08 '13 at 08:53

Tormod

4,551
2
28
50

so how is my answer different with this one? http://stackoverflow.com/a/19029317/2816021 – Rossana Oct 08 '13 at 09:20
@Rossana Notice that the post says "Correctly written methods". If the method blocks while CREATING the task, it is not "correctly written". Asynch methods should return right away. If the creation of a task is time consuming (it needs resolve DNS naming or something), the creation itself should be wrapped in a new task so that the client considers the whole thing to be just one awaitable task. – Tormod Oct 08 '13 at 09:46

UI still freezing when using async/await

2 Answers2