How to use BeginExecuteReader

Question

Good day.

Please, help me about how to use three methods BeginExecuteReader() of SqlCommand class, using Generic Lists. I made a method using BeginExecuteReader, but i don't know if this is the best way of usage

public class Empresa {
    public Empresa() {
        PkEmpresa = -1;
        CodigoEmpresa = "";
        Descripcion = "";
        PkCategoriaEmpresa = -1;
    }

    public int PkEmpresa { get; set; }
    public string CodigoEmpresa { get; set; }
    public string Descripcion { get; set; }
    public int PkCategoriaEmpresa { get; set; }

    public Empresa ShallowCopy() {
        return (Empresa)this.MemberwiseClone();
    }
}



public class AsyncronousDAL {
    private static string getConexion() {
        return "Data Source=DATABASE;Initial Catalog=DATA_BASE;Integrated Security=True;Asynchronous Processing=True";
    }

    public static List<Empresa> ConsultaAsincrona() {
        List<Empresa> _resultados = new List<Empresa>();

        using (SqlConnection conexion = new SqlConnection(getConexion())) {
            using (SqlCommand commando = new SqlCommand("[dbo].[pruebaAsync]", conexion)) {
                commando.CommandType = System.Data.CommandType.StoredProcedure;
                conexion.Open();
                IAsyncResult resultado = commando.BeginExecuteReader();
                using (SqlDataReader reader = commando.EndExecuteReader(resultado)) {
                    while (reader.Read()) {
                        _resultados.Add(new Empresa() {
                            PkEmpresa = Convert.ToInt32(reader["PkEmpresa"]),
                            CodigoEmpresa = reader["CodigoEmpresa"].ToString(),
                            Descripcion = reader["Descripcion"].ToString(),
                            PkCategoriaEmpresa = Convert.ToInt32(reader["PkCategoriaEmpresa"])
                        });
                    }
                }
            }
        }

        return _resultados;
    }
}

Answer 1

If you aren't familiar with the Asynch pattern there are lots of tutorials and examples on the web. This is old, but still relevant: http://msdn.microsoft.com/en-us/library/aa719595(v=vs.71).aspx

When you call BeginExecuteReader that work is going to end up being pushed out to a worker thread, allowing your main to continue executing. When you call EndExecuteReader that will cause your main thread to block until that task is complete.

If you immediately called EndExecuteReader - you aren't really getting any benefit (in fact, you're introducing additional overhead).

Take a look at the example here: http://msdn.microsoft.com/en-us/library/7szdt0kc.aspx

The BeginExecuteReader method returns immediately, but until the code executes the corresponding EndExecuteReader method call, it must not execute any other calls that start a synchronous or asynchronous execution against the same SqlCommand object. Calling the EndExecuteReader before the command's execution is completed cause the SqlCommand object to block until the execution is finished.

This is the relevant section of code:

            // Although it is not required that you pass the 
            // SqlCommand object as the second parameter in the 
            // BeginExecuteReader call, doing so makes it easier
            // to call EndExecuteReader in the callback procedure.
            AsyncCallback callback = new AsyncCallback(HandleCallback);
            command.BeginExecuteReader(callback, command);