14.4 Reading Database Records Using SqlDataReader

14.4 Reading Database Records Using SqlDataReader

You want to read each record within a certain table in an SQL database.

Technique

The SqlDataReader class provides a quick forward-only reader that streams data from a database. Using an SqlDataReader does not utilize a data adapter or a DataSet , leaving the in-memory data-storage details up to the developer.

Two objects are used in conjunction with the SqlDataReader . The first is an SqlConnection , and the second is an SqlCommand used to perform the database query. After the SqlConnection is created, create a new SqlCommand object passing a string used for the SELECT command and the SqlConnection object. Next, open the database connection by calling the Open method from the SqlConnection object. You create an SqlDataReader by calling the ExecuteReader method from the SqlCommand object, which returns an SqlDataReader ready for use.

The SqlDataReader reads a record from the database each time its Read method is called. If no more records are left to read, the method returns false . Furthermore, calling Read reads the record into the object and you must then extract any fields using one of several reading methods defined in the SqlDataReader class corresponding to the data type of the column. Each of these methods uses an integer denoting the column index of the data you want to read. For instance, if a cell contains integer data in the fourth column, you call the GetInt32 method passing the integer 4 as a parameter. The following code makes a connection to the Northwind database and creates an SqlCommand object that returns all the records from the Products table. After the ExecuteReader method is called from the SqlCommand object, a while loop is created to read all the records from the table. Within the loop, the code writes each cell within the current record to the console using the GetValue method. This method simply returns the corresponding cell data in a System.Object :

 using System; using System.Data; using System.Data.SqlClient; namespace _4_SqlReader {     class Class1     {         [STAThread]         static void Main(string[] args)         {             string connectionString = "Integrated Security=SSPI; " +                 "Data Source=VCSMARKHSCH6;Initial Catalog=Northwind;";             SqlConnection connection = new SqlConnection( connectionString );             SqlCommand select =                 new SqlCommand("SELECT * From PRODUCTS", connection );             SqlDataReader rdr;             connection.Open();             rdr = select.ExecuteReader();             bool writeColumns = true;             while (rdr.Read())             {                 if( writeColumns == true )                 {                     for( int i = 0; i < rdr.FieldCount; i++ )                     {                         Console.Write(rdr.GetName(i) + "\t" );                     }                     Console.WriteLine();                     writeColumns = false;                 }                 for( int i = 0; i < rdr.FieldCount; i++ )                 {                     Console.Write( "\"{0}\" ", rdr.GetValue(i).ToString() );                 }                 Console.WriteLine();             }             rdr.Close();             connection.Close();         }     } } 

Comments

The previous recipes within this chapter demonstrated how to set up data adapters to fill a DataSet object as well as the method to create a typed DataSet . All these operations created several objects in the process to contain the data, and once the data was read, it was cached in a DataSet object to support random access. The SqlDataReader class takes an opposite approach to data access. Instead of creating several different objects to create an in-memory representation of the data, it leaves that functionality up to you. In other words, an SqlDataReader simply reads data and discards it with each subsequent Read method call. Additionally, because this data is discarded, you cannot make random access, which is why an SqlDataReader is known as a forward-only reader.

The two methods shown so far in this chapter both support the access of data but use different methodologies. Choosing one depends on the type of functionality you need. If your goal is to use the data throughout the lifetime of an application, with the ability to update the database periodically, then using a data adapter coupled with a DataSet is the best method. If you need to simply read data using the most efficient way possible, with regards to performance, then consider using an SqlDataReader or other suitable data reader.