Query Timing out when Command Timeout = 0

Question

I have a frustrating issue with a query that typically takes between 1.5-2 minutes to run (due to a lack of ability to modify this database, we cannot improve it more than this time). The query times out, despite the Command Timeout property being set to 0 (this is C# code).

Here is the code that executes the query:

public DataTable GetData()
{
    DataTable results = new DataTable();

    try
    {
        using (var sqlConnection = new SqlConnection(ConfigurationManager.AppSettings["SqlConnectionString"].ToString()))
        {
            String command = _query;

            sqlConnection.Open();
            var sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = command;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandTimeout = 0;
            SqlDataAdapter daM = new SqlDataAdapter(sqlCommand.CommandText, sqlConnection);
            daM.Fill(results);
            sqlConnection.Close();
        }
    }
    catch(Exception e)
    {
        Console.WriteLine("Error " + e.StackTrace);
    }

    Console.WriteLine("Retrieving results for query " + _query);
    Console.WriteLine("Total Results: " + results.Rows.Count);
    return results;
}

I'm not sure where to look for the culprit. Setting a more explicit timeout does nothing, and as I said there's no way to further improve the query that we've been able to find. The connection string has the following parameters:

server =

Integrated Security = SSPI

database =

Connection Timeout = 0

Any advice of where I should look next? We are using Microsoft SQL Server.

Answer 1

You have set sqlCommand.CommandTimeout, but later you've created SqlDataAdapter as

SqlDataAdapter daM = new SqlDataAdapter(sqlCommand.CommandText, sqlConnection)

Here adapter implicitly creates and uses new SqlCommand (not the one you've configured) since you've passed there command text, not instance of SqlCommand.

Use another constructor of SqlDataAdapter and create it like

 SqlDataAdapter daM = new SqlDataAdapter(sqlCommand)

Answer 2

Set timeout on SqlConnection does not work in your case, you need do it on SqlDataAdapter.

daM.SelectCommand.CommandTimeout = Value;

Google for "How do you change a SqlDataAdapter .CommandTimeout?"