Data Reader - Does It Really Read One Row At A Time? 1

[Skittle]

I have a question regarding the DataReaders.
I think I am missing a concept somewhere and would welcome any help.

In all of the sources I have consulted the DataReader is broadly described as
'a tool to perform read only, forward only against a data source which only reads one row at a time. It does not hold a big dataset in memory.'

So I coded the simple example below but found that when I loop through the DataReader results, the results do not
reflect any changes another program makes to a row that does not yet have the focus in the while loop.
In other words, disproving the 'one row at a time' and 'no big dataset in memory' advantages.

In other words, if I:-
1) break in debug on the 'End While' after the first .READ
2) change a field value in the second row in the table that the .READ has yet to read
3) relaese the debug break to run to the 'End While' again
4) check the contents of row 2 in the debug session and find the change I made outside the program is not reflected
in row 2 in the program

To me this means the results are a dataset behind the scenes at the time the DataReader was executed.
Therefore is must be holding the data in memory and merely allows me to step through the results with a .READ.
So where is the big advantage of using it over a DataAdapter that also reads all of the data? The only difference
the functionality is missing on a DataReader to perform updates which I suppose can be more efficient when performing read only actions.

 Dim MyString As String
        Dim MySQLCommand As New System.Data.SqlClient.SqlCommand
        Dim MySQLConnection As New System.Data.SqlClient.SqlConnection

        Try
            MySQLConnection.ConnectionString = "Integrated Security=true;Initial Catalog=MyDatabase;server=MyServer;"
            MySQLConnection.Open()
            MySQLCommand.Connection = MySQLConnection
            MySQLCommand.CommandType = CommandType.Text
            MySQLCommand.CommandText = "SELECT * FROM MYTABLE"


            Dim mySQLReader As System.Data.SqlClient.SqlDataReader
            mySQLReader = MySQLCommand.ExecuteReader()


            While mySQLReader.Read
                MyString = "Field 0 : " + mySQLReader.Item(0).ToString + " Field 1 : " + mySQLReader.Item(1).ToString
                Console.WriteLine(MyString)
            End While

            mySQLReader.Close()
            MySQLConnection.Close()

        Catch ex As Exception
            MessageBox.Show("Unexpected Program Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[jebenson]

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."

The behavior you are seeing is because of this: "stored in the network buffer on the client". What this means is that as the query executes, the data are streamed from the database server to your machine. If your app reads the data quickly enough, then you'll see one record retrieved at a time. However if your app does not read the data quickly enough, the stream is buffered on your machine's network card. Once that buffer fills up it sends a buffer full message back to the network card on the database server machine, which in turn sends the buffer full message back to the database server, which then stops streaming data. When your app reads more data, this process is reversed and the database server sends more data until the network card's buffer is full...and so forth.

So, there is indeed only 1 row "in memory" (RAM) at any given time, but that does not mean that there is only 1 row read from the database server at a time.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Skittle]

Thanks for your help.
That solves that mystery.



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.