Performing an SQL query while waiting on an asyncronous operation generates an exception

Question

The code I'm writing copies data from one table to another. It is possible the query may run for a long time so I'm performing an asynchronous query and while waiting I'm doing a count on the destination table to provide a status update.

The query that does the count is getting the following exception message...

The command execution cannot proceed due to a pending asynchronous operation already in progress.

So clearly what I'm doing is not allowed. So what are the rules for asynchronous SQL operations? Is there another way to get the status of the pending operation?

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder( _connectionString );
builder.AsynchronousProcessing = true;

using( SqlConnection connection = new SqlConnection( builder.ConnectionString ) )
{
    connection.Open();

    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandType = System.Data.CommandType.Text;
    command.CommandText = "INSERT INTO DestTable SELECT * FROM SourceTable";

    IAsyncResult result = command.BeginExecuteNonQuery();

    using( SqlConnection statusConnection = new SqlConnection( _connectionString ) )
    {
        while( !result.IsCompleted )
        {
            SqlCommand statusCommand = new SqlCommand();
            statusCommand.Connection = statusConnection;
            statusCommand.CommandType = System.Data.CommandType.Text;
            statusCommand.CommandText = "SELECT COUNT(*) FROM DestTable";

            int currentRowCount = (int)command.ExecuteScalar();

            Thread.Sleep( 500 );
        }

        command.EndExecuteNonQuery( result );
    }
}

Answer 1

It looks like you are attempting to use the same command.

I think that you want to replace

int currentRowCount = (int)command.ExecuteScalar();

with

int currentRowCount = (int)statusCommand.ExecuteScalar();

You are attempting to use the same Statement, which should be blocked due to the first execution.