my output parameters are always null when i use BeginExecuteNonQuery

Question

I have a stored procedure that returns a varchar(160) as an output parameter of a stored procedure.

Everything works fine when i use ExecuteNonQuery, i always get back the expected value.

However, once i switch to use BeginExecuteNonQuery, i get a null value for the output.

I am using connString + "Asynchronous Processing=true;" in both cases.

Sadly the BeginExecuteNonQuery is about 1.5 times faster in my case...but i really need the output parameter.

Thanks!

EDIT:This is how i am processing the BeginExecuteNonQuery callback (i am using .net 4.0...)

    Dim resp as String=""
    cmd.BeginExecuteNonQuery(Sub(result As IAsyncResult)
                                 Dim c As SqlCommand = Nothing
                                 Try
                                     c = CType(result.AsyncState, SqlCommand)
                                     c.EndExecuteNonQuery(result)
                                     **resp = CStr(c.Parameters("@response").Value)**
                                 Catch ex As Exception
                                     WriteLog("ERR - LogRequest - " & ex.Message)
                                 Finally
                                     c.Connection.Close()
                                     c.Dispose()
                                 End Try
                             End Sub, cmd)

Answer 1

If you use BeginExecuteNonQuery your code does not wait for the query to execute before continuing, that is why you don't have the output parameter. In order to retrieve the out parameter you need to specify an AsyncCallback delegate that runs when the query has finished executing. Also are you sure that BeginExecuteNonQuery is really faster and that the perceived performance increase is not just because the process is just not waiting for the query to execute. The point of Async queries is where you want to fire off a long bit of processing such as to produce a complex report, then do something later once it is complete, e.g. email the user to tell them their report has been processed.

Answer 2

When you use BeginExecuteNonQuery the query runs in the background, and your code continues to run. What is probably happening is that you are looking at the result before the query has finished executing. This is from the msdn help for BeginExecuteNonQuery:

IAsyncResult result = command.BeginExecuteNonQuery();
while (!result.IsCompleted)
{
    Console.WriteLine("Waiting ({0})", count++);
    // Wait for 1/10 second, so the counter
    // does not consume all available resources 
    // on the main thread.
    System.Threading.Thread.Sleep(100);
}
Console.WriteLine("Command complete. Affected {0} rows.", 
     command.EndExecuteNonQuery(result));

So the result is only properly available once IsCompleted is true.

Note that this is just an example from the docs, the real use of the async functions is to allow the rest of your app (eg. your UI) to continue to function whilst a long query is being run.