Looping through Data with the DataTableReader

 

Looping through Data with the DataTableReader

The DataTableReader class lets you iterate through DataRow objects in one or more DataTable objects. The DataTableReader provides a stable, forward-only, read-only means of looping over DataRow objects. You can also use a DataTableReader to populate many Windows and Web controls without having to write looping code.

If an underlying DataRow is deleted or removed from the DataTable before the DataTable Reader gets to the DataRow object, no attempt is made to retrieve the DataRow object. If a DataRow object that has already been read is deleted or removed, the current position is maintained—there is no resulting shift in position.

If DataRow objects are added to the underlying DataTable object while the DataTableReader is looping, these DataRow objects are included in the DataTableReader iterations only if the DataRow is added after the current position of the DataTableReader object. DataRow objects that are inserted before the current position of the DataTableReader are not included in the iterations.

The DataTableReader contains a method called Read that is executed to load the DataTable Reader with the DataRow at the current position, and then the position is advanced. If the end of file is reached, the Read method returns null. Any attempt to execute the Read method after the Read method returns null always returns null, even if more DataRow objects are added.

The DataSet contains a method called CreateDataReader that returns an instance of the DataTableReader class. If the DataSet contains more than one table, the DataTableReader object reads the first DataTable object in the DataSet.

The following code example demonstrates the use of the DataTableReader to loop over a DataTable and display the Person object's Name property and then move to the Part DataTable and display the PartName property to a TextBox.

image from book

Visual Basic

'Create an initial DataSet
Dim masterData As New DataSet("Sales")
Dim person As DataTable = masterData.Tables.Add("Person")
person.Columns.Add("Id", GetType(Guid))
person.Columns.Add("Name", GetType(String))
person.PrimaryKey = New DataColumn() {person.Columns("Id")}
Dim part As DataTable = masterData.Tables.Add("Part")
part.Columns.Add("Id", GetType(Guid))
part.Columns.Add("PartName", GetType(String))
part.PrimaryKey = New DataColumn() {part.Columns("Id")}

For i As Integer = 0 To 100
    person.Rows.Add(Guid.NewGuid(), "Joe " + i.ToString())
    part.Rows.Add(Guid.NewGuid(), "Part " + i.ToString())
Next
'read the data in the DataTable
Dim rd As DataTableReader = masterData.CreateDataReader()

While (rd.Read())
    TextBox1.AppendText(rd("Name").ToString() + vbcrlf)
End While
rd.NextResult()
While (rd.Read())
    TextBox1.AppendText(rd("PartName").ToString() + vbcrlf)
End While
 

image from book

image from book

C#

//Create an initial DataSet
DataSet masterData = new DataSet("Sales");
DataTable person = masterData.Tables.Add("Person");
person.Columns.Add("Id", typeof(Guid));
person.Columns.Add("Name", typeof(string));
person.PrimaryKey = new DataColumn[] { person.Columns["Id"] };
DataTable part = masterData.Tables.Add("Part");
part.Columns.Add("Id", typeof(Guid));
part.Columns.Add("PartName", typeof(string));
part.PrimaryKey = new DataColumn[] { part.Columns["Id"] };

for (int i = 0; i < 100; i++)
{
   person.Rows.Add(Guid.NewGuid(), "Joe " + i);
   part.Rows.Add(Guid.NewGuid(), "Part " + i);
}
//read the data in the DataTable
DataTableReader rd = masterData.CreateDataReader();
while (rd.Read())
{
  textBox1.AppendText(rd["Name"].ToString() + "\r\n");
}
rd.NextResult();
while (rd.Read())
{
   textBox1.AppendText(rd["PartName"].ToString() + "\r\n");
}
 

image from book

The DataTableReader class inherits from the DbDataReader class. You'll find more information on the properties inherited from the DbDataReader in the next chapter.