3

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);
}
Ersl
  • 31
  • 2

2 Answers2

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.

Tim
  • 2,878
  • 1
  • 14
  • 19
  • Unfortunately I am not able to move away from the SQLDataAdapter at this time. But I will bear this in mind in the future. I have tried using the ConnectionTimeout but that has no effect on the command execution time. I also have a client that is suffering from hitting the default timeout of 5 minutes. So for whatever reason, it appears that the default timeout is not getting overwritten. – Ersl Feb 27 '15 at 13:59
0

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

Community
  • 1
  • 1
Oscar
  • 13,594
  • 8
  • 47
  • 75
  • The quoted code is from a quick abstract VS project I created to isolate the issue. – Ersl Feb 27 '15 at 11:52
  • 1
    Also, how will reducing the data solve the issue? My problem is that the timeout exception is NOT getting thrown. – Ersl Feb 27 '15 at 11:54
  • @Ersl Sorry, I though you were talking about the exception been thrown. – Oscar Feb 27 '15 at 11:58