none
SqlDataAdapter.Fill() does not time out when it should RRS feed

  • Question

  • Hi

     

    I am populating a DataSet via a SqlDataAdapter. The Fill() method (the overload that takes just the DataSet to populate) is not honouring the CommandTimeout I'm setting (5 seconds).

     

    I need to override the default timeout, as it's a requirement of my app to timeout quickly if the DB is running slowly under heavy load.

     

    Code:

     

     

    conn.Open();

     

    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM MyTable", conn);

     

    adapter.SelectCommand.CommandTimeout = 5;    // Set timeout

     

    DataSet results = new DataSet();

     

    adapter.Fill(results);   // Does not time out at specified time!

     

     

    PS I need to use DataSet over DataReader as I'm passing the results to a remote client via DataSet. Also, I have some other queries accessed via stored procedures that return multiple result sets in the one query

     

    Many thanks

    Monday, June 30, 2008 12:59 PM

Answers

  • It depends what the DataAdapter does during this 5 seconds.

     

    What is the size of the data you are reading from the database? Once the data adapter starts reading, it will continue to read - the timeout is set two between subsequent reads (and not from the beginning). This is because it is a connection timeout and not a total read timeout. Thus, it will timeout when there will be no connectivity with the server for 5 seconds.

     

    If you want to control the total time the data is read, use SqlDataReader to pull the data and read it row by row in loop. Once the 'total time' is over, break the loop.

     

    However, if the data reader does not pull any data and just waits on the connection, this sounds like a bug for me. You can use network monitoring tool or SQL profiler to see if the data is actually beeing read.

     

    Let me know if this help.

    Saturday, July 5, 2008 8:23 AM
    Moderator

All replies

  • ... some more info on this:

     

    I'm using the .NET Framework v2.0.50727

    DB is SQL 2005

     

    Monday, June 30, 2008 1:03 PM
  • It depends what the DataAdapter does during this 5 seconds.

     

    What is the size of the data you are reading from the database? Once the data adapter starts reading, it will continue to read - the timeout is set two between subsequent reads (and not from the beginning). This is because it is a connection timeout and not a total read timeout. Thus, it will timeout when there will be no connectivity with the server for 5 seconds.

     

    If you want to control the total time the data is read, use SqlDataReader to pull the data and read it row by row in loop. Once the 'total time' is over, break the loop.

     

    However, if the data reader does not pull any data and just waits on the connection, this sounds like a bug for me. You can use network monitoring tool or SQL profiler to see if the data is actually beeing read.

     

    Let me know if this help.

    Saturday, July 5, 2008 8:23 AM
    Moderator
  • Thanks Nissam

     

    The size of the data can vary. In my tests I'm returning several thousand rows. In my app this won't happen, but the timeout could be set to as small as a couple of seconds. 

     

    Interestingly, when using the overload of SqlDataAdapter.Fill that takes paging parameters - Fill(dataSet, startRecords, maxRecords, dataTableName), the timeout does work as expected! I can't imagine why this would work differently with timeouts from other overloads.

     

    Regarding the meaning of the command timeout, this is from the MSDN docs on SqlCommand.CommandTimeout:

     

    "This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time."

     

    From this it seems that the timeout is a total read timeout, which is how it behaves in the Fill overload described above, but not in my earlier example.

     

    Seems lika a bug!

    Monday, July 7, 2008 1:20 PM
  • SqlCommand.CommandTimeout property affects only single operation of the command. So the timeout set will be the total time for all network reads during single execution such as SqlCommand.ExecuteReader or SqlDataReader.Read(), SqlDataReader.GetString(). The timeout is reset each time new operation is called.

     

    SqlDataAdapter users public interface of the command, so it calls ExecuteReader once followed by calling several DataReader operations as needed to retrieve the data. So the total time is (one call for ExecuteReader + other operations on command) X CommandTimeout, depending on the parameters.

     

     

    Monday, July 7, 2008 9:01 PM
    Moderator