Problem with Datareader together with Thread

Question

I have a problem adding the returned data from a Select in the Datagridview. The problem actually occurs when I make use of backGroundWorker next to a ProgressBar.

The specific code that performs the Select and then add Datagridview the data is:

FbCommand command_ObtemCliente_loja99 = new FbCommand("Select ctas.cdcliente, ctas.ljcliente, cli.nome from ctasreceber ctas, clientes cli where ctas.cdcliente = cli.cdcliente and ctas.ljcliente = cli.ljcliente and flgpagamento = 'A' and (flgexcluido <> 'S' or flgexcluido is null) group by ctas.cdcliente, cli.nome, ctas.ljcliente", ConexaoAoBanco_loja99);

ConexaoAoBanco_loja99.Open();

reader = command_ObtemCliente_loja99.ExecuteReader();


if (reader.HasRows)
    while (reader.Read())
    {
        dtGridView_Resultados_Busca_CtasReceber.BeginInvoke(new Action(() =>{
            dtGridView_Resultados_Busca_CtasReceber.Rows.Add(reader[0], reader[1], reader[2]);
        }));
    }
}

reader.Close();
ConexaoAoBanco_loja99.Close();

And the button that calls performs such a function in my code is:

gpbx_PesquisaDados.Visible = false;
gpbx_PesquisaPersonalizado.Visible = false;

bgWorkerIndeterminada.RunWorkerAsync();

//define a progressBar para Marquee
prgsbar_AgrupTotalDados.Style = ProgressBarStyle.Marquee;
prgsbar_AgrupTotalDados.MarqueeAnimationSpeed = 5;

//informa que a tarefa esta sendo executada.
lbl_Status.Text = "PROCESSANDO...";

But what is returned to me, is a Exception when adding query data in Datagridview (dtGridView_Resultados_Busca_CtasReceber)

System.Invalidoperationexception: 'There are no data to read.'

I tested the code without using the backGroundWorker, in the case where the button I use to call this function, automatically already makes the query and insertion of the data in the datagridview. This way, the data is correctly added but then another error occurs and the application closes.

A remark I noticed: If the code part below does not exist:

dtGridView_Resultados_Busca_CtasReceber.BeginInvoke(new Action(() =>{
    dtGridView_Resultados_Busca_CtasReceber.Rows.Add(reader[0], reader[1], reader[2]);
}));

I mean, be like this:

 dtGridView_Resultados_Busca_CtasReceber.BeginInvoke(
      dtGridView_Resultados_Busca_CtasReceber.Rows.Add(reader[0], reader[1], reader[2]);

"Works" in parts as data is added to Datagridview, but another error occurs:

'control' accessed from a thread that is not the one in which it was created.

And then the application is closed.

What can I do? Because in a way "works" the insertion of data in Datagridview, but another error soon occurs. And otherwise, the data does not even reach the Datagridview, as the application is already terminated immediately by Exception

Answer 1

Use a typed collection to represent this data.

Example:

public class ClienteCtaReceberModel
{
    public string CdCliente {get;set;}
    public string NomeCliente {get;set;}
    public string Loja {get;set;}
}

within the process of backgroundworker, you assemble the list:

string sql = "Select ctas.cdcliente, ctas.ljcliente, cli.nome from ctasreceber ctas, clientes cli where ctas.cdcliente = cli.cdcliente and ctas.ljcliente = cli.ljcliente and flgpagamento = 'A' and (flgexcluido <> 'S' or flgexcluido is null) group by ctas.cdcliente, cli.nome, ctas.ljcliente";


List<ClienteCtaReceberModel> lista = new List<ClienteCtaReceberModel>();

using (FbCommand command_ObtemCliente_loja99 = new FbCommand(sql, ConexaoAoBanco_loja99))
{
    ConexaoAoBanco_loja99.Open();
    using (IDataReader reader = command_ObtemCliente_loja99.ExecuteReader())
    {
        while (reader.Read())
        {
             lista.Add(new ClienteCtaReceberModel(){ CdCliente = reader[0].ToString(), Loja = reader[1].ToString(), NomeCliente = reader[2].ToString() });
        }
    }
}


e.Result = lista; //aqui você informa para o BackgroundWorker, que o resultado dele é uma lista de ClienteCtaReceberModel

Finally, in the event Completed of BackgroundWorker, you recover the Result, which is your list, and assigns as DataSource of DataGridView:

private void bgWorkerIndeterminada_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{

    List<ClienteCtaReceberModel> lista = e.Result as List<ClienteCtaReceberModel>;
    dtGridView_Resultados_Busca_CtasReceber.DataSource = lista;

}