One of the most common tasks that data-aware applications are asked to perform involves executing a query and outputting the results. For managed applications, the DataReader class is the perfect tool for the job. DataReader objects expose the results of database queries as fast, forward-only, read-only streams of data. DataReaders come in two flavors: SqlDataReader for SQL Server databases and OleDbDataReader for other types of databases. The following example uses SqlDataReader to query the Pubs database that comes with Microsoft SQL Server for all the records in the “Titles�table. It then writes the “Title�field of each record to a console window:

SqlConnection connection =
    new SqlConnection ("server=localhost;uid=sa;pwd=;database=pubs");
connection.Open ();

SqlCommand command =
    new SqlCommand ("select * from titles", connection);
SqlDataReader reader = command.ExecuteReader ();

while (reader.Read ())
    Console.WriteLine (reader.GetString (1));

connection.Close ();

The SqlConnection object represents the database connection. Open opens a connection, and Close closes it. SqlCommand encapsulates the query used to extract records from the database. Calling ExecuteReader on the SqlCommand object executes the command and returns a SqlDataReader object. Reading the records returned by the query is as simple as calling SqlDataReader.Read repeatedly until it returns null.

I purposely didn’t include exception handling code in this sample to keep the code as simple and uncluttered as possible. In the real world, you’ll want to use try/catch/finally to recover gracefully from errors and to ensure that the database connection is closed even in the face of inopportune exceptions:

SqlConnection connection =
    new SqlConnection ("server=localhost;uid=sa;pwd=;database=pubs");

try {
    connection.Open ();

    SqlCommand command =
        new SqlCommand ("select * from titles", connection);
    SqlDataReader reader = command.ExecuteReader ();

    while (reader.Read ())
        Console.WriteLine (reader.GetString (1));
}
catch (SqlException e) {
    Console.WriteLine (e.Message);
}
finally {
    connection.Close ();
}

Tailoring this code to work with databases other than Microsoft SQL Server is a simple matter of changing the Sql classes to OleDb classes and modifying the connection string accordingly.

A Command object’s ExecuteReader method executes a query and returns a DataReader encapsulating the results. The complementary ExecuteNonQuery method performs inserts, updates, and deletes. The following code uses a SQL INSERT command to add a record to SQL Server’s Pubs database:

SqlConnection connection =
    new SqlConnection ("server=localhost;uid=sa;pwd=;database=pubs");

try {
    connection.Open ();
    string sqlcmd =
        "insert into titles (title_id, title, type, pub_id, " +
        "price, advance, royalty, ytd_sales, notes, pubdate) " +
        "values ('BU1001', 'Programming Microsoft.NET', " +
        "'Business', '1389', NULL, NULL, NULL, NULL, " +
        "'Learn to program Microsoft.NET', 'Jan 01 2002')";
    SqlCommand command = new SqlCommand (sqlcmd, connection);
    command.ExecuteNonQuery ();
}
catch (SqlException e) {
    Console.WriteLine (e.Message);
}
finally {
    connection.Close ();
}

To update or delete a record (or set of records), you simply replace the INSERT command with an UPDATE or DELETE command. Of course, there are other ways to add, modify, and remove records. The full range of options is discussed in Chapter 12.

DataSet, which belongs to the System.Data namespace, is the centerpiece of ADO.NET. A DataSet is an in-memory database capable of holding multiple tables and even of modeling constraints and relationships. Used in combination with SqlDataAdapter and OleDbDataAdapter, DataSet can handle virtually all the needs of modern-day data access applications and is frequently used in lieu of DataReader to facilitate random read/write access to back-end databases.

The following code fragment uses SqlDataAdapter and DataSet to query a database and display the results. It’s functionally equivalent to the SqlData­Reader example presented earlier:

SqlDataAdapter adapter = new SqlDataAdapter (
    "select * from titles",
    "server=localhost;uid=sa;pwd=;database=pubs"
);

DataSet ds = new DataSet ();
adapter.Fill (ds);

foreach (DataRow row in ds.Tables[0].Rows)
    Console.WriteLine (row[1]);

SqlDataAdapter serves as a liaison between DataSet objects and physical data sources. In this example it executes a query, but it’s capable of performing inserts, updates, and deletes, too. For details, see—you guessed it—Chapter 12.