Read Datatable and do a loop in rows async c#

Question

I'm trying to implement asynchronous methods in my program, and I want to read each row asynchronously from a datatable. I have the following situation:

   private void VerifyPermissions()
    {
        try
        {
            string constring = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false", AcessoBancoDados.server, AcessoBancoDados.user, AcessoBancoDados.password, AcessoBancoDados.database);

            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = constring;
            con.Open();

            var query = "SELECT id FROM users";

            MySqlCommand cmd = new MySqlCommand(query, con);
            MySqlDataAdapter da = new MySqlDataAdapter(query, con);

            DataTable dt = new DataTable();

            da.Fill(dt);
            con.Close();

            foreach (DataRow item in dt.Rows)
            {
               Messagebox.Show(item["id"].ToString());
            }
    }

And the call method:

private void button1_Click(object sender, EventArgs e)
    {
       VerifyPermissions()
    }

Anyone can show me an async situation for this? Thanks.

In the foreach part I have to Add controls to a flowlayout. If the select statement is large, it freezes the ui for a while. I want to bypass that freeze... — Alexandre Hansen, Sep 29 '18 at 06:20
All the controls should be added in the UI thread and adding them to UI asynchronously doesn't make sense. If your problem is freezing UI because of opening connection and loading data, you can [load data into data table asynchronously](https://stackoverflow.com/a/38427392/3110834). — Reza Aghaei, Sep 29 '18 at 15:24

score 1 · Accepted Answer · answered Sep 30 '18 at 07:21

according to your comment you would need something like my code below. If you use SqlCommand instead of SqlDataAdapter you will have async methods already and don't need to create a task.

(also don't mix ui and data access. keep them separate.)

    private async void button1_Click(object sender, EventArgs e)
    {
        await VerificarPermissoes();
    }


    private async Task VerificarPermissoes()
    {
        await Task.Run(() =>
        {
              // put your code from above here.
        }); 
    }

Bruno Warmling · Answer 2 · 2018-09-29T16:27:47.363

Well, you can do this basically by creating tasks in c#, see the example below

private void VerificarPermissoes()
    {
        try
        {
            DataTable dt = new DataTable();
            string constring = String.Format("");
            string query = "SELECT id FROM users";

            using (SqlConnection con = new SqlConnection(constring))
            {
                con.Open();

                DbCommand command = con.CreateCommand();
                command.CommandText = query;
                dt.Load(command.ExecuteReader());
            }

            foreach (DataRow item in dt.Rows)
            {
                Task.Factory.StartNew(delegate () { ProcessItem(item["id"].ToString()); });
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void ProcessItem(string item)
    {
        AddControl(myControl, childControl);
    }

    private void button1_Click(object sender, EventArgs e)
    {
        VerificarPermissoes();
    }



    private void AddControl(Control ctrl, Control child)
        {
            if (ctrl.InvokeRequired)
            {
                Action act = delegate () { AddControl(ctrl, child); };
                this.Invoke(act);
            }
            else
            {
                ctrl.Controls.Add(child);
            }
        }

Hi Bruno, thank you for your reply. In this code I`ve tried to add controls to a flowlayout panel in ProcessItem method but it returns exceptions in Output. I`ve tried this: UserTag ut = new UserTag(); flowLayoutPanel2.Controls.Add(ut); — Alexandre Hansen, Sep 29 '18 at 03:57
To do this, you have to invoke a action. By invoking a method, it means that piece of code will run in the main thread of your program, and then you can perform actions to the UI. — Bruno Warmling, Sep 29 '18 at 16:20

Read Datatable and do a loop in rows async c#

2 Answers2