2

I'm trying to use the async pattern to execute a SQL command and return a DataTable. Can someone please advice how to solve this problem?

This is my code:

    private static async Task<DataTable> ExecuteAsync(Connections connection, SqlBuilder sql)
    {
        using (SqlConnection conn = new SqlConnection(GetConnectstring(connection)))
        {
            await conn.OpenAsync();
            using (SqlCommand cmd = new SqlCommand(sql.Query, conn))
            {
                foreach (var parameter in sql.ColumnValues.Where(d => !d.Name.StartsWith("#")))
                {
                    cmd.Parameters.AddWithValue(parameter.Name, parameter.Value);
                }

                //Why am I getting a deadlock when executing the next line?
                using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
            }
        }
    }

Best regards, Thomas

svick
  • 236,525
  • 50
  • 385
  • 514
thomas
  • 1,399
  • 1
  • 17
  • 32
  • 1
    Are you sure it is a deadlock not just timing out on the server connection? – bradgonesurfing Aug 23 '13 at 07:49
  • How do you know you have a deadlock? You may be connecting to the wrong server, have a slow connection or returning too much data. Does the method work if you use the synchronous methods? – Panagiotis Kanavos Aug 23 '13 at 08:06
  • You'll need to characterize "deadlock". It is also an exception that SQL Server can generate. Which I suspect is the real problem. – Hans Passant Aug 23 '13 at 10:02
  • 1
    When running this as a synchronous call, I get a response back after a second or two. When running as async, it all stops. When debugging, the line where it stops is when reading into the SqlDataReader – thomas Aug 23 '13 at 12:05

1 Answers1

11

I suspect you are using Wait or Result further up your call stack. This causes a deadlock if called from a UI thread, as I describe on my blog.

Stephen Cleary
  • 437,863
  • 77
  • 675
  • 810
  • Even after being "100% sure" of not using any Result, I found it hidden in my code. Thank you... – thomas Aug 23 '13 at 12:22