The Data Reader Object

A DataReader object is a pointer into a table of rows that can return individual column values for the current row. You can't create a DataReader object directly. Instead, use the Command object's Execute-Reader method; for example:

// Create a connection object OleDbConnection conn = null; OleDBCommand cmd = null; OleDBDataReader dr = null; conn = New OleDBConnection(

System.Configuration.ConfigurationSettings.AppSettings. Get("Northwind");

cmd = new OleDBCommand("SELECT * FROM Customers", conn); // create the DataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

The last line creates the DataReader. The CommandBehavior.CloseConnection parameter is one of several CommandBehavior enumeration options you can select to determine how the DataReader reads the data from the database. Table 13.1 shows the enumeration options.

Table 13.1: CommandBehavior Enumeration Options

The last line creates the DataReader. The CommandBehavior.CloseConnection parameter is one of several CommandBehavior enumeration options you can select to determine how the DataReader reads the data from the database. Table 13.1 shows the enumeration options.

Table 13.1: CommandBehavior Enumeration Options

Member Name



Tells .NET to close the associated Connection when you close the

DataReader created using the ExecuteReader method.


Sets n.


Prevents SQL Server from applying read locks to the selected rows. The query returns column and primary key information as well as the data. Using KeyInfo, the result is identical to appending the for browse clause to the query. (See the for browse command in SQL Server Books Online for more information.)


The query returns column information only. It does not return data. This option is most useful when you need to discover the names and data types of the columns.


The result of the query is read sequentially to the column level. Use this when your query results contain BLOB (binary large objects) or long text binary fields.


Use this option for queries that return a single field. An example is selecting the last name field from a table using a specific primary key value to determine the row.


Use this option for queries that return a single row. For example, you can use the SingleRow option for queries that select a row with a specific primary key value.

When you create a DataReader, the row pointer does not point at the first row (see Figure 13.9).

When you create a DataReader, the row pointer does not point at the first row (see Figure 13.9).

Figure 13.9: Initial DataReader state

To read the first row, you must issue the Read command. Therefore, if you want to process each row of data and display the values in an HTML table, your code for a DataReader might look something like Listing 13.4.

Listing 13.4: The Web Form ch13-4.aspx Creates an OleDbDataReader and Displays the Data from the Customers Table (ch13-4.aspx.cs)

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.OleDb;

namespace CSharpASP.ch13 {

/// Summary description for ch13 4. /// </summary>

public class ch13 4 : System.Web.UI.Page {

private void Page Load(object sender, System.EventArgs e) { OleDbConnection conn = new

OleDbConnection(System.Configuration.ConfigurationSettings. AppSettings.Get("Northwind"));

OleDbCommand cm = new OleDbCommand

("SELECT * FROM Customers", conn); OleDbDataReader dr;

dr = cm.ExecuteReader(CommandBehavior.CloseConnection); Response.Write("<table border=\"1\">"); while (dr.Read()) {

for (int i = 0; i < dr.FieldCount; i++) { Response.Write("<td>"); Response.Write(dr.GetValue(i)); Response.Write("&nbsp;"); Response.Write("</td>");

Response.Write("</table>"); dr.Close();

// Web Form Designer generated code omittted

The Read method returns true as long as the read was successful, so you would typically call the Read method in a loop until it returns false. The dr.GetValue(i) (see highlighted code) retrieves a column value from the current row in its native format. I was able to use this because all the values are strings.

Warning I've left out the error-trapping in this example and upcoming ones in this chapter for clarity, but in production code, you should never try to access outside resources without wrapping the attempt in a try block and handling any resulting errors.

In a browser, the result looks like Figure 13.10.

While that type of code will look extremely familiar to those of you who have programmed with classic ASP, it's not at all like most Web Form code because it doesn't take advantage of .NET features such as server controls and data binding.

«».»^r^jfcJ^iiiiTHiiii/JJIfjr^^l. " 1 J .n.-

«».»^r^jfcJ^iiiiTHiiii/JJIfjr^^l. " 1 J .n.-


hixra An-doa ^ Chat JT E :ric


Grirxni |

Asa 'Jr.'.J > .^trt I1 i




Mrr»»* 1312



_ ,. J z-jJm <I2m !HjCr:.»ri . , .isfliu JLVJ.' - L


rT- r A ElldfcliJt

HTJW I'tl^lMWSM rjr F-^hhi'1 A'TWTWM H


' DdLrfu^

Ux+i H+er i-wwii* it Ktnbrim jarnauER


j*rr a.r

Ficucxiuc l-lrnkxr-z^i 2-4, cd«r .. . Oh.* NJ-T^ El-.r


£i! | M+irt.1

Unlnmr -tzhz Oiojif UIT-I

BQi3 ipjn


r.-nn* ■ mitw


Figure 13.10: Results from Listing 13.4 in Internet Explorer

Here's a better example. Create a new Web Form, ch13-5.aspx. In Design mode, drag a DataGrid server control onto the form. Name the DataGrid control dgCustomers. Place the control near the upper-left corner of the form. Next, switch to the code-behind class and enter this code in the Form_Load event:

using System.Data.OleDb;

private void Page Load(object sender, System.EventArgs e) { OleDbConnection conn = new OleDbConnection(

System.Configuration.ConfigurationSettings. AppSettings.Get("Northwind")); conn.Open();

OleDbCommand cm = new OleDbCommand("SELECT * FROM Customers", conn);

OleDbDataReader dr = null;

dr = cm.ExecuteReader(CommandBehavior.CloseConnection); // enable the following two lines to set the outer border // dgCustomers.BorderStyle = BorderStyle.Outset // dgCustomers.BorderWidth = Unit.Pixel(3)

dgCustomers.DataSource = dr; dgCustomers.DataBind();

The big difference between this and the code in Listing 13.4 (other than that it's shorter) is that you don't have to write the HTML yourself; instead, you can simply bind the DataGrid to the DataReader. The DataSource property assigns the DataReader as the source of the DataGrid's data. The DataBind method causes the control to fill itself with the data when it renders. ASP.NET takes care of all the formatting.

The output is identical to the ch13-4 Web Form except that the table gridlines are different. You can change the outer (perimeter) border for Server controls using the BorderStyle property. Add these lines just before the highlighted lines in the preceding listing:

DgCustomers.BorderStyle = BorderStyle.Outset; DgCustomers.BorderWidth = Unit.Pixel(2);

Note The preceding code lines exist in the ch13-5.aspx.cs file on but are commented out. Enable the lines to see the outer border.

Note that you can't simply assign a constant integer value as border width because several different units of measurement are available; instead, you must tell the control which unit you want to use. The System.Web.UI.WebControls namespace exposes a Unit structure containing Pixel, Point, and

Percentage members. Select the unit that most closely matches your needs. Remember that specifying measurements in percentages causes the values to change as the user resizes the browser. Typically, you would use the Point or Pixel unit to specify a fixed measurement such as border width.

DataGrid Formatting

The DataGrid display that results from the Web Form chl3-5.aspx is functional but not very attractive. With some experimentation, you can format a DataGrid exactly as you like, but at first, it's much easier to use the predefined styles. Go back to Design view. Select the DataGrid and then click the link called Auto Format Builder that appears just below the Properties window. You may recall following this procedure in Chapter 7, "The SessionState Object." The Auto Format Builder lets you select among a large number of options to change the DataGrid's appearance. Select one you like and run the Web Form again. The result should be much more attractive.

I'm not going to go deeply into formatting options for Web controls because setting formatting properties corresponds very closely to CSS styles (that's how ASP.NET writes them for the client). The fastest way to get started is to apply an Auto Format to the DataGrid and then look at how that affects the properties of the control. Also, look at the generated HTML after applying an Auto Format. You'll see that the DataGrid consists of three main parts: a header, a body, and a footer. Each part has a distinct CSS style property called HeaderStyle, ItemStyle, and FooterStyle, respectively. In addition, a DataGrid can alternate the styles for neighboring rows. You can see the alternating row styles change as you select different Auto Format styles. You control those styles with the AlternatingItemStyle property. The EditItemStyle and SelectedItemStyle properties control the appearance of the currently selected items in the grid. Because DataGrids can page records, there's a PagerStyle property that controls the appearance of the label controls used to page forward and backward through a set of records, as well as a label for the current page.

Like standard CSS styles, the DataGrid styles are hierarchical and additive—more specialized styles such as AlternatingItemStyle inherit from and override styles from more generalized styles such as the ItemStyle property. All these style properties (except the PagerStyle property, which is a DataGridPagerStyle object) are instances of a TableItemStyle class that has the properties required to format a cell, such as BackColor, BorderColor, BorderStyle, BorderWidth,Font,ForeColor, Height, Width, Wrap, HorizontalAlign, and VerticalAlign.

Was this article helpful?

0 0

Post a comment