C # and database Access Technology Summary (11) Data Reader (DataReader) 1

Data reader

When you execute a command that returns a result set, you need a method to extract the data from the result set.

There are two ways to process a result set:

First, use the data reader (DataReader):

Second, use both the data Adapter and the ADO Data set (DataSet).

This section will learn about the data reader.

DataReader class

In ADO, each data provider implements its own DataReader.

A data reader (DataReader) is the simplest way to select some data from a data source, but it is also a weak one.

The DataReader class does not have a constructor, so it cannot be instantiated directly, and a DataReader instance needs to be returned from the command object by invoking their ExecuteReader method.

1. Create a DataReader Object

The DataReader object is never explicitly created using the constructor of the DataReader object in ADO.

In fact, the DataReader class does not provide a public constructor.

People usually invoke the ExecuteReader method of the command class, which returns a DataReader object.

The following code illustrates how to create a SqlDataReader object:

The function of the following code is to read data from the table student and output all data for the data column number and name to the console:

String cnstr="server= (local); database=student; Integrated security=true"; SqlConnection cn=NewSqlConnection (CNSTR); CN. Open ();stringSqlstr="SELECT * FROM Student"; SqlCommand cmd=NewSqlCommand (Sqlstr, CN); SqlDataReader Dr =cmd. ExecuteReader (); while(Dr. Read ()) {String ID=dr["School Number"].    ToString (); String name=dr["name"].    ToString (); Console.WriteLine ("School Number: {0} name: {1}", ID, name);} Dr. Close (); CN. Close ();

The most common use of the DataReader class is to retrieve a SQL query or stored procedure return record.

In addition DataReader is a connected, forward-only, and read-only result set .

That is, when you use a data reader, you must keep the connection open.

In addition, you can traverse the recordset from beginning to end, and only in this order, that is, you can traverse all records in one direction only, and the database connection remains open during this process, otherwise you will not be able to read the data through DataReader.

This means that you cannot stop at a record and move back.

Records are read-only, so the data reader class does not provide any way to modify database records.

Note:

The data reader uses the underlying connection, which is proprietary to the connection.

When the data reader is opened, it is not possible to use the corresponding Connection object to perform any other tasks, such as executing additional commands.

You should turn off the data reader immediately when you are finished reading the data reader's records or you no longer need a data reader.

After reading the data, you need to call the close () method to close the DataReader object.

If you create a DataReader object, you are using another overload of the ExecuteReader method, which is the following code:

SqlDataReader Mydatareader=cmd. ExecuteReader (CommandBehavior. CloseConnection);

closes the underlying connection automatically when the DataReader object is closed, eliminating the need to display the close () method that calls the connection object to close it.

The Execute method of the Command object has an overloaded version that accepts the command behavior arguments.

Although the command text specifies a query that returns a result set, you can provide some instructions on how you want to use the results by executing the command behavior.

Ado. NET defines the CommandBehavior enumeration in the System.dara namespace, with values and specific meanings as shown in the table.

Member name	Description
CloseConnection	When the command is executed, if the associated DataReader object is closed, the associated connection object is also closed
default	< Span style= "font-size:15px;" > This query may return multiple result sets. default does not set CommandBehavior flag, so calling executereader (Commandbehavior.default) is functionally equivalent to calling ExecuteReader ()
keyinfo	< Span style= "font-size:15px;" > This query returns column and primary key information.     Note: When using KeyInfo, use SQL The. NET Framework Data Provider for the server appends the FOR BROWSE clause to the statement being executed. users should be aware of potential side effects, such as interference with the use of SET FMTONLY on statements
schemaonly
sequentialaccess	sequentialaccess is not loading entire rows, Instead, the DataReader loads the data as a stream. Once you have read the contents of a location in the returned data stream, It is no longer possible to read the location or data before that location from the DataReader.
Singleresult	Query returns a result set
SingleRow	The query should return a row. Executing a query may affect the database state. Some. NET framework data providers may, but are not required to, use this information to optimize the performance of the command. When you specify singlerow with the ExecuteReader method of the OleDbCommand object, the. NET Framework Data Provider for OLE DB uses the OLE IRow interface (if available) to perform the binding. Otherwise, it uses the IRowset interface. If your SQL statement should return only one row, specifying SingleRow can also improve application performance. When you execute a query that returns multiple result sets, you can specify SingleRow. In this case, multiple result sets are still returned, but each result set has only one row.

C # and database Access Technology Summary (11) Data Reader (DataReader) 1