CODEWITHSUNDEEP
c#asp.netsql-server
Aufban
Aufban

Reputation: 33

How to stop a query that is taking too long to process

I have a small program that let students test their SQL queries built in ASP.Net and using an SQL Server Database. The problem is that some queries "hang" the console and SQL Server. With that in mind, I want to limit the execution time to 10s or throw an exception.

Right now the code looks something like this:

        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = TextBox1.Text; //Their query
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 1;
        cmd.Connection = sqlConnection1;

        sqlConnection1.Open();
        try
        {
            GridView1.DataSource = cmd.ExecuteReader();
            GridView1.DataBind();
            int count = GridView1.Rows.Count;
            Label1.Text = count.ToString() + " Rows";
        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
            GridView1.DataSource = null;
            GridView1.DataBind();
        }
        finally
        {
            sqlConnection1.Close();
        }

CommandtTimeout is not doing it for me because often times the problem is that the queries are continually streaming rows (Mainly due to a cartesian product operation).

Is there a way to stop the query mid execution without looping through every row?

Upvotes: 0

Views: 951

Answers (2)

kara
kara

Reputation: 130

You can use threads for checking the timeout of the code. 

//Variables
string input;

//Code to run until time exceeds
Thread thread = new Thread(() => {
    input = Console.ReadLine();
});
thread.Start();

//Check if time exceeded
if (!thread.Join(TimeSpan.FromSeconds(10)))
{
    thread.Abort();
    throw new Exception("Time exceeded.");
}

Upvotes: 1

slolife
slolife

Reputation: 19870

Instead of using GridView1.DataSource = cmd.ExecuteReader(); and GridView1.DataBind();, you're going to have to write the code to keep reading the DataReader until you determine that it has taken too long or too many rows have been read already.

Upvotes: 0

Related Questions