Can I interrupt data reader during reading?

Question

It is possible to stop a running reader?

Scenario: I have a table with 100000 data sets

CREATE TABLE stock (
uid bigint NOT NULL,
name text,
quantity integer,
x bytea,
y bytea
);

and a console application (.NET 4.0, Npgsql 2.0.11.0/2.0.11.92) for reading data

conn = new NpgsqlConnection("Server=localhost;Database=postgres;User id=postgres;password=postgres;Timeout=600;CommandTimeout=600;ConnectionLifeTime=600;");
using (new ConnectionOpen(conn))
using (var ta = conn.BeginTransaction(IsolationLevel.Snapshot))
{
    IDbCommand command = conn.CreateCommand("SELECT * from stock;");
    command.SetTransaction(ta);
    IDataReader reader = command.ExecuteReader();

    int n = 0;
    while (!reader.IsClosed && reader.Read())
    {
        n++;

        if (n > 5000)
        {
            if (reader != null)
            {
                 ((NpgsqlDataReader)reader).Close();
            }
        }
     }
     ((NpgsqlDataReader)reader).Dispose();
     reader = null;
}

I have observed data reader can not really stopped. It seems data reader reads all rows first and returns normally afterwards.

This example is an abstract of a bigger application where user will stop data reader by pressing a button because reading takes too long.

Answer 1

I know this thread is rather old, but I believe the correct answer to this person's question is as follows:

command.Cancel(); //execute before closing the reader
reader.Close();

By calling DbCommand.Cancel(), you're indicating that no further records should be processed and that the command (including the underlying query) should halt immediately and get out quick. If you don't cancel the command, when you try to close the DbDataReader (or break out of a loop/using block), and you're dealing with a large number of records being returned, the Close() method fills in the values for output parameters, return value, and RecordsAffected.

If you try to close the reader before it’s read all of the records, Close tries to read all the data and fill out those values, and it will just seem to hang (most likely resulting in some sort of timeout exception being thrown). If you don’t care about the remaining values in the result set — and you most likely don’t if you’re breaking out of the read loop — you should cancel the underlying command before calling Close().

Parts of the above information was retrieved from: https://www.informit.com/guides/content.aspx?g=dotnet&seqNum=610