Threaded Sql Query in C#

Question

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

@SATSON using a different thread won't stop the query on the remote server — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 18, 2014 at 11:59

Marek · Accepted Answer · 2014-03-18 12:45:38Z

3

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);
}

answered Mar 18, 2014 at 12:45

Marek

3,60517 gold badges76 silver badges125 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:53:10Z

1

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.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Mar 18, 2014 at 12:01

vtortola

36.1k34 gold badges169 silver badges270 bronze badges

2 Comments

Panagiotis Kanavos Over a year ago

Cancelling a server command is not related to the client thread

Ender KARADAG Over a year ago

wat about using SqlCommand.Cancel? Do SqlCommand.Cancel stop the query on remote side? i think that can be done someway. User can stop the query in Management studio and Toad For SqlServer

Luaan · Accepted Answer · 2014-03-18 12:22:50Z

0

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.

answered Mar 18, 2014 at 12:22

Luaan

64.1k8 gold badges108 silver badges126 bronze badges

Comments

samar · Accepted Answer · 2014-03-18 12:44:25Z

0

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.

edited Mar 18, 2014 at 12:44

answered Mar 18, 2014 at 12:33

samar

5,2319 gold badges50 silver badges72 bronze badges

Comments

morethanyell · Accepted Answer · 2017-09-05 15:07:17Z

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();

Collectives™ on Stack Overflow

Threaded Sql Query in C#

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related