A DataReader is an efficient solution for instances when you just want to read some data. This code shows the DataReader in action:

SimpleDataAccess.aspx.cs (excerpt)

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Collections.Generic;

using System.Data.SqlClient;

public partial class SimpleDataAccess : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected List GetProductList()

{

List products = new List();

string connectionString =

ConfigurationManager.ConnectionStrings["NorthwindConnectionStr

?ing"].ConnectionString;

string query = “SELECT * FROM Products”;

using (SqlConnection connection =

new SqlConnection(connectionString))

using (SqlCommand command = new SqlCommand(query, connection))

{

connection.Open();

IDataReader dr =

command.ExecuteReader(

CommandBehavior.CloseConnection

);

while (dr.Read())

{

products.Add(dr["ProductName"].ToString());

}

}f

return products;

}

}

Notice anything unusual about this code? (I’ll give you a hint—I’ve highlighted the interesting lines in bold!) While we’re using an instance of SQLDataReader, we’re talking to it through an IDataReader interface. Talking to objects through interfaces makes our code more portable—for instance, it could more easily be converted to work with a data provider other than SQL Server—so use IDataReader unless you need additional functionality that the basic IDataReader interface doesn’t support.

Look at the documentation for the System.Data.Common namespace to see the full list of classes shared by .NET Framework data providers.

You can work with three primary classes to retrieve data directly from a SQL Server database via a DataReader:

SqlConnection

This class allows us to specify the server and database to which we want to talk, and to provide login information for them.

SqlCommand

Once we’ve connected to a database, we use a SqlCommand to do the actual work.

SqlDataReader

The SqlDataReader allows us to read through the command’s results. The SqlDataReader stays connected to the database while we’re using it, and it only has access to one row of data at a time. A component that provides this kind of data access is often described as a firehose cursor, as we first learned in “How can I get started using ADO.NET? ADO.NET data access data access ADO.NET”, because the DataReader “sprays” the data one row at a time. Don’t bother to ask it about anything other than the current row—it can’t tell you.