C# Get datatable return from a thread with references

Question

I have a method that call another method from a class with references which in turn returns a datatable.

I'm trying to run that query in a thread, now to run the query itself in a thread is easy, but how do I get it to return the datatable from the thread?

Example of Method calling the class:

private void loadCombo(string sqlComand, string value, ComboBox loadBox)
        {           
            DataTable dt = new DataTable();

            //Thread thread = new Thread(() => sqlScript.loadCombo(sqlComand, value, loadBox));
            //thread.start();

            dt = sqlScript.loadCombo(sqlComand, value, loadBox);
            loadBox.ValueMember = value;
            loadBox.DataSource = dt;
            loadBox.Refresh();
        }

Windows Forms Client Application. Calling the class normally as in above example does work. I'm just thinking that if I can run these in threads and implement multi threading it might make my application faster. As I have a couple of these type of queries running. — shinga77, Feb 14 '17 at 10:07
You should get an error if you try to set `loadBox` from another thread, try to search for error itself [next time](http://stackoverflow.com/q/2562946/1997232). — Sinatr, Feb 14 '17 at 10:11
You can call the database access on another thread and perhaps have the datatable passed as an argument to the thread complete method.. but you are not allowed to access the winforms control in a thread different to that which created it. — Wheels73, Feb 14 '17 at 10:14

score 0 · Answer 1 · answered Feb 14 '17 at 10:23

0

This sounds like a simple question.

You can use Task (as well as async/await, but that might be optional, usually it's used to disable/enable corresponding button to prevent multiple executions) to execute long running code in event handler without blocking UI. You still need to invoke the acess to UI elements:

async void loadCombo(string sqlComand, string value, ComboBox loadBox)
{
    // disable button

    await Task.Run(() =>
    {
        var dt = sqlScript.loadCombo(sqlComand, value, loadBox);
        Invoke((Action)(() =>
        {
            loadBox.ValueMember = value; // is it out parameter? anyway, copying your code
            loadBox.DataSource = dt;
            loadBox.Refresh();
        }));
    }

    // enable button
}

There shouldn't be any problem to create and pass DataTable from another thread.

answered Feb 14 '17 at 10:23

Sinatr

20,892
15
90
319

`Task.Run` will use another thread from ThreadPool - which will be waste of threads. Instead use `Sql...Async` methods for loading data in real async manner – Fabio Feb 14 '17 at 10:28
@Fabio, that's correct. I was answering regarding await/invoke details for a long running synchronous method (no clues what `sqlScript` is). – Sinatr Feb 14 '17 at 10:30
sqlScript is a referance to a class called sqlScript. loadCombo, is a method within that class. – shinga77 Feb 14 '17 at 10:46

score 0 · Accepted Answer · edited Feb 15 '17 at 21:38

Instead of executing database query on another thread use async-await approach, which will be executed on one thread and will not block UI.

For this you need to separate "data querying" operation and ComboBox setting

public class SqlScript
{
    public async Task<DataTable> LoadDataAsync(string command, object value)
    {
        // Load data asynchronously by using ..Async methods
    }
}

private async Task loadComboAsync(string sqlComand, string value, ComboBox loadBox)
{           
    var data = await sqlScript.LoadDataAsync(sqlComand, value);

    loadBox.ValueMember = columnNameWhichRepresentValue;
    loadBox.DataSource = data;
}

And you don't need to call loadBox.Refresh method, setting new datasource will do it anyway.

Do not use Task.Run. Database operations do nothing "heavy" which need to be executed on another thread. Database operations only send a command and wait for response - so using another thread only for waiting is waste of resources.
Instead use SqlConnection.OpenAsync and SqlCommand.ExecuteReaderAsync methods

public async Task<DataTable> loadCombo(string sqlCommand, string value)
{
    var yourConnectionString = "DataSource=...";
    using (var connection = new SqlConnection(yourConnectionString))
    using (var command = new SqlCommand(sqlCommand, connection))
    {
        await connection.OpenAsync();
        var reader = await command.ExecuteReaderAsync();

        var data = new DataTable();
        data.Load(reader);
        return data;
    }
}

score 0 · Answer 3 · answered Feb 14 '17 at 11:06

Thank you @Sinatr and @Fabio, I tested a combination of your answers and the below seems to give me the fastest return time.

Main Form Method Call:

private async Task loadCombo(string sqlCommand, string value, ComboBox loadBox)
        {   
            var data = await sqlScript.loadCombo(sqlCommand, value);
            loadBox.ValueMember = value;
            loadBox.DataSource = data;
        }

sqlScript Class Method:

public async Task<DataTable> loadCombo(string sqlCommand, string value)
        {
            SqlConnection myConn;
            SqlCommand myCmd = default(SqlCommand);
            SqlDataReader oResult;
            DataTable dt = new DataTable();

            await Task.Run(() =>
            {

                using (myConn = new SqlConnection("Data Source=" + frmMain.sqlServer + ";" + "Initial Catalog=" + frmMain.sqlData + ";User Id=" + frmMain.sqlUser + ";Password=" + frmMain.sqlPwd + ";"))
                {
                    try
                    {
                        myConn.Open();

                        if (myConn.State == ConnectionState.Open)
                        {
                            myCmd = new SqlCommand((sqlCommand), myConn);
                            oResult = myCmd.ExecuteReader();
                            dt.Load(oResult);
                            myConn.Close();
                        }
                        else
                        {

                        }
                    }
                    catch (Exception err)
                    {
                        using (StreamWriter w = File.AppendText("ErrorLog.log"))
                        {
                            frmMain.Log("sqlScripts.loadCombo: " + err.Message, w);
                        }
                    }//End Try
                }//End using
            });
            return dt;
        }

shinga77 · Answer 4 · 2017-02-14T10:58:50.410

Thank you @Sinatr, I just had to twek your answer a bit, the below code seems to work.

private async void loadCombo(string sqlCommand, string value, ComboBox loadBox)
        {
            await Task.Run(() =>
            {
                var dt = sqlScript.loadCombo(sqlCommand, value, loadBox);
                Invoke((Action)(() =>
                {
                    loadBox.ValueMember = value;
                    loadBox.DataSource = dt;
                }));
            });
        }

C# Get datatable return from a thread with references

4 Answers4