CODEWITHSUNDEEP
c#sql-serverado.net.net-3.5
Pellared
Pellared

Reputation: 1292

SqlCommand.ExecuteReader transfers data on Close even without a single Read

Given a database connection via TCP
When I execute following code
Then I see in Task Manager that SQL Server always sends the queried data (when the Reader closes) over the network - even when we do not Read/Fetch them!

var command = new SqlCommand("SELECT TOP 100 Stamp, Blob", connection) { CommandTimeout = commandTimeout };
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.CloseConnection)
{
    // even if we do not read anything here
    //while (reader.Read())
    //{
        //var values = reader.GetValues();
        //if (check condition on values)
        //   break;
    //}
}

Is it normal? What is the cause? Any solutions?

We do not want to download all data returned by the query.

Upvotes: 1

Views: 230

Answers (1)

usr
usr

Reputation: 171178

I believe that TDS does not have the ability to skip results. You cannot tell the server to skip sending them. Instead, the client must skip over them as they stream in.

Here are some options:

  1. Live with this behavior
  2. Cancel the command
  3. Close the connection
  4. Modify the query to not return results you don't need (why can't you do this? Seems like the obvious solution.)

Upvotes: 2

Related Questions