CommandTimeout not working when using SqlDataAdapter to fill a DataTable

Question

I am setting CommandTimeout to 1 second and no TimeoutException is being thrown as expected. The query I am running takes about 7-8 seconds. The timeout does work however when I use ExecuteReader to execute a query rather than trying to fill a DataTable. I have tried setting CommandTimeout when after creating the command and also after creating the DataAdapter.

using(SqlConnection con = new SqlConnection("data source=*****;user id==*****;password==*****;initial catalog==*****;"))
{
    string query = "select * from *****";

    SqlCommand command = new SqlCommand(query, con);
    //command.CommandTimeout = 1;

    CostingDataSet cds = new CostingDataSet();

    SqlDataAdapter da = new SqlDataAdapter(command);
    da.SelectCommand.CommandTimeout = 1;

    Stopwatch stopwatch = Stopwatch.StartNew();
        da.Fill(cds.CostingData);
    stopwatch.Stop();

    Console.WriteLine(stopwatch.ElapsedMilliseconds);
}

Answer 1

The cause is the magic that occurs in the SQLDataAdapter, which is frankly, why they are a bad idea.

My guess is they are using async to perform the fill, which will always ignore command timeouts.

My suggestion: run away from the adapter, and never look back. They aren't that valuable and make everything messier.

If that isn't possible, set your connection timeout in your connection string and it should apply regardless of how the db is accessed.