QueryTimeout Expired is the Error in the asp.net. Users usually get an exception in their application

“The timeout period elapsed prior to completion of the operation or the server is not responding”

When we work with the database which contain multiple tables and that tables contain thousands no. of rows. So whenever user wants to access that table,then it takes more time in loading and we get an error of QueryTimeout before completing the execution which gives the impression that server is down or something similar but it is the problem of CommandTimeout property which takes 30 seconds to execute a query.

To resolve this error we set the value of CommandTimeout property, By default its value is 30 seconds. You can assign any number of seconds to this property. CommandTimeout waits for the query to execute before timing out.

It will Wait for 2 minute for query execution before timing out

commd.CommandTimeout = 120;

It will Wait for 2 minute for query execution before timing out

commd.CommandTimeout = 180;

 It will Wait indefinitely for query execution

commd.CommandTimeout = 0; 

To resolve this problem in our program, let's understand it through an example.

Include the following code in your console application:

using System;
using System.Data.SqlClient;

namespace ConsoleApplicationExm
{
    class QueryTimeOut
    {
        
        public static void Main()
        {
            string ConnString = "Data Source=SHIKHABANSAL-PC; Initial Catalog=StudentDetails; Integrated Security=True";
           
            string queryTimeoutString = "waitfor delay '00:02:00'";
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(queryTimeoutString, conn);
                
                cmd.CommandTimeout = 80;
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("Got expected SqlException due to command timeout ");
                    Console.WriteLine(ex);
                }
            }
        }
    }
}

In the above example, we are using a SQL Server method “waitfor delay”.This method will pause the execution in SQL Server for 2 minutes, and set commandTimeout property to 80 seconds, so when you'll execute the program,it will show an exception: “Timeout Expired”. Refer the below screenshot.

There might be Long running task or uncommitted transactions, For example:There are thousand number of rows needs to be updated which will take long time in execution and we will get Timeout exception as by default it takes 30 seconds so to avoid queryTimeout we can adjust commandTimeout property to any number of seconds or zero second accordingly so that query can be executed successfully.