CODEWITHSUNDEEP
c#sqlsql-servermultithreading
Ender KARADAG
Ender KARADAG

Reputation: 135

Threaded Sql Query in C#

ive searched for the archive but cant find a suitable entry. sorry if exists.

im using vs2008, .NET 3.5, MS-SQL2008

My Code is simple;

SqlConnection CONN=new SqlConnection(SomeConnectionString);
CONN.Open();

SqlCommand cmd = CONN.CreateCommand();
cmd.CommandText="SELECT FIELD1,FIELD2,FIELD3 from table1";

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();
da.Fill(dt);

so DataTable dt is ready to use.

But i want to make the query stoppable by user, coz it may last too much long to wait. so user will stop the query, change parameters and requery.

Also i want to show a stopwatch for query time, during query execution.

As expected, i cant do those in the same thread of sql query.

What should be the best and simplest threading approach for above code? can someone reply a piece of code for that? note that; DataTable dt must be available for the main thread at the end.

thanks in advance

Upvotes: 2

Views: 6408

Answers (5)

morethanyell
morethanyell

Reputation: 326

Try something like this...

    Thread t = new Thread(() => {

        using (SqlConnection con = new SqlConnection(_someConnectionString)) {

            string query = "SELECT FIELD1, FIELD2, FIELD3 FROM table1";

                using (SqlCommand com = new SqlCommand(query, con)) {

                    try {

                       con.Open();

                       using (SqlDataReader reader = com.ExecuteReader()) {
                           if (reader.HasRows) {
                               DataTable dt = new DataTable();
                               dt.Load(reader);
                           }

                       }

                    } catch (Exception ex) {
                        // Anything you want to do with ex
                    } finally {
                        con.Close();
                    }

                }

        }

    });

    t.IsBackground = true;

    t.Start();

Upvotes: 0

Marek
Marek

Reputation: 3575

Well if you want to do a separate thread and execute the command there you can try this code:

System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ThreadStart(LoadDB));
thread.Start();
//put this command wherever you want

private void LoadDB()
{
SqlConnection CONN=new SqlConnection(SomeConnectionString);
CONN.Open();

SqlCommand cmd = CONN.CreateCommand();
cmd.CommandText="SELECT FIELD1,FIELD2,FIELD3 from table1";

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();
da.Fill(dt);
}

Upvotes: 3

samar
samar

Reputation: 5201

SqlCommand.Cancel might be of use here.

Create a class as given in the link here. This link gives a good example of how to start a thread and terminate it gracefully.

The class Worker in this link has 2 methods which you should take note of. DoWork within which you can call your sql query and the other is RequestStop within which you can call your SqlCommand.Cancel which should stop your execution of the query on server side. RequestStop method can be called by a user event like button click which in turn will stop execution of query.

Hope this helps.

Upvotes: 0

Luaan
Luaan

Reputation: 63732

You can use the SqlCommand.BeginExecuteReader to start an asynchronous command. This allows you to handle UI (your "stopwatch"), and continue work in the asynchronous callback.

Going back to a UI thread is simple, if the callback doesn't end up back there automatically, just use Invoke on some control and that code will be processed on the UI thread at the earliest convenience.

You can call SqlCommand.Cancel to try and cancel the query, which should work, mostly. If it doesn't, your only other choice is to create another SqlConnection and another SqlCommand, and kill the other connection through SQL.

Upvotes: 0

vtortola
vtortola

Reputation: 35895

I am afraid there is not asynchronous or cancellable API for IDbDataAdapter or DataTable.Load, so your only way is to do the job in a ThreadPool thread like in https://stackoverflow.com/a/2108944/307976 and forget about it if you want to cancel it.

Upvotes: 1

Related Questions