Retrieve data by a DataReader
Applies to: 
.NET Framework
.NET
.NET Standard
To retrieve data using a DataReader, create an instance of the Command object, and then create a DataReader by calling Command.ExecuteReader to retrieve rows from a data source. The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially.
Note
The DataReader is a good choice when you're retrieving large amounts of data because the data is not cached in memory.
The following example illustrates using a DataReader, where reader represents a valid DataReader and command represents a valid Command object.
reader = command.ExecuteReader();
Use the DataReader.Read method to obtain a row from the query results. You can access each column of the returned row by passing the name or ordinal number of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). For a list of typed accessor methods for data provider-specific DataReaders, see SqlDataReader. Using the typed accessor methods when you know the underlying data type reduces the amount of type conversion required when retrieving the column value.
The following example iterates through a DataReader object and returns two columns from each row.
static void HasRows(SqlConnection connection)
{
using (connection)
{
SqlCommand command = new SqlCommand(
"SELECT CategoryID, CategoryName FROM Categories;",
connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Check if the DataReader has any row.
if (reader.HasRows)
{
// Obtain a row from the query result.
while (reader.Read())
{
Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
reader.GetString(1));
}
}
else
{
Console.WriteLine("No rows found.");
}
// Always call the Close method when you have finished using the DataReader object.
reader.Close();
}
}
Close the DataReader
Always call the Close() method when you have finished using the DataReader object.
Note
If your Command contains output parameters or return values, those values are not available until the DataReader is closed.
Important
While a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.
Note
Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Garbage Collection.
Retrieve multiple result-sets using NextResult
If the DataReader returns multiple result sets, call the NextResult method to iterate through the result sets sequentially. The following example shows the SqlDataReader processing the results of two SELECT statements using the ExecuteReader method.
static void RetrieveMultipleResults(SqlConnection connection)
{
using (connection)
{
SqlCommand command = new SqlCommand(
"SELECT CategoryID, CategoryName FROM dbo.Categories;" +
"SELECT EmployeeID, LastName FROM dbo.Employees",
connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Check if the DataReader has any row.
while (reader.HasRows)
{
Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
reader.GetName(1));
// Obtain a row from the query result.
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0),
reader.GetString(1));
}
// Hop to the next result-set.
reader.NextResult();
}
// Always call the Close method when you have finished using the DataReader object.
reader.Close();
}
}
Get schema information from the DataReader
While a DataReader is open, you can retrieve schema information about the current result set using the GetSchemaTable method. GetSchemaTable returns a DataTable object populated with rows and columns that contain the schema information for the current result set. The DataTable contains one row for each column of the result set. Each column of the schema table maps to a property of the columns returned in the rows of the result set, where the ColumnName is the name of the property and the value of the column is the value of the property. The following example writes out the schema information for DataReader.
static void GetSchemaInfo(SqlConnection connection)
{
using (connection)
{
SqlCommand command = new SqlCommand(
"SELECT CategoryID, CategoryName FROM Categories;",
connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Retrieve schema information about the current result-set.
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
foreach (DataColumn column in schemaTable.Columns)
{
Console.WriteLine(String.Format("{0} = {1}",
column.ColumnName, row[column]));
}
}
// Always call the Close method when you have finished using the DataReader object.
reader.Close();
}
}