Timeout a sql execution C#?

Question

At what point does the application actually execute the query? Is it Is it at connection open adapter.fill?

     if (backgroundWorker1.CancellationPending == false)
        {

        try
        {

            superset = new DataSet();


            string[] lines = BranchTBox.Lines;

            for (int i = 0; i < lines.Length; i++)
            {

                if (lines[i].Length == 3)
                {

                    if (qachk.Checked == false)
                    {
                        connectionString = "Driver={IBM DB2 ODBC DRIVER}; Database=" + lines[i] + "; Hostname=" + lines[i] + "." + lines[i] + ".xx; Port = xx; Protocol = xx; Uid=xx; Pwd= xx;";

                    }
                    else
                    {
                        foreach (Control child in panel4.Controls)
                        {
                            if ((child as RadioButton).Checked)
                            {
                                qaserver = child.Text;
                            }
                        }

                        connectionString = "Driver={IBM DB2 ODBC DRIVER}; Database=" + lines[i] + "; Hostname=" + qaserver + ".xx; Port = xx; Protocol = xx; Uid=xx; Pwd= xx;";

                    }





                    connection = new OdbcConnection(connectionString);

                    adapter = new OdbcDataAdapter(masterquery, connection);


                    connection.Open();
                      if ((backgroundWorker1.CancellationPending == false) && (connection.State == ConnectionState.Open))
                      {
                          if (superset != null)
                          {


                              adapter.Fill(superset);
                              superset.Merge(superset);
                              connection.Close();
                          }

                      }






                    //progressBar1.Value = 0;

                }

               if (backgroundWorker1.CancellationPending == false)
                      {
                          if (superset != null)
                          {

                              dataGridView1.Invoke((Action)(() => dataGridView1.DataSource = superset));
                              dataGridView1.Invoke((Action)(() => dataGridView1.DataSource = superset.Tables[0]));


                              timer1.Stop();
                              progressBar1.Invoke((Action)(() => progressBar1.Value = 0));
                              tabControl1.Invoke((Action)(() => tabControl1.SelectedTab = tabPage3));
                          }
               }



                      //  progressBar1.Invoke((Action)(() => progressBar1.Value = 0));






            }




        }

I'm trying to only run a query for 30 seconds the have it timeout. I tried adding the connection timeout to the connection string but that didn't work. Any other suggestions? Can I try a try catch clause?

Connection timeout is not the same as command timeout, which is usually expressed as a property of a SqlCommand object (adapter.SelectCommand?) If you post more code, we can help identify. — StingyJack
– StingyJack, Commented Oct 8, 2015 at 17:24
I'm using the IBM DB2 ODBC DRIVER does that make a difference? — Sewder
– Sewder, Commented Oct 8, 2015 at 17:29
What do you mean "have it time out but not stop it"? That doesn't make sense to me. And yes, the Fill method is what actually executes the command in this case. — mason
– mason, Commented Oct 8, 2015 at 17:30
@Sewder - only that you would be using OdbcCommand or OleDbCommand or something other than SqlCommand. It still has the same property, and the driver should honor the value provided. — StingyJack
– StingyJack, Commented Oct 8, 2015 at 17:33
@mason so it's looping through multiple databases so I just wanted it to close out the connection and then loop to the next database connection, I just need to figure out how to cancel the command now. — Sewder
– Sewder, Commented Oct 8, 2015 at 17:39

StingyJack · Accepted Answer · 2015-10-08 17:36:37Z

1

As mason points out, the Fill method is what executes the command.

Your data adapter would need this extra statement in the middle to increase the timeout.

adapter = new OdbcDataAdapter(masterquery, connection);
adapter.SelectCommand.Timeout = 60;
connection.Open();

more info here

However, there is no way to use a timeout and allow the query to continue.

answered Oct 8, 2015 at 17:36

StingyJack

19.6k11 gold badges69 silver badges126 bronze badges

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

2 Comments

Sewder Over a year ago

Thahks! Is there a catch I can do, once it times out? Like show a message box if it does go over 60 seconds?

StingyJack Over a year ago

You can wrap in a try catch, sure. Just catch the correct exception type.

Breeze · Accepted Answer · 2015-10-08 17:36:18Z

0

The query is executed on adapter.Fill(). To change the command timeout set the masterquery.CommandTimeout to a value (in seconds) or 0 for no timeout

answered Oct 8, 2015 at 17:36

Breeze

2,0982 gold badges34 silver badges45 bronze badges

Collectives™ on Stack Overflow

Timeout a sql execution C#?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related