DataReader class
1. Create a DataReader object
The front mentioned that there is no constructor to create a DataReader object. Usually we use the COMMAND class ExecuteRader method to create a DataReader object:
SQLCommand cmd = new SQLCOMMAND (CommandText, ConnectionObject)
SqlDataReader DR = cmd.executeRead ();
The most common usage of the DataReader class is to retrieve the records returned by the SQL query or stored procedure. It is the only set set of only forward and read-only, that is, when using it, the database connection must keep the open state, and can only stop the information from going through the information, and cannot stop the data.
Note: DataReader uses the underlying connection, the connection is it proprietary, which means that the DataReader cannot use the corresponding connection to go to him, such as performing additional commands. Before using DataReader, you must remember to turn off the reader and connection.
2. Use command behavior to specify DataReader characteristics
We use cmd.executeReader () instantiate the DataReader object, in fact, this method has overloaded version, accepts command line parameters, these parameters should be Commandbehavior enumeration:
SqlDatarader DR = cmd.executeReader (Commandbehavior.CloseConnection);
Above we use Commandbehavior.CloseConnection, the role is to automatically close the corresponding ConnectionObject when DataReader is turned off. This avoids we close the Connection object after we forget to close the DataReader object. Don't tell me that you don't like this parameter, you can guarantee that you remember to close the connection. What do you forget? Or or you use your partner development to develop it? This component does not necessarily let you have permission to close the connection. Also commandbehavior.singlerow can return the result set back to a single line, and commandbehavior.singleresult returns the result of multiple result sets. Of course, there is other values for the Commandbehavior enumeration, see MSDN.
3. Traverse records in DataReader
When the ExecuteReader method breaks the DataReader object, the first record is in front of the position of the current cursor. The READ method of the data reader must be called to move the cursor to the first record, and then the first record is the current record. If the reader contains more than one, the read method returns a BOOL value true. That is to say, the role of the Read method is to move the cursor position to the next record within the allowable range. Isn't it a bit similar to rsmovenext, isn't it? If the current cursor indicates the last record, call the Read method to get FALSE. We often do this:
While (Dr.Reader ())
{
// Do Something with the current record
}
Note that if you take a long time for each record, it means that the reader will open for a long time, then the database connection will also maintain long-term open state. It is better to use a non-connected Dataset at this time.
4. Visit the value of the field
There are two ways. The first is the Item property, this property returns the value of the field index or field name corresponding to the field. The second is a GET method, which returns the value of the field index specified by the field. A bit hard to understand, isn't it? Don't tighten, look at OK.
Item property
Each DataReader class defines an ITEM property. For example, now we have a DataReader instance DR. The corresponding SQL statement is SELECT FID, FNAME FROM Friend, then we can use the following method to get the return value: Object id = DR ["fid"];
Object name = DR ["fname"];
or:
Object id = DR [0];
Object name = DR [0];
Note that the index always starts from 0. In addition, you find that we use Object to define the ID and name, yes, the value returned by the Item property is Object, but you can force the type conversion.
INT ID = (int) DR ["FID"];
String name = (String) DR ["fname"];
Remember: Make sure the validity of type conversion is your own responsibility, otherwise you will get an exception.
Get method
Start us have used a change in the first article. Each DataReader defines a set of GET methods, such as the GetInt32 method, the returned field value as a .NET CLR 32-bit certificate. As with the above example we use the following ways to access the values of the FID and FNAME:
INT ID = Dr.Getint32 (0);
String name = Dr.getstring (1);
Note Although these methods turn data from the data source type to .NET data type, they do not perform other data conversions, such as they do not convert 16-bit integers to 32-bit. So you must use the correct GET method. In addition, the GET method cannot use the field name to access the field, that is, the above is:
INT ID = Dr.Getint32 ("FID"); // Error
String name = tr.getstring ("fname"); // error
Obviously, this shortcomings are fatal in some occasions. When your fields are many, or you will come to see you after a while, you will feel hard to understand! Of course we can use other ways to try to solve this problem. A viable method is to use const:
Const int fileindex = 0;
Const int nameIndex = 1;
INT ID = Dr.Getint32 (FidIndex);
String name = Dr.getstring (NameIndex);
This approach is not very good, another good way:
INT NameIndex = Dr.Getordinal ("FNAME"); / / get the index value corresponding to the FNAME
String name = Dr.getstring (NameIndex);
This seems a bit trouble, but when it is necessary to traverse the large number of results of the reader, this method is effective because the index only needs to be executed.
INT FidIndex = Dr.Getordinal ("FID");
INT NameIndex = Dr. Getordinal ("FName");
While (Dr.Read ())
{
INT ID = Dr.Getint32 (FidIndex);
String name = Dr.Getint32 (NameIndex);
}
So far, we have discussed the basic operation of DataReader. As for some advanced supermaphysome of DataReader, we will discuss it later.
