1

I've noticed that many people don't understand how queries are performed to retrieve data from a database.

Often there is a code like the following:

var reader = await sqlCommand.ExecuteReaderAsync();
dataTable.Load(reader);

There are several answers on StackOverflow, which present it as an asynchronous download of the data to the DataTable.

However, I believe that the ExecuteReader method does not load data, but only creates an instance of the reader.

I can confirm this with a quote from official documentation:

The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when you're retrieving large amounts of data because the data is not cached in memory.

One person in a dispute with me cited a link and claimed that the data download was taking place there. But I don't see a call to the Read method inside this code! Meanwhile, the ExecuteScalarAsync method just below has such a call.

Hence the data will be loaded with the DataTable.Load method or will be done with the while(reader.Read()) loop. Am I right?

So where are the data loaded: in the ExecuteReader(Async) method or in the Read/Load method?

Alexander Petrov
  • 13,457
  • 2
  • 20
  • 49
  • Yes, I know the correct answer to my question. But I want confirmation from the community to refer to it later in similar disputes. – Alexander Petrov Nov 02 '18 at 09:40
  • SO is not really the place for resolving disputes with your colleagues; especially when the answer is obvious and easily verified with a few lines of code. From the doco: ExecuteReaderAsync() An asynchronous version of ExecuteReader(), which sends the CommandText to the Connection and builds a SqlDataReader. You are correct. – Mitch Wheat Nov 02 '18 at 09:45
  • ReadAsync() : An asynchronous version of Read(), which advances the reader to the next record in a result set. T – Mitch Wheat Nov 02 '18 at 09:47
  • If you want a reliable answer, it might be nessesary to put out a Bounty (once that option becomes avalible). Had to do the same thing to get a reliable answer to my question here: https://stackoverflow.com/questions/49899012/can-you-delete-in-a-replication-based-distributed-database – Christopher Nov 02 '18 at 09:49
  • @MitchWheat - totally agree. But SO has incorrect answers (accepted and upvoted), whom authors I can't convince to fix them. – Alexander Petrov Nov 02 '18 at 09:49
  • You have a connection between the client (you application) and the SQL Server. The Execute Reader sends the Command Text to the server and then 1) Synchronous Mode : Waits for server to send response 2) Asynchronous Mode : Continues. You are dealing with different layers of the network. The Data Transfer is performed at the Network Layer. The ExecuteReader is at the Application layer. So the download is running at the Network Layer. – jdweng Nov 02 '18 at 09:52
  • @MitchWheat - [here](https://stackoverflow.com/a/24399507) and [here](https://stackoverflow.com/a/49078619). – Alexander Petrov Nov 02 '18 at 09:55

1 Answers1

0

When the data is actually retreived is a bit of a tricky question, because you are using a DataReader.

At it's core DataReaders work like Enumerators. Read() does stuff comparable to Current (Current is a property, so it is a funciton call without looking like one). In both cases I can not tell you when exactly the Data is transfered/has been fully built in memory. That is a implementation Detail. I can tell you it has been fully retreived when Read/Current returns it's "I am finished" retrun value rather then the next result.

If I build a Enumerator from a array, then all data is already there in memory. And, that case is not rare, it happens every time you are calling a foreach loop! Foreach does not work with collections, only with Enumerators. Luckily getting a Enumerator from a cllection is so trivial, it is a implicit Conversion.

On the other hand, the Enumerator might be build from a External Data Source. Like Directory.EnumerateFiles() or File.ReadLines(). Latest, the next line is loaded eactly when Current is called. However if the code writer is somewhat smart, then next 1-many results will be loaded in the background between Current calls. And how much it loads ahead, might even be dependant on the Environment (how much RAM you have to waste on this). If hte data is already there when Current is called, it is returned. If not, Current becomes a blocking call until the next result is actually retreived.

Christopher
  • 9,634
  • 2
  • 17
  • 31
  • In this case, it is specifically about obtaining data from RDBMS. – Alexander Petrov Nov 02 '18 at 14:50
  • @AlexanderPetrov: As I said, readers with DB's usually work like Enumerators. I know the data transfer is finished if the "next Entry please" Funciton returns "no more entries". But between "all data is loaded ahead of time" and "only one item is feteched the moment "next entry please" is called, anything is possible. Even with the same piece of code. – Christopher Nov 02 '18 at 15:02