threading Oracle queries in c#

Question

I understand the concept of threading, but I'm new to it in general, so please bear with me. Apologies if this is a noobish question.

I need to run a few intensive queries against an Oracle database in my Windows Forms program. While this code DOES work, the program freezes up since the queries can take up to a minute to run and I'm running several in sequence. I thought that creating a separate thread, as below, would prevent that. Do I need to specify something in the creation of the thread that indicates only a certain amount of resources should be dedicated to it? I found a few answers on Google but didn't really understand them.

Thanks in advance for your help!

    private void loginButton_Click(object sender, EventArgs e)
    {
        loginStatus.Text = "STATUS: Running...";
        Thread thread1 = new Thread(new ThreadStart(GetLoginData));            
        thread1.Start();
        loginStatus.Text = "STATUS: Ready";
    }

    private void GetLoginData()
    {
        try
        {

            Invoke(new Action(() => loginButton.Enabled = false));

            string LDS01_start = "select count(*) from BBLEARN.AUTH_PROVIDER_LOG where AUTH_PROVIDER_PK1 = 103 ";
            string LDAPS_start = "select count(*) from BBLEARN.AUTH_PROVIDER_LOG where AUTH_PROVIDER_PK1 = 106 ";
            string middle = "and log_date >= '" + GetDate(loginStartDate) + @"' 
                         and log_date < '" + GetDate(loginEndDate) + @"' ";

            string LDS01_0 = LDS01_start + middle + "and event_type = 0";
            string LDS01_1 = LDS01_start + middle + "and event_type = 1";
            string LDS01_2 = LDS01_start + middle + "and event_type = 2";
            string LDS01_5 = LDS01_start + middle + "and event_type = 5";
            string LDS01_6 = LDS01_start + middle + "and event_type = 6";

            string LDAPS_0 = LDAPS_start + middle + "and event_type = 0";
            string LDAPS_1 = LDAPS_start + middle + "and event_type = 1";
            string LDAPS_2 = LDAPS_start + middle + "and event_type = 2";
            string LDAPS_5 = LDAPS_start + middle + "and event_type = 5";
            string LDAPS_6 = LDAPS_start + middle + "and event_type = 6";

            Invoke(new Action(() => GetData(LDS01_0, LDS01_LB0)));
            Invoke(new Action(() => GetData(LDS01_1, LDS01_LB1)));
            Invoke(new Action(() => GetData(LDS01_2, LDS01_LB2)));
            Invoke(new Action(() => GetData(LDS01_5, LDS01_LB5)));
            Invoke(new Action(() => GetData(LDS01_6, LDS01_LB6)));

            Invoke(new Action(() => GetData(LDAPS_0, LDAPS_LB0)));
            Invoke(new Action(() => GetData(LDAPS_1, LDAPS_LB1)));
            Invoke(new Action(() => GetData(LDAPS_2, LDAPS_LB2)));
            Invoke(new Action(() => GetData(LDAPS_5, LDAPS_LB5)));
            Invoke(new Action(() => GetData(LDAPS_6, LDAPS_LB6)));

            Invoke(new Action(() => loginButton.Enabled = true));
            loginStatus.Text = "STATUS: Ready";

        }
        catch (Exception e)
        {
            Debug.WriteLine(e);
        }
    }

    private void GetData(string selectCommand, Label label)
    {
        //open the connection
        OracleConnection conn = new OracleConnection(connectString);
        conn.Open();

        //define the command
        selectCommand = selectCommand.Replace(Environment.NewLine, " ");
        OracleDataAdapter dataAdapter = new OracleDataAdapter(selectCommand, conn);
        OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);

        //run the command
        DataTable table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        dataAdapter.Fill(table);

        //pull the result
        label.Text = table.Rows[0][0].ToString();

        //close the connection
        conn.Close();

    }

T McKeown · Accepted Answer · 2014-07-08 14:41:46Z

2

You are invoking the GetData() method, invoking uses the UI thread, the GetData() method is going against the DB and it taking time so your screen locks up. You need to simply invoke the UI changes and then Thread off the DB calls, upon completion invoke the UI thread to enable buttons etc...

Change your calls in GetLoginData() from:

Invoke(new Action(() => GetData(LDS01_0, LDS01_LB0)));

To this:

GetData(LDS01_0, LDS01_LB0);

Change GetData() to this:

private void GetData(string selectCommand, Label label)
{
    //open the connection
    OracleConnection conn = new OracleConnection(connectString);
    conn.Open();

    //define the command
    selectCommand = selectCommand.Replace(Environment.NewLine, " ");
    OracleDataAdapter dataAdapter = new OracleDataAdapter(selectCommand, conn);
    OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);

    //run the command
    DataTable table = new DataTable();
    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
    dataAdapter.Fill(table);
    //close the connection
    conn.Close();

    Invoke(new Action(() => RenderData(label, table.Rows[0][0].ToString())));


}

Create this new RenderData() method:

private void RenderData(Label label, string text)
{
    label.Text = text;
}

edited Jul 8, 2014 at 14:41

answered Jul 8, 2014 at 14:28

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Cliff Over a year ago

Ah, so create the new threads INSIDE the GetData() method?

T McKeown Over a year ago

your method is not so simple, you want to thread the call to the DB but you also want to display the results per call... you want to use async here. Your problem is that you want to display the data table results, so you have fired off the GetData() using the UI thread, it gets the data and is able to render the results..

T McKeown Over a year ago

What you need to do is get the data via a non UI thread and have the non-UI thread Invoke a dedicated method where it passes the datatable, now the UI thread can just be used to render results. You don't need to use async if you handle all of this correctly. I will post an example:

Cliff Over a year ago

Thank you, this does seem to run significantly faster, but it still freezes up...any ideas?

T McKeown Over a year ago

where is it locking up?

Collectives™ on Stack Overflow

threading Oracle queries in c#

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related