SqlDataReader connection timeout vs command timeout

Question

I would like to understand relation between connection timeout and command timeout and how one affects other. Please consider this code as an example.

// declare the SqlDataReader, which is used in
// both the try block and the finally block
SqlDataReader rdr = null;

// create a connection object
SqlConnection conn = new SqlConnection("someconnstr");

// create a command object
SqlCommand cmd = new SqlCommand("select * from dbo.mytable", conn);

try
{
    // open the connection
    conn.Open();

    // 1. get an instance of the SqlDataReader
    rdr = cmd.ExecuteReader();

    while (rdr.Read())
    {
        // get the results of each column
        Guid Id = (Guid)rdr["Id"];
        string displayName = (string)rdr["Name"];

        // print out the results
        Console.WriteLine("{0}, {1}", Id, displayName);
    }

    Console.WriteLine("Reading done");
}
catch(Exception ex)
{
    Console.WriteLine(ex.Message);
}

As per MSDN link, command timeout is a cumulative timeout for all reads. That means if you call Read() again, it will have another 30 seconds to complete. I want to set timeout in such a way that I can impose a maximum timeout for all records.

Is connection timeout a good thing for doing this? In the given example, if I set connection timeout to 120 seconds and the while loop does not finish in 120 seconds, will it throw a timeout error?

This question is related to Stackoverflow question.

This should answer your question: [What is the difference between SqlCommand.CommandTimeout and SqlConnection.ConnectionTimeout?](http://stackoverflow.com/questions/847264/what-is-the-difference-between-sqlcommand-commandtimeout-and-sqlconnection-conne) — Shai Cohen, Feb 14 '17 at 02:39
My question is bit different. SqlConnection.ConnectionTimeout is for timelimit for opening connection. I want to impose max time for reading all records (the total time the while loop can take). — murtazat, Feb 14 '17 at 03:06
What is your reasoning for imposing the max time? What led you to conclude that you need to implement a max timeout? — Shai Cohen, Feb 14 '17 at 05:57
Being a single threaded app, it needs to return quickly from the method that has the while loop to read all records. CommandTimeout does not seem useful because every iteration of while loop has 30 seconds to complete. — murtazat, Feb 14 '17 at 06:14
Does this answer your question? [What is the difference between SqlCommand.CommandTimeout and SqlConnection.ConnectionTimeout?](https://stackoverflow.com/questions/847264/what-is-the-difference-between-sqlcommand-commandtimeout-and-sqlconnection-conne) — Michael Freidgeim, Jul 10 '22 at 13:15

score 2 · Answer 1 · edited Jul 01 '20 at 13:04

I want to set timeout in such a way that I can impose a maximum timeout for all records. Is connection timeout a good thing for doing this?

No - a connection timeout is the maximum amount that it will take to open a connection. It has nothing to do with operations after a connection is established.

.That means if you call Read() again, it will have another 30 seconds to complete.

Possibly - it depends on how many network packets each read requires. The sentence before the one you quote states:

This property is the cumulative time-out (for all network packets that are read during the invocation of a method) for all network reads during command execution or processing of the results. For example, with a 30 second time out, if Read requires two network packets, then it has 30 seconds to read both network packets.

It's possible that all of the data could be read in one packet and you'd only need one network read.

If you want a timeout for the while loop you'd need to add a variable and check it within the while loop:

DateTime maxTimeUtc = DateTime.UtcNow.AddSeconds(timeout)
while(rdr.Read())
{
    if(DateTime.UtcNow > maxTimeUtc)
    // do something
}

I would also get in the habit of wrapping the connection, command, and reader in using blocks so that they are disposed of as soon as you are done with them.

SqlDataReader connection timeout vs command timeout

1 Answers1