Need to iterate through SQL table rows, one at a time (table too big to use adapter.Fill)

Question

It's easy enough for me to read through a small SQL Server 2005 table like this:

string cmdText = "select * from myTable";
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connection);
DataTable table = new DataTable();
adapter.Fill(table);

Unfortunately, this method appears to load the entire table into memory, which simply isn't going to work with the gigantic tables I'm working with.

I would like to be able to iterate through the table one row at a time, such that only one row needs to be in memory at once. Something along the lines of:

foreach (DataRow row in rowIteratorObject)
{
  // do something using the row

  // current row goes out of scope and is no longer in memory
}

Kind of similar to the way you can use StreamReader to deal with a text file one line at a time, instead of reading it all in at once. Does anyone know of a way to do this with table rows (or, if I'm barking up the wrong tree, an alternative solution)?

Answer 1

You should use a DataReader:

using( var connection = new SqlConnection( "my connection string" ) ) {
    using( var command = connection.CreateCommand() ) {
        command.CommandText = "SELECT Column1, Column2, Column3 FROM myTable";

        connection.Open();
        using( var reader = command.ExecuteReader() ) {
            var indexOfColumn1 = reader.GetOrdinal( "Column1" );
            var indexOfColumn2 = reader.GetOrdinal( "Column2" );
            var indexOfColumn3 = reader.GetOrdinal( "Column3" );

            while( reader.Read() ) {
                var value1 = reader.GetValue( indexOfColumn1 );
                var value2 = reader.GetValue( indexOfColumn2 );
                var value3 = reader.GetValue( indexOfColumn3 );

                // now, do something what you want
            }
        }
        connection.Close();
    }
}

Answer 2

Just use a SqlDataReader instead.