asp data reader 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp 

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory.

After creating an instance of the Command object, you create a DataReader by calling Command.ExecuteReader to retrieve rows from a data source, as shown in the following example.

[Visual Basic]
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
[C#]
SqlDataReader myReader = myCommand.ExecuteReader();

You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). For a list of typed accessor methods, see the OleDbDataReader Class and the SqlDataReader Class. Using the typed accessor methods when the underlying data type is known will reduce the amount of type conversion required when retrieving the column value.

The following code example iterates through a DataReader object, and returns two columns from each row.

[Visual Basic]
Do While myReader.Read()
  Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop
myReader.Close()
[C#]
while (myReader.Read())
  Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();

The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory.

Closing the DataReader

You should always call the Close method when you have finished using the DataReader object.

If your Command contains output parameters or return values, they will not be available until the DataReader is closed.

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

Multiple Result Sets

If multiple result sets are returned, the DataReader provides the NextResult method to iterate through the result sets in order, as shown in the following code example.

[Visual Basic]
Dim myCMD As SqlCommand = New SqlCommand("SELECT CategoryID, CategoryName FROM Categories;" & _
                                         "SELECT EmployeeID, LastName FROM Employees", nwindConn)
nwindConn.Open()

Dim myReader As SqlDataReader = myCMD.ExecuteReader()

Dim fNextResult As Boolean = True
Do Until Not fNextResult
  Console.WriteLine(vbTab & myReader.GetName(0) & vbTab & myReader.GetName(1))

  Do While myReader.Read()
    Console.WriteLine(vbTab & myReader.GetInt32(0) & vbTab & myReader.GetString(1))
  Loop

  fNextResult = myReader.NextResult()
Loop

myReader.Close()
nwindConn.Close()
[C#]
SqlCommand myCMD = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;" +
                                  "SELECT EmployeeID, LastName FROM Employees", nwindConn);
nwindConn.Open();

SqlDataReader myReader = myCMD.ExecuteReader();

do
{
  Console.WriteLine("\t{0}\t{1}", myReader.GetName(0), myReader.GetName(1));

  while (myReader.Read())
    Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));

} while (myReader.NextResult());

myReader.Close();
nwindConn.Close();

Getting Schema Information from the DataReader

While a DataReader is open, you can retrieve schema information about the current result set using the GetSchemaTable method. GetSchemaTable returns a DataTable object populated with rows and columns that contain the schema information for the current result set. The DataTable will contain one row for each column of the result set. Each column of the schema table row maps to a property of the column returned in the result set, where the ColumnName is the name of the property and the value of the column is the value of the property. The following code example writes out the schema information for DataReader.

[Visual Basic]
Dim schemaTable As DataTable = myReader.GetSchemaTable()

Dim myRow As DataRow
Dim myCol As DataColumn

For Each myRow In schemaTable.Rows
  For Each myCol In schemaTable.Columns
    Console.WriteLine(myCol.ColumnName & " = " & myRow(myCol).ToString())
  Next
  Console.WriteLine()
Next
[C#]
DataTable schemaTable = myReader.GetSchemaTable();

foreach (DataRow myRow in schemaTable.Rows)
{
  foreach (DataColumn myCol in schemaTable.Columns)
    Console.WriteLine(myCol.ColumnName + " = " + myRow[myCol]);
  Console.WriteLine();
}

OLE DB Chapters

Hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter) can be retrieved using the OleDbDataReader. When a query that includes a chapter is returned as a DataReader, the chapter is returned as a column in that DataReader and is exposed as a DataReader object.

The ADO.NET DataSet can also be used to represent hierarchical rowsets using parent-child relationships between tables. For more information, see Creating and Using DataSets.

The following code example uses the MSDataShape Provider to generate a chapter column of orders for each customer in a list of customers.

[Visual Basic]
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
                                         "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim custCMD As OleDbCommand = New OleDbCommand("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " & _
                                         "  APPEND ({SELECT CustomerID, OrderID FROM Orders} AS CustomerOrders " & _
                                         "  RELATE CustomerID TO CustomerID)", nwindConn)
nwindConn.Open()

Dim custReader As OleDbDataReader = custCMD.ExecuteReader()
Dim orderReader As OleDbDataReader

Do While custReader.Read()
  Console.WriteLine("Orders for " & custReader.GetString(1)) ' custReader.GetString(1) = CompanyName

  orderReader = custReader.GetValue(2)                       ' custReader.GetValue(2) = Orders chapter as DataReader

  Do While orderReader.Read()
    Console.WriteLine(vbTab & orderReader.GetInt32(1))       ' orderReader.GetInt32(1) = OrderID
  Loop
  orderReader.Close()
Loop

custReader.Close()
nwindConn.Close()
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" +
                                                "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

OleDbCommand custCMD = new OleDbCommand("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " +
                                      "  APPEND ({SELECT CustomerID, OrderID FROM Orders} AS CustomerOrders " +
                                      "  RELATE CustomerID TO CustomerID)", nwindConn);
nwindConn.Open();

OleDbDataReader custReader = custCMD.ExecuteReader();
OleDbDataReader orderReader;

while (custReader.Read())
{
  Console.WriteLine("Orders for " + custReader.GetString(1)); 
// custReader.GetString(1) = CompanyName

  orderReader = (OleDbDataReader)custReader.GetValue(2);      
// custReader.GetValue(2) = Orders chapter as DataReader

  while (orderReader.Read())
    Console.WriteLine("\t" + orderReader.GetInt32(1));        
// orderReader.GetInt32(1) = OrderID
  orderReader.Close();
}

custReader.Close();
nwindConn.Close();