BeginExecuteNonQuery without EndExecuteNonQuery

Question

I have the following code:

using (SqlConnection sqlConnection = new SqlConnection("blahblah;Asynchronous Processing=true;")
{
    using (SqlCommand command = new SqlCommand("someProcedureName", sqlConnection))
    {
        sqlConnection.Open();

        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@param1", param1);

        command.BeginExecuteNonQuery();
    }
}

I never call EndExecuteNonQuery.

Two questions, first will this block because of the using statements or any other reason? Second, will it break anything? Like leaks or connection problems? I just want to tell sql server to run a stored procedure, but I don't want to wait for it and I don't even care if it works. Is that possible? Thanks for reading.

Answer 1

This won't work because you're closing the connection while the query is still running. The best way to do this would be to use the threadpool, like this:

ThreadPool.QueueUseWorkItem(delegate {
    using (SqlConnection sqlConnection = new SqlConnection("blahblah;Asynchronous Processing=true;") {
        using (SqlCommand command = new SqlCommand("someProcedureName", sqlConnection)) {
            sqlConnection.Open();

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@param1", param1);

            command.ExecuteNonQuery();
        }
    }
});

In general, when you call Begin_Whatever_, you usually must call End_Whatever_ or you'll leak memory. The big exception to this rule is Control.BeginInvoke.

Answer 2

You should always call the EndExecuteNonQuery() method to prevent leaks. It may work now but who knows what will happen in future versions of .NET. The general rule is always follow a BeginExecute... with an EndExecute...

Answer 3

1. You can't close the connection after you submit the BeginExceuteNotQuery. It will abort the execution. Remove the using block.

2. In order to close the connection, you must know when the call has completed. For that you must call EndExecuteNonQuery, usually from a callback:

.

command.BeginExecuteNonQuery(delegate (IAsyncResult ar) {
   try { command.EndExecuteNonQuery(ar); }
   catch(Exception e) { /* log exception e */ }
   finally { sqlConnection.Dispose(); }
   }, null);

If you want to submit a query and don't care about the results, see Asynchronous T-SQL execution for a reliable pattern that ensures execution even if client diconnects or crashes.