DataReader Class note which is very easy

The DataReader Class

The DataReader object represents a read-only, forward-only stream of data, which is ideal for quickly retrieving query results. The DataReader is useful if you don't need the full support for versioning and change tracking provided by the DataSet. Best of all, because the DataReader loads only a single row into memory at a time, it has a small in-memory footprint.

You can't create a DataReader directly. Instead, you must use the ExecuteReader( ) method of a Command object that returns a DataReader.

As with all connection-specific objects, there is a DataReader for every data provider. Here are two examples:

·         System.Data.SqlClient.SqlDataReader provides forward-only, read-only access to a SQL Server database (Version 7.0 or later).

·         System.Data.OleDb.OleDbDataReader provides forward-only, read-only access to a data source exposed through an OLE DB provider.

Typical DataReader access code follows five steps:

1.      Create a Command object with an appropriate SELECT query.

2.      Create a Connection, and open it.

3.      Use the Command.ExecuteReader( ) method, which returns a live DataReader object.

4.      Move through the returned rows from start to finish, one at a time, using the DataReader.Read( ) method. You can access a column in the current row by index number or field name.

5.      Close the DataReader( ) and Connection( ) when the Read( ) method returns false to indicate there are no more rows.

Performing a Query with a DataReader

To retrieve records with a Command and DataReader, you need to use the SELECT statement, which identifies the table and rows you want to retrieve, the filter and ordering clauses, and any table joins:

SELECT columns FROM tables WHERE search_condition

       ORDER BY order_expression ASC | DESC

When writing a SELECT statement with a large table, you may want to limit the number of returned results to prevent your application from slowing down dramatically as the database grows. Typically, you accomplish this by adding a WHERE clause that limits the results.

Example below shows a sample Windows application that fills a list box with the results of a query. The designer code is omitted.

Example. Using a fast-forward DataReader

// DataReaderFillForm.cs - Fills a ListBox

using System;

using System.Windows.Forms;

using System.Data.SqlClient;

public class DataReaderTest : Form

{

    private ListBox lstNames;

    private string connectionString = "Data Source=localhost;" +

        "Initial Catalog=Northwind;Integrated Security=SSPI";

  public DataReaderTest()

  {

    lstNames = new ListBox();

    lstNames.Dock = DockStyle.Fill;

    Controls.Add(lstNames);

    Load += new EventHandler(DataReaderTest_Load);

  }

  public static void Main()

  {

    DataReaderTest t = new DataReaderTest();

    Application.Run(t);

  }

    private void DataReaderTest_Load(object sender, System.EventArgs e)

    {

        string SQL = "SELECT ContactName FROM Customers";

        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand(SQL, con);

        SqlDataReader r = null;

        // Execute the command.

        try

        {

            con.Open();

            r = cmd.ExecuteReader();

            // Iterate over the results.

            while (r.Read())

            {

                lstNames.Items.Add(r["ContactName"]);

            }

        }

        catch (Exception err)

        {

            MessageBox.Show(err.ToString());

        }

        finally

        {

            if (r != null) r.Close();

            con.Close();

        }

    }

}