This chapter is from the book
This chapter is from the book
This chapter is from the book
The DataReader
The DataReader provides a forward-only, read-only stream of data from the data store. The DataReader is best used when either there are many records in the result set and pulling them all in at once would use too much memory, or when you want to iterate through the records to work with the data returned. As a stream of records, the DataReader helps manage memory allocation. Rather than all of the records in the result set being returned at once and using up a chunk of memory on the server, the DataReader streams in one record at a time.
You've seen how to execute a command against a data store, and in the previous listings, the results weren't too big. But imagine if your Managed Command returned a result set with over 100,000 records in it. Now imagine 1,000 users doing that all at the same time. It would use up the memory space for 100,000,000 records of data, and that could spell disaster for your Web application.
What would be ideal is a way to connect to the data store, bring the results back in a stream, and evaluate those results one record at a time. Ideally, this would only use up the memory for one record at a time as well.
This type of functionality is exactly what the DataReader provides. The DataReader is the classic "fire hose" of data access—a forward-only, read-only stream returned from the data store. In Listing 3.4, you bound a DataGrid server control to the DataReader (The DataGrid is covered in depth in Chapter 5, "Using a Basic DataGrid," and Chapter 6, "Altering DataGrid Output."). This set-up the DataGrid to display the entire contents of the stream. However, with a data stream, you can step through the data that's returned very easily and decide how to react to it.
The DataReader exposes a Read() method which advances to the next record in the stream. Using the Read() method you can iterate through the result set evaluating or working with the data.
[VB]
While myDataReader.Read
'Do something with the current row
End While
[C#]
while (myDataReader.Read()){
//Do something with the current row
}
You should be able to come up with a good reason to step through the results of a command execution and evaluate the results. This is something you've done relentlessly as a classic ASP developer (does Do While Not RecordSet.EOF sound familiar?). How often have you looped through ADO RecordSets, checking the values of a particular column and using Response.Write on the RecordSet row if the criterion is met? Or granted access to a page if the user name and password columns match the submitted values?
In Listing 3.5 you will use the DataReader class to iterate through the result set and add any record with the value "USA" to a new DataTable. The DataTable and DataRow classes you learned about in chapter 2 are used in Listing 3.5.
Listing 3.5 Evaluating Data with the DataReader
[VB]
01: <%@ Page Language="VB" %>
02: <%@ Import Namespace="System.Data" %>
03: <%@ Import Namespace="System.Data.SqlClient" %>
04: <script runat="server">
05: Sub Page_Load(Sender As Object, E As EventArgs)
06: Dim myConnection As SqlConnection
07: Dim myCommand As SqlCommand
08: Dim myDataTable As New DataTable
09: Dim myRow As DataRow
10:
11: myConnection = New SqlConnection("server=localhost;
database=Northwind; uid=sa; pwd=;")
12: myCommand = New SqlCommand("SELECT * FROM Customers", myConnection)
13: myConnection.Open()
14: Dim myDataReader As SqlDataReader = myCommand.ExecuteReader()
15:
16: myDataTable.Columns.Add("CustomerID",
System.Type.GetType("System.String"))
17: myDataTable.Columns.Add("CompanyName",
System.Type.GetType("System.String"))
18: myDataTable.Columns.Add("Address",
System.Type.GetType("System.String"))
19: myDataTable.Columns.Add("City",
System.Type.GetType("System.String"))
20: myDataTable.Columns.Add("Region",
System.Type.GetType("System.String"))
21: myDataTable.Columns.Add("Country",
System.Type.GetType("System.String"))
22:
23: While myDataReader.Read
24: If myDataReader("Country") = "USA" Then
25: myRow = myDataTable.NewRow()
26: myRow("CustomerID") = myDataReader("CustomerID")
27: myRow("CompanyName") = myDataReader("CompanyName")
28: myRow("Address") = myDataReader("Address")
29: myRow("City") = myDataReader("City")
30: myRow("Region") = myDataReader("Region")
31: myRow("Country") = myDataReader("Country")
32: myDataTable.Rows.Add(myRow)
33: End If
34: End While
35:
36: myDataGrid.DataSource = myDataTable.DefaultView
37: myDataGrid.DataBind()
38:
39: myConnection.Close()
40: End Sub
41: </script>
[C#]
01: <%@ Page Language="C#" %>
02: <%@ Import Namespace="System.Data" %>
03: <%@ Import Namespace="System.Data.SqlClient" %>
04: <script runat="server">
05: void Page_Load(Object sender, EventArgs e){
06: SqlConnection myConnection;
07: SqlCommand myCommand;
08: DataTable myDataTable = new DataTable();
09: DataRow myRow;
10:
11: myConnection = new SqlConnection("server=localhost;
database=Northwind; uid=sa; pwd=;");
12: myCommand = new SqlCommand("SELECT * FROM Customers", myConnection);
13: myConnection.Open();
14: SqlDataReader myDataReader = myCommand.ExecuteReader();
15:
16: myDataTable.Columns.Add("CustomerID",
System.Type.GetType("System.String"));
17: myDataTable.Columns.Add("CompanyName",
System.Type.GetType("System.String"));
18: myDataTable.Columns.Add("Address",
System.Type.GetType("System.String"));
19: myDataTable.Columns.Add("City",
System.Type.GetType("System.String"));
20: myDataTable.Columns.Add("Region",
System.Type.GetType("System.String"));
21: myDataTable.Columns.Add("Country",
System.Type.GetType("System.String"));
22:
23: while(myDataReader.Read()){
24: if(myDataReader["Country"].ToString() == "USA"){
25: myRow = myDataTable.NewRow();
26: myRow["CustomerID"] = myDataReader["CustomerID"].ToString();
27: myRow["CompanyName"] = myDataReader["CompanyName"].ToString();
28: myRow["Address"] = myDataReader["Address"].ToString();
29: myRow["City"] = myDataReader["City"].ToString();
30: myRow["Region"] = myDataReader["Region"].ToString();
31: myRow["Country"] = myDataReader["Country"].ToString();
32: myDataTable.Rows.Add(myRow);
33: }
34: }
35:
36: myDataGrid.DataSource = myDataTable.DefaultView;
37: myDataGrid.DataBind();
38:
39: myConnection.Close();
40: }
41: </script>
[VB & C#]
42: <html>
43: <form runat="server" method="post">
44: <body>
45: <asp:DataGrid runat="server" id="myDataGrid" />
46: </form>
47: </body>
48: </html>
In chapter 2, you read an overview of the ADO.NET Document Object Model and saw samples of creating DataTables dynamically. Listing 3.5 makes use of that knowledge. On line 08, you create a DataTable dynamically. On lines 16[ed]21, you add columns to the DataTable's Columns collection. On lines 23[ed]34, you step through the results of the SqlCommand execution with the DataReader.Read() method.If the "Country" field is "USA", you create a new DataRow (line 25) and add the values of the current record in the DataReader stream to the row (lines 26[ed]32). On line 33, you add the new DataRow object to the DataTable's Rows collection. Finally, you bind the DefaultView of the dynamically created DataTable to the DataGrid server control. You end up with a table limited to customers in the USA, as shown in Figure 3.3.
){kind=link}
Figure 3.3 You can use the DataReader.Read() method to iterate through the result set and react to the data.