CommandTimeout not working when using SqlDataAdapt

问题:

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);
}

回答1:

Queries like "select * from" are a bad idea.

What is the reason not to use select *?

That said, maybe you could restric the amount of data to return, by paging or similar way. Reducing the amount of returned data will make it work

回答2:

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.