Iterating Through Records in an SQL Server Table

In many situations you no longer need to iterate through records in an SQL Server table in ASP.NET pages as you did with ASP. This is because you can now bind DataSet Tables directly to controls.

But on occasion you may still need to iterate through all the records in a DataSet Table so that they can be processed in some special way.

The DataSet Table object contains a Rows collection that allows you to iterate through each record it contains. This technique shows you how to create a loop so that you can process each record from an SQL Server table through a DataSet Table object.

The ASP.NET page presented with this technique iterates through each record in an Employees table using a For loop. The ID of each employee is placed into the Text property of this Label control as it is processed:

<asp:label id="1b1Message" runat="Server" />

The code that iterates through the records fires when the page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)

Within that procedure, these data objects are needed:

Dim DBConn as SQLConnection

Dim DBCommand As SQLDataAdapter Dim DSPageData as New DataSet

You also need a variable that will be used in the For loop:

Dim I as Long

You start by connecting to the SQL Server database:

DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")

Then you retrieve all the employee records:

DBCommand = New SQLDataAdapter ("Select * from Employees", DBConn)

and place them into a DataSet table object:

DBCommand.Fill(DSPageData, "Emps")

Next, you initiate a For block that will go from 0 to the total number of records in the DataSet table minus 1. You retrieve the record count in the DataSet Table object through the Count property of the Rows collection:

For I = 0 To DSPageData.Tables("Emps").Rows.Count - 1

Then within the loop you can process each record. You would use the variable "I" to refer to the CU rrent row within the current iteration of the loop:

'Code to process record

lblMessage.Text = lblMessage.Text _
& "
Processed Record: "
& DSPageData.Tables("Emps"). _ Rows(I).Item("EmpID")

You would then move on to process the next record: Next