Program is not responding/reacting while Dataset.Fill

Question

I have the problem that my program window can no longer be moved or react as long as it is in the Dataset.fill function.

Have anyone an idea why that might be?

using (OracleCommand cmd = new OracleCommand(sqlQuery))
{
    cmd.CommandType = CommandType.Text;
    cmd.Connection = oraCon;

    using (DataSet dSetHelper = new DataSet())
    {
        using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
        {
            dataAdapter.SelectCommand = cmd;
            if (!File.Exists(projPath + "/" + tableNameFromRow + "/" + tableNameFromRow + ".xml"))
            {
                currentTableName.Text = "Export: " + tableNameFromRow;
                this.Refresh();

                dataAdapter.Fill(dSetHelper);
                dSetHelper.WriteXml(projPath + "/" + tableNameFromRow + "/" + tableNameFromRow + ".xml");
            }
        }
    }
}

Answer 1

Have anyone an idea why that might be?

Yes, in simple terms your program hs a thread whose sole job is to draw the UI. It is the thread that runs any button click handler code etc and it is not supposed to be kept busy for a long time. When you run a 30 seconds db query the thread will literally stop drawing the UI while it waits for the query to finish, and all your clicks and drags and key presses just go into this queues of messages waiting for the thread to process them. After a couple of seconds, probably, windows notices that the UI thread has stopped doing its normal work of keeping the queue clear (the window UI messages queue is building up and up and not clearing) and it fades the window and puts "not responding" in the title. When the db query completes, the thread suddenly starts processing UI messages again and the app comes alive

You need to not make your UI thread busy for long periods. Don't get it to do IO work (disks, databases) that takes longer than eg half a second. For longer work, you can turn your method async (post the method signature if you want more help with that) and do:

await Task.Run(_ => { dataAdapter.Fill(dSetHelper); });

If your filling a huge amount of data consider the other ramifications it may have; perhaps stream the data from the db using a reader rather than loading it all into a dataset. If the query is slow running, perhaps look at ways to optimize it; sticking a UI on a slow running query normally puts it into the hands of the user, which can then mean they do stuff like hammer the Run Query button when they get frustrated at it taking long, compounding the problem.. (consider disabling the button while the query runs)

If you want to simplify your code, you don't need to using a dataset and you don't need to provide a command/connection to a data adapter; DAs have a constructor that takes an sql and a connection string and they make the command/connection for you, open it and dispose it. Which means you can boil a DA query down to something like:

using var da = new...(sql,con str);
var dt = new DataTable();
da.Fill(dt);