Can I interrupt reading data while reading?

Question

Can I stop a working reader?

Scenario: I have a table with 100,000 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 observed that the data reader cannot really be stopped. It seems the data reader first reads all the rows and returns after that.

This example is an abstraction of a larger application in which the user stops the data reader by pressing a button because reading takes too much time.

Answer 1

I know that this thread is quite old, but I believe that the correct answer to this question of a person is as follows:

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

By calling DbCommand.Cancel (), you indicate that further entries should not be processed and the command (including the base request) should be stopped immediately and exit quickly. If you do not cancel the command when you try to close the DbDataReader (or exit the loop / block used) and you are dealing with a large number of returned records, the Close () method fills in the values ​​for the output parameters, the return value, and RecordAffected.

If you try to close the reader before it reads all the records, Close will try to read all the data and fill in these values, and it will just hang (most likely, it will lead to the exception of some kind of timeout exception). If you do not care about the remaining values ​​in the result set, and you most likely do not want you to break out of the reading cycle, you must cancel the basic command before calling Close ().

Portions of the above information were obtained from: https://www.informit.com/guides/content.aspx?g=dotnet&seqNum=610

Answer 2

You can put a break in a while loop, but you’re not sure what / how you attach this to a user action to let them decide when to exit the read loop. Alternatively, you can restructure the code to return the first number of lines and then give them a continue button to return the rest or return the next number x of lines.

Answer 3

This is an assumption.

Possible Solution 1

 ... using (var ta = conn.BeginTransaction(IsolationLevel.Snapshot)) { IDbCommand command = conn.CreateCommand("SELECT * from stock;"); command.SetTransaction(ta); IDataReader reader = command.ExecuteReader(); int n = 5000; //put it in using using(IDataReader reader = command.ExecuteReader()) { //read first N rows for(int i=0;i<n;i++) { //get value from the columns of the current row for (i = 0; i < reader.FieldCount; i++) { Console.Write("{0} \t", reader[i]); } } } } 

Possible separation 2

Use the TOP sql command, see examples

Answer 4

A data reader typically returns chunks of data from a database server (at least how it works with SQL Server). Postgre SQL can behave differently under covers.

Another way to attack this is to load the data as a background task ( BackgroundWorker , Task , etc.). This way your UI thread remains responsive, and it doesn't matter how the reader is implemented under the covers.