How to delay my program while reading from a database C#

Question

I have a mess with trying to read line by line from a MS Access database and display it on the screen when between every display there are a few seconds of sleeping.

I am using System.Threading as you can see but it seems the sleeping is happening before the program displays the records and when the sleeping is over only the last record is displayed without displaying the previous.

Here is my code, I will really appreciate any help!

private void com_start_Click(object sender, EventArgs e)
{
    try
    {
        string ConString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\MyBrainWash\Englishdb.accdb;";

        OleDbConnection Con = new OleDbConnection(ConString);
        Con.Open();

        check_connection.Text = "succeeded";

        OleDbCommand command = new OleDbCommand();
        command.Connection = Con;
        command.CommandText = "Select * From words";

        OleDbDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                lab_word.Text = reader["word"].ToString();
                lab_definition.Text = reader["definition"].ToString();
                Thread.Sleep(30000);
            }
        }

        reader.Close();
        Con.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error " + ex);
    }
}

When you sleep you sleep in the UI thread. Nothing is updated on the screen then. You need a timer or other better way of doing things. `Thread.Sleep()` is almost always the wrong thing to do. — Sami Kuhmonen, Dec 10 '16 at 18:53
May I suggest simply reading all from the database at once and simply delaying the display of your information? — Fang, Dec 10 '16 at 18:54
related: http://stackoverflow.com/questions/20849979/how-to-make-oledb-code-run-asynchronous — Josh Part, Dec 10 '16 at 19:00
I thought it would be good because I want it to pouse and not move to the next record and now i am understanding this is because of the command @SamiKuhmonen — user6497813, Dec 13 '16 at 16:48

Fabio · Accepted Answer · 2016-12-10T19:17:57.640

UI controls lab_word and lab_definition.Text will be updated after whole com_start_Click method completes.
That is why you see that only last row have been shown.

You need "release" UI thread after reading every row for 3 seconds for updating UI controls with new values.

I think async/await approach suits very good this purpose.
Mark button click with async keyword.

private async void com_start_Click(object sender, EventArgs e)
{
    string ConString = 
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\MyBrainWash\Englishdb.accdb;";
    using (var Con = new OleDbConnection(ConString))
    {
        Con.Open();
        check_connection.Text = "successed";
        using (var command = new OleDbCommand())
        {
            command.Connection = Con;
            command.CommandText = "Select * From words";
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    lab_word.Text = reader["word"].ToString();
                    lab_definition.Text = reader["definition"].ToString();
                    await Task.Delay(30000);
                }
            }
        }
    }
}

You can also use asynchronous methods of OleDbConnection, OleDbCommand and OleDbReader

private async void com_start_Click(object sender, EventArgs e)
{
    string ConString = 
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\MyBrainWash\Englishdb.accdb;";
    using (var Con = new OleDbConnection(ConString))
    {
        await Con.OpenAsync();
        check_connection.Text = "successed";
        using (var command = new OleDbCommand())
        {
            command.Connection = Con;
            command.CommandText = "Select * From words";
            using (var reader = await command.ExecuteReader())
            {
                while (await reader.ReadAsync())
                {
                    lab_word.Text = reader["word"].ToString();
                    lab_definition.Text = reader["definition"].ToString();
                    await Task.Delay(30000);
                }
            }
        }
    }
}

thanks for explaining com_start_Click method.I tried your first solution but I dont want to click every time I want to move to the next record.I want it automatically.@fabio — user6497813, Dec 13 '16 at 16:40
Hi again,I read about async and await but I'm a little confused about the order of commands in my code.await is waiting until first iteration is over and then returning to the async method but it hasn't finished yet because of another iteration so how can I see it on the UI?according to you the whole com_start_Click has to be finished.@fabio — user6497813, Dec 15 '16 at 12:39
At `await` execution will be returned to the caller of `com_start_Click` method. While UI thread released it will update UI controls, then when task completes execution will continue after `await` -> goes to next iteration of reader — Fabio, Dec 15 '16 at 12:49

How to delay my program while reading from a database C#

1 Answers1