Connection Timeout exception for a query using ADO.Net

Question

Update: Looks like the query does not throw any timeout. The connection is timing out.

This is a sample code for executing a query. Sometimes, while executing time consuming queries, it throws a timeout exception.

I cannot use any of these techniques: 1) Increase timeout. 2) Run it asynchronously with a callback. This needs to run in a synchronous manner.

please suggest any other techinques to keep the connection alive while executing a time consuming query?

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

Answer 1

Since you are using ExecuteNonQuery which does not return any rows, you can try this polling based approach. It executes the query in an asyc manner (without callback) but the application will wait (inside a while loop) until the query is complete. From MSDN. This should solve the timeout problem. Please try it out.

But, I agree with others that you should think more about optimizing the query to perform under 30 seconds.

        IAsyncResult result = command.BeginExecuteNonQuery();

        int count = 0;
        while (!result.IsCompleted)
        {
            Console.WriteLine("Waiting ({0})", count++);
            System.Threading.Thread.Sleep(1000);
        }
        Console.WriteLine("Command complete. Affected {0} rows.",
        command.EndExecuteNonQuery(result));