2

I am new to ADO.net I need to retrieve from DB a set of rows, then iterate one-be-one and send them, conditionally, to some objects which may UPDATE or DELETE received row from DB. From SqlDataReader documentation I didn't understand properly how it works (does it Read from DB all rows or only some of them or one-by-one?) From MSDN:

Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader.

  • When exactly result is returned? During command.ExecuteReader() or during reader.Read()? And what is the content of Result - all data or partial?
  • What is "network buffer" on local machine?
  • From where Read reads data? From DB or from Cache?

Will affect data modifying (UPDATE or DELETE) retrieving from DB of next data? Code stub: 

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(queryString, connection))
{
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader());
    {
    while (reader.Read())
        {
            //ReadSingleRow...
        //Perform some checks and if TRUE send to a manager obeject whcih perform UPDATE or DELETE on this record
        }
    }
}

or better to use SqlDataAdapter with DataSet or DataTable here?

torrential coding
  • 1,755
  • 2
  • 24
  • 34
ALZ
  • 1,997
  • 2
  • 27
  • 44
  • Have you tried Activity monitor from SSMS when you execute the code you will see what SQL is sent to the server. – Irfarino Apr 17 '13 at 17:11
  • still no, but I will do it. But my question is more theoretical - I want to understand how it works concretely, because workaround always will be found – ALZ Apr 17 '13 at 17:14
  • Please ignore the earlier comment it timed out on me. Have you tried debugging your code and use Activity monitor in SSMS to see what SQL is being executed and when, try stepping through the code and you will see exactly what happens. To answer your first point, the command.executereader() method will submit the comand to the server. The results are stored on the client PC have a look at this link[ADO.NET](http://msdn.microsoft.com/en-ca/library/haa3afyz(v=vs.71).aspx). Have a look at this book,Beginning C# 5.0 Databases from APress, excellent resourse on ADO.NET programming. – Irfarino Apr 17 '13 at 17:17

1 Answers1

0

For what you're describing, it would be better to use a SqlDataAdapter and a DataTable - these are designed as an "offline cache" of server data that will track any changes made and can update the server's copy when SqlDataAdapter.Update() is called.

SqlDataReader is designed as a forward-only, read-only stream of data from the server - you would have to build your own logic to update the server.

To answer your other questions:

When exactly result is returned?

Results are returned when ExecuteReader() completes. Depending on how you look at it, these are either partial or complete results - the server has completely finished executing the query and is sending all of the results to the client, but the client is only dealing with the results one packet at a time.

What is "network buffer" on local machine?

The "network buffer" that the MSDN is talking about refers to an 8Kb (or whatever size is set by the "Packet Size" connection string keyword) byte array that stores latest network packet read from the server. This is a standard practice for applications that deal with network data - you copy a chunk of data from the network into a local buffer, parse that data and then get the next chunk.

From where Read reads data? From DB or from Cache?

Read() processes data from the network buffer until there is no more data left in the buffer, it will then read the next packet from the network into the buffer, and continue processing that.

Daniel Paoliello
  • 161
  • 9