A DataReader object allows forward-only, read-only access to a database. Different properties and strategies for the Command classes are saved for later exercises. Each provider has its own version of DataReader which inherits the System.Data.Common.DbDataReader base class.

Provider DataReader class
SQL Server SqlDataReader
OLE DB OleDbDataReader
ODBC OdbcDataReader

Figure 1 – DataReader Classes

The DbDataReader class contains properties and methods used for reading a row in a database table. Some of this is presented in the following tables.

Property Description
FieldCount Specifies the number of columns of the current row or record.
HasRows Specifies whether the current row has at least 1 row.
IsClosed Specifies whether the DbDataReader is closed.
RecordsAffected Specifies the number of rows that have been updated, inserted, or deleted.

Figure 2 – DbDataReader Properties

Method Description
GetBoolean Gets the value of a column as a boolean value.
GetChar Gets the value of a column as a char value.
GetDataTypeName Gets the name of the data type of the current column.
GetDateTime Gets the value of the column as a DateTime object.
GetDecimal Gets the value of the column as a decimal value.
GetDouble Gets the value of the column as a double value.
GetFieldType Gets the field type of the specified column.
GetInt32 Gets the value of the column as an int value.
GetName Gets the name of the column.
GetOrdinal Gets the column ordinal with the specified column name.
GetString Gets the value of the column as a string value.
GetValue Gets the value of a column as an object.
GetValues Gets all the column of a row as an array of objects.
NextResult Advances the reader to the following outcome when reading the aftereffects of a result of proclamations.
Read Advances the reader to the next record.

Figure 3 – DbDataReader Methods

The DataReader only loads a single row in the memory at a time to ensure the minimum use of memory. DataReader can only be used when a connection is open, so you need to open a connection first and as soon as you are done using it, then you must close the connection. A corresponding command assigned with an SQL SELECT statement should call the ExecuteReader() method to create an instance of DbDataReader object. Again, we will use the SQL Server provider for the following examples. (Suppose that a connection was already declared).

SqlCommand command = new SqlCommand("SELECT * FROM Students", connection);
SqlDataReader reader;

connection.Open();
reader = command.ExecuteReader();

You can see that we first need an Open connection before using the ExecuteReader() method. We did this using the DbConnection.Open() method. An overloaded version of the ExecuteReader() method also exists that accepts a value from the System.Data.CommandBehavior enumeration. Here are some of its values you can use.

Value Description
CloseConnection Immediately closes the connection when the Close method of the DataReader is called.
Default The default behavior of the DataReader.
SingleResult The query returns a single result set.
SingleRow The query is expected to return only a single row.

Figure 4 – System.Data.CommandBehavior Enumeration Values

For example, if you want the reader to only read a single row, then you should pass the SingleRow value to the ExecutReader()method.

reader = command.ExecuteReader(CommandBehavior.SingleRow);

You can also combine command behaviors using the bitwise OR operator.

reader = command.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.CloseConnection);

Once the ExecuteReader() was executed and an instance of DbDataReader was placed in a variable, we can now iterate through each record of the result set that was returned bySELECT statement. The accompanying demonstrates to you proper methodologies to acquire the estimation of each field of each line.

while (reader.Read())
{
   MessageBox.Show(reader["FirstName"].ToString());
}

We used a for loop and inside its condition, we used the Read() method of the DataReader to read the first row of the result set that was returned by the query. If a row was successfully read, the method will return true and continue the loop. Once that method is executed, we can use an indexer for the DataReader object and pass the name of the column. This will come back the result as Associate in Nursing object, therefore, we want to convert it 1st to its correct knowledge sort. After the body of the loop is executed, the Read() method is executed once again to read the next row. If there the row previously read is the last row and no other rows left to be read, then the Read() method will return false and stop the loop. Alternatively, you can use the Get methods of the DataReader which accepts the column index of the value to be retrieved. For example, suppose we want to retrieve the StudentId which is a number, then you can use the following code.

int studentID = reader.GetInt32(0);

This will return the value of the first column (index 0) as a value of type int.

After using the DataReader, you need to close the DataReader and the Connection to release the resources that were used and to make the connection available.

reader.Close();
connection.Close();

We will use the DataReader classes to read the contents of a database in a connected fashion and it will be demonstrated in a later lesson.