Retrieving Data Using the DataReader

Article
10/17/2014

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, rather than waiting for the entire results of the query to be returned, and (by default) storing only one row at a time in memory, reducing system overhead.

After creating an instance of the Command object, you create a DataReader by calling Command.ExecuteReader to retrieve rows from a data source, as shown in the following example.

Dim myReader As SqlDataReader = myCommand.ExecuteReader()
[C#]
SqlDataReader myReader = myCommand.ExecuteReader();

You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference 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, see the OleDbDataReader Class and the SqlDataReader Class. Using the typed accessor methods, when the underlying data type is known, reduces the amount of type conversion required when retrieving the column value.

Note The .NET Framework version 1.1 includes an additional property for the DataReader, HasRows, which enables you to determine if the DataReader has returned any results before reading from it.

The following code example iterates through a DataReader object, and returns two columns from each row.

If myReader.HasRows Then
  Do While myReader.Read()
    Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
  Loop
Else
  Console.WriteLine("No rows returned.")
End If

myReader.Close()
[C#]
if (myReader.HasRows)
  while (myReader.Read())
    Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
else
  Console.WriteLine("No rows returned.");

myReader.Close();

The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory.

Closing the DataReader

You should always call the Close method when you have finished using the DataReader object.

If your Command contains output parameters or return values, they will not be available until the DataReader is closed.

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to 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 Programming for Garbage Collection.

Multiple Result Sets

If multiple result sets are returned, the DataReader provides the NextResult method to iterate through the result sets in order, as shown in the following code example.

Dim myCMD As SqlCommand = New SqlCommand("SELECT CategoryID, CategoryName FROM Categories;" & _
                                         "SELECT EmployeeID, LastName FROM Employees", nwindConn)
nwindConn.Open()

Dim myReader As SqlDataReader = myCMD.ExecuteReader()

Dim fNextResult As Boolean = True
Do Until Not fNextResult
  Console.WriteLine(vbTab & myReader.GetName(0) & vbTab & myReader.GetName(1))

  Do While myReader.Read()
    Console.WriteLine(vbTab & myReader.GetInt32(0) & vbTab & myReader.GetString(1))
  Loop

  fNextResult = myReader.NextResult()
Loop

myReader.Close()
nwindConn.Close()
[C#]
SqlCommand myCMD = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;" +
                                  "SELECT EmployeeID, LastName FROM Employees", nwindConn);
nwindConn.Open();

SqlDataReader myReader = myCMD.ExecuteReader();

do
{
  Console.WriteLine("\t{0}\t{1}", myReader.GetName(0), myReader.GetName(1));

  while (myReader.Read())
    Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));

} while (myReader.NextResult());

myReader.Close();
nwindConn.Close();

Getting 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 will contain one row for each column of the result set. Each column of the schema table row maps to a property of the column returned in 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 code example writes out the schema information for DataReader.

Dim schemaTable As DataTable = myReader.GetSchemaTable()

Dim myRow As DataRow
Dim myCol As DataColumn

For Each myRow In schemaTable.Rows
  For Each myCol In schemaTable.Columns
    Console.WriteLine(myCol.ColumnName & " = " & myRow(myCol).ToString())
  Next
  Console.WriteLine()
Next
[C#]
DataTable schemaTable = myReader.GetSchemaTable();

foreach (DataRow myRow in schemaTable.Rows)
{
  foreach (DataColumn myCol in schemaTable.Columns)
    Console.WriteLine(myCol.ColumnName + " = " + myRow[myCol]);
  Console.WriteLine();
}

OLE DB Chapters

Hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter) can be retrieved using the OleDbDataReader. When a query that includes a chapter is returned as a DataReader, the chapter is returned as a column in that DataReader and is exposed as a DataReader object.

The ADO.NET DataSet can also be used to represent hierarchical rowsets using parent-child relationships between tables. For more information, see Creating and Using DataSets.

The following code example uses the MSDataShape Provider to generate a chapter column of orders for each customer in a list of customers.

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
                                         "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim custCMD As OleDbCommand = New OleDbCommand("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " & _
                                         "  APPEND ({SELECT CustomerID, OrderID FROM Orders} AS CustomerOrders " & _
                                         "  RELATE CustomerID TO CustomerID)", nwindConn)
nwindConn.Open()

Dim custReader As OleDbDataReader = custCMD.ExecuteReader()
Dim orderReader As OleDbDataReader

Do While custReader.Read()
  Console.WriteLine("Orders for " & custReader.GetString(1)) ' custReader.GetString(1) = CompanyName

  orderReader = custReader.GetValue(2)                       ' custReader.GetValue(2) = Orders chapter as DataReader

  Do While orderReader.Read()
    Console.WriteLine(vbTab & orderReader.GetInt32(1))       ' orderReader.GetInt32(1) = OrderID
  Loop
  orderReader.Close()
Loop

custReader.Close()
nwindConn.Close()
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" +
                                                "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

OleDbCommand custCMD = new OleDbCommand("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " +
                                      "  APPEND ({SELECT CustomerID, OrderID FROM Orders} AS CustomerOrders " +
                                      "  RELATE CustomerID TO CustomerID)", nwindConn);
nwindConn.Open();

OleDbDataReader custReader = custCMD.ExecuteReader();
OleDbDataReader orderReader;

while (custReader.Read())
{
  Console.WriteLine("Orders for " + custReader.GetString(1)); 
// custReader.GetString(1) = CompanyName

  orderReader = (OleDbDataReader)custReader.GetValue(2);      
// custReader.GetValue(2) = Orders chapter as DataReader

  while (orderReader.Read())
    Console.WriteLine("\t" + orderReader.GetInt32(1));        
// orderReader.GetInt32(1) = OrderID
  orderReader.Close();
}

custReader.Close();
nwindConn.Close();

Oracle REF CURSORs

The .NET Framework Data Provider for Oracle supports the use of Oracle REF CURSORs to return a query result. An Oracle REF CURSOR is returned as an OracleDataReader.

You can retrieve an OracleDataReader object, that represents an Oracle REF CURSOR using the OracleCommand.ExecuteReader method, and you can also specify an OracleCommand that returns one or more Oracle REF CURSORs as the SelectCommand for an OracleDataAdapter used to fill a DataSet.

To access a REF CURSOR returned from an Oracle data source, create an OracleCommand for your query and add an output parameter that references the REF CURSOR to the Parameters collection of your OracleCommand. The name of the parameter must match the name of the REF CURSOR parameter in your query. Set the type of the parameter to OracleType.Cursor. The ExecuteReader method of your OracleCommand will return an OracleDataReader for the REF CURSOR.

If your OracleCommand returns multiple REF CURSORS, add multiple output parameters. You can access the different REF CURSORs by calling the OracleCommand.ExecuteReader method. The call to ExecuteReader will return an OracleDataReader referencing the first REF CURSOR. You can then call the OracleDataReader.NextResult method to access subsequent REF CURSORs. Although the parameters in your OracleCommand.Parameters collection match the REF CURSOR output parameters by name, the OracleDataReader will access them in the order that they were added to the Parameters collection.

For example, consider the following Oracle package and package body.

CREATE OR REPLACE PACKAGE CURSPKG AS 
  TYPE T_CURSOR IS REF CURSOR; 
  PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, 
                              DEPTCURSOR OUT T_CURSOR); 
END CURSPKG;

CREATE OR REPLACE PACKAGE BODY CURSPKG AS 
  PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, 
                              DEPTCURSOR OUT T_CURSOR) 
  IS 
  BEGIN 
    OPEN EMPCURSOR FOR SELECT * FROM DEMO.EMPLOYEE; 
    OPEN DEPTCURSOR FOR SELECT * FROM DEMO.DEPARTMENT; 
  END OPEN_TWO_CURSORS; 
END CURSPKG;

The following code creates an OracleCommand that returns the REF CURSORs from the previous Oracle package by adding two parameters of type OracleType.Cursor to the Parameters collection.

Dim cursCmd As OracleCommand = New OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn)
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output
[C#]
OracleCommand cursCmd = new OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn);
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;

The following code returns the results of the previous command using the Read and NextResult methods of the OracleDataReader. The REF CURSOR parameters are returned in order.

oraConn.Open()

Dim cursCmd As OracleCommand = New OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn)
cursCmd.CommandType = CommandType.StoredProcedure
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output

Dim rdr As OracleDataReader = cursCmd.ExecuteReader()

Console.WriteLine(vbCrLf & "Emp ID" & vbTab & "Name")

Do While rdr.Read()
  Console.WriteLine("{0}" & vbTab & "{1}, {2}", rdr.GetOracleNumber(0), rdr.GetString(1), rdr.GetString(2))
Loop

rdr.NextResult()

Console.WriteLine(vbCrLf & "Dept ID" & vbTab & "Name")

Do While rdr.Read()
  Console.WriteLine("{0}" & vbTab & "{1}", rdr.GetOracleNumber(0), rdr.GetString(1))
Loop

rdr.Close()
oraConn.Close()
[C#]
oraConn.Open();

OracleCommand cursCmd = new OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn);
cursCmd.CommandType = CommandType.StoredProcedure;
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;

OracleDataReader rdr = cursCmd.ExecuteReader();

Console.WriteLine("\nEmp ID\tName");

while (rdr.Read())
  Console.WriteLine("{0}\t{1}, {2}", rdr.GetOracleNumber(0), rdr.GetString(1), rdr.GetString(2));

rdr.NextResult();

Console.WriteLine("\nDept ID\tName");

while (rdr.Read())
  Console.WriteLine("{0}\t{1}", rdr.GetOracleNumber(0), rdr.GetString(1));

rdr.Close();
oraConn.Close();

The following example uses the previous command to populate a DataSet with the results of the Oracle package.

**Note **It is recommended that you also handle any conversion from the Oracle NUMBER type to a valid .NET Framework type before storing the value in a DataRow to avoid an OverflowException. You can use the FillError event to determine if an OverflowException has occurred. For more information on the FillError event, see Working with DataAdapter Events.

Dim ds As DataSet = New DataSet()

Dim oraDa As OracleDataAdapter = New OracleDataAdapter(cursCmd)
oraDa.TableMappings.Add("Table", "Employees")
oraDa.TableMappings.Add("Table1", "Departments")

oraDa.Fill(ds)
[C#]
DataSet ds = new DataSet();

OracleDataAdapter oraDa = new OracleDataAdapter(cursCmd);
oraDa.TableMappings.Add("Table", "Employees");
oraDa.TableMappings.Add("Table1", "Departments");

oraDa.Fill(ds);

Partager via