Use the DataReader to Process Data One Row at a Time...
By: John Kilgo Date: January 20, 2003 Download the code.

At times you may find the need to do some processing of rows from a database before they are displayed to the user. We all remember processing classic ado recordsets one row at a time. You can do the same thing with .Net's DataReader which produces a very fast forward-only stream of database table data. In this article all we are going to do is display the data rather than alter it. However, we are going to display the data "by hand" one row and column by column at a time. This will show where in the code you can process the data to fit your specific needs.

As usual, we will use a very simple aspx page containing only a button to request retrieval of the data. The actual work will be in the code-behind file. The aspx file is shown below without much explanation except to point out that it contains a button to call a routine to display the data.
<%@ Page Language="vb" Src="ProcessData.aspx.vb" Inherits="ProcessData"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>ProcessData</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body>
<form runat="server">
<asp:Button id="btnGetData" runat="server" text="Get Data" onclick="Get_Data" />
</form>
</body>
</html>
The code-behind file is displayed below. Notice that we are reading the database table rows in one row at a time using a While..End While loop. Within that loop we have a For...Next loop which retrieves each of the table columns for the row we are processing. The values of each column are read using the line "dataReader.GetValue(intColIndex)". It is at this point that we can process the data in any manner we deem necessary. All that we are doing in the example program is wrapping the rows and columns in an html table and displaying them. You could do something quite different with the data.
Imports System
Imports System.Web.UI
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class ProcessData : Inherits Page

  Sub Get_Data(Sender As Object, e As EventArgs)
    Dim intColIndex As Integer
    Dim objConn As New SqlConnection( _
      ConfigurationSettings.AppSettings("ConnectionString"))
    Dim cmdCustomers As New SqlCommand("Select Top 10 CustomerID, " _
        & "CompanyName, ContactName, " _
        & "ContactTitle, Phone, Fax From Customers", objConn)
    Dim dataReader As SqlDataReader

    objConn.Open()
    dataReader = cmdCustomers.ExecuteReader(CommandBehavior.CloseConnection)
    Response.Write("<table border='1'>")

    While dataReader.Read
      Response.Write("<tr>")
      For intColIndex = 0 To dataReader.FieldCount - 1
        Response.Write("<td>")
        Response.Write(dataReader.GetValue(intColIndex))
        Response.Write("</td>")
      Next
      Response.Write("</tr>")
    End While
    Response.Write("</table>")
    dataReader.Close()
  End Sub

End Class
You may download the code here.