- DataReader Versus DataAdapter
- Instantiating the DataReader
- Binding DataReader Results to a Web Control
- Stepping Through Data with the DataReader
- Limitations of the DataReader
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Stepping Through Data with the DataReader
The DataReader offers more granular control of database records than just data binding, however. By using the Read() method of the DataReader object, you can step through each record of the resultset individually. This is akin to the old days of stepping through each record in a recordset using ADO. This gives you the ability to process each record with as much precision as you need.
Listing 8.5 demonstrates how to use a DataReader to pull back data and retrieve individual fields. This example uses some interesting DataReader methods, such as GetOrdinal(), to facilitate the retrieval of the information. When run, the example in Listing 8.5 (VB .NET) and Listing 8.6 (C#) will look like Figure 8.2.
){kind=link}
Listing 8.5 Retrieving Database Fields Using the DataReader in Visual Basic .NET
<% @Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="Main.css">
<!-- End Style Sheet -->
<script language="VB" runat="server" >
Sub Page_Load(Source as Object, E as EventArgs)
Dim conn as SqlConnection
conn = New SqlConnection("Initial Catalog=Northwind;" + _
"Server=(local);UID=sa;PWD=;")
Dim cmd as SqlCommand
cmd = New SqlCommand("SELECT EmployeeID, FirstName, " + _
"LastName, HireDate FROM Employees", conn)
Dim reader as SqlDataReader
conn.Open()
reader = cmd.ExecuteReader()
Dim strBuilder as StringBuilder = New StringBuilder()
Dim First_Name__Ordinal as Int32 = reader.GetOrdinal("FirstName")
Dim Last_Name__Ordinal as Int32 = reader.GetOrdinal("LastName")
Dim Hire_Date__Ordinal as Int32 = reader.GetOrdinal("HireDate")
Dim EmployeeID__Ordinal as Int32 = reader.GetOrdinal("EmployeeID")
while (reader.Read())
strBuilder.Append( _
reader.GetInt32(EmployeeID__Ordinal).ToString() + " " + _
reader.GetString(First_Name__Ordinal) + " " + _
reader.GetString(Last_Name__Ordinal) + " " + _
reader.GetDateTime(Hire_Date__Ordinal).ToString() + _
"<br>" )
end while
output.Text = strBuilder.ToString()
reader.Close()
conn.Close()
End Sub
</script>
</HEAD>
<BODY>
<h1>Stepping through records with the DataReader</h1>
<hr>
<form runat="server" id=form1 name=form1>
<asp:Label id="output" runat="server"></asp:Label>
</form>
<hr>
</BODY>
</HTML>
Listing 8.6 Retrieving Database Fields Using the DataReader in C#
<% @Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="Main.css">
<!-- End Style Sheet -->
<script language="C#" runat="server" >
void Page_Load(Object Source, EventArgs E)
{
SqlConnection conn = new SqlConnection("Initial Catalog=Northwind;" +
"Server=(local);UID=sa;PWD=;");
SqlCommand cmd = new SqlCommand("SELECT EmployeeID, FirstName, " +
"LastName, HireDate FROM Employees", conn);
SqlDataReader reader;
conn.Open();
reader = cmd.ExecuteReader();
StringBuilder strBuilder = new StringBuilder();
int First_Name__Ordinal = reader.GetOrdinal("FirstName");
int Last_Name__Ordinal = reader.GetOrdinal("LastName");
int Hire_Date__Ordinal = reader.GetOrdinal("HireDate");
int EmployeeID__Ordinal = reader.GetOrdinal("EmployeeID");
while (reader.Read())
{
strBuilder.Append(
reader.GetInt32(EmployeeID__Ordinal).ToString() + " " +
reader.GetString(First_Name__Ordinal) + " " +
reader.GetString(Last_Name__Ordinal) + " " +
reader.GetDateTime(Hire_Date__Ordinal).ToString() +
"<br>" );
}
output.Text = strBuilder.ToString();
reader.Close();
conn.Close();
}
</script>
</HEAD>
<BODY>
<h1>Stepping through records with the DataReader</h1>
<hr>
<form runat="server" id=form1 name=form1>
<asp:Label id="output" runat="server"></asp:Label>
</form>
<hr>
</BODY>
</HTML>
In the example in Listing 8.5, the standard ADO.NET objects are created and initialized with the database query in lines 12–23. Then, in line 24, a new StringBuilder object is created that will be used to build the SQL query string. Lines 26–29 use the GetOrdinal() method of the DataReader to locate the ordinal of the various fields in the resultset retrieved from the database. It's faster to retrieve the ordinal values once than to force ASP.NET to locate the values each time they're needed. Line 31 uses the Read() method to load the first record in the resultset and begin a loop. Lines 32–37 use the appropriate data retrieval methods to build a string to display in the Web form. Figure 8.2 shows how this example looks when loaded in a Web browser.
Figure 8.2 The appearance of the Web form in Listing 8.5 when loaded.
The SqlDataReader contains a number of methods for retrieving values from the fields in the data source. In fact, each different field data type has its own method for extracting data. The most common data retrieval functions for the SqlDataReader are listed in Table 8.1.
These methods all accept the integer ordinal of the location of the field within the record. Because the ordinal could change, or might be difficult to locate, the example in Listing 8.5 uses the GetOrdinal() method of the DataReader object. By passing the field name to the GetOrdinal() method, you can return its ordinal location within the array. By placing that value into a variable, you can simplify your code quite a bit.
Table 8.1 Data Retrieval Methods of the DataReader Object
|
Method |
Field Type |
|
GetBoolean() |
Bool |
|
GetByte() |
Byte |
|
GetChar() |
Char |
|
GetDateTime() |
DateTime |
|
GetDecimal() |
Decimal |
|
GetDouble() |
Double |
|
GetFloat() |
Float |
|
GetGuid() |
Guid |
|
GetInt16() |
Int16 |
|
GetInt32() |
Int32 |
|
GetInt64() |
Int64 |
|
GetString() |
String |
The records in the example in Listing 8.5 are enumerated using a WHILE loop in conjunction with the Read() method of the DataReader object. The Read()method advances the DataReader to the next record and returns true or false, depending on whether or not there are more records.