Datareader object and Data Acquisition

Datareader object and Data Acquisition
The datareader object accesses the database in a connection-based manner. That is to say, when accessing the database and performing SQL operations, datareader must be connected to the database all the time. This puts a certain amount of pressure on the database connection load, but the working method of the datareader object will greatly reduce this pressure.
1. common attributes of the datareader object
The datareader object provides sequential and read-only access to the data result set obtained by the command object. Because datareader only performs read operations and stores only one piece of data in the result set in the memory buffer at a time, datareader objects are highly efficient. to query a large amount of data, data does not need to be randomly accessed or modified at the same time. datareader is the preferred choice.
The datareader object has the following common attributes.
Fieldcount attribute: This attribute is used to indicate the number of fields in a row of data obtained by datareader.
Hasrows attribute: Used to indicate whether a datareader contains data.
Isclosed attribute: Used to indicate whether the datareader object is closed.
2. Common Methods for datareader objects
Similarly, the datareader object in SQL Server data provider is called sqldatareader, while
Oledbdatareader in provider.
The datareader object uses pointers to manage the connected result sets, its common methods include closing, reading the next record in the record set, reading the next record set, reading the fields and records in the record set, and determining whether the record set is empty.
1. Close Method
The close method is used to close the datareader object without parameters or return values. Since datareader always maintains a connection with the database when executing SQL commands, the connection object corresponding to this object cannot be used for other operations when the datareader object is enabled. Therefore, when using the datareader object, you must use the close method to close the datareader object. Otherwise, it will not only affect the database connection efficiency, it also prevents other objects from accessing the database using the connection object.
2. bool read () method
Bool
The read () method directs the record pointer to the next record in the current result set. The return value is true or false. After the executereader method of command returns the datareader object, you must use the read method to obtain the first record. When you read a record and want to obtain the next record, you can also use the read method. If the current record is already the last one, false is returned when the read method is called. That is, if this method returns true, you can access the fields contained in the current record.
3. bool nextresult () method
The bool nextresult () method directs the record pointer to the next result set. After you call this method to obtain the next result set, you must use the read method to access the result set.
4. Object getvalue (int I) Method
Object getvalue (int
(I) The method returns the value of the specified column in the row of the current record based on the index value of the input column. Because the data type of the returned Column cannot be predicted in advance, This method uses the object type to receive the returned data.
5. Int getvalues (object [] values) Method
Int getvalues (object []
The values) method saves all the data in the current record row to an array and returns it. You can use the fieldcount attribute to obtain the total number of fields in the record, and then define the length of the array that receives the returned value.
6. Obtain the specified field
Methods for obtaining the specified field include getstring, getchar, and getint32. These methods all contain a parameter that represents the column index, and the returned results are of the object type. You can call the preceding method to obtain the value of a specified column by entering the column index based on the field type.

For example, if the index of the ID column in the database is 0
String id = getstring (0 );
Code You can obtain the id value.
7. Method for returning the data type and column name of a column
You can call the getdatatypename () method to obtain the column type through the input column index. The method is defined as follows:
String getdatatypename (int I)
You can call the getname () method to obtain the name of a column by entering the column index. The method is defined as follows:
String getname (int I)
By using the above two methods, you can obtain the column name and column fields in the data table.
8. bool isdbnull (int I) method:
The parameter of the bool isdbnull (int I) method is used to specify the index number of a column. This method is used to determine whether the value of the column with the specified index number is null. True or false is returned.
3. database access code example of datareader object
The following code uses the datareader object to obtain and access the result set.
// Connection string
Private Static string strconnect = "Data Source = localhost;
Uid = sa; Pwd = ASPnet; database = logindb"
Sqlconnection objconnection = new sqlconnection (strconnect );
Sqlcommand objcommand = new sqlcommand ("", objconnection );
// Set the SQL statement of the Query Class
Objcommand. commandtext = "select * from users ";
Try
{
// Open the database connection
If (objconnection. State = connectionstate. Closed)
{
Objconnection. open ();
}
// Obtain the running result
Sqldatareader result = objcommand. executereader ();
// If the dataread object successfully obtains data, true is returned; otherwise, false is returned.
If (result. Read () = true)
{
// Fields in the output result set
Response. Write (result ["userid"]. tostring ());
Response. Write (result ["nickname"]. tostring ());
Response. Write (result ["userrole"]. tostring ());
}
}
Catch (sqlexception E)
{
Response. Write (E. Message. tostring ());
}
Finally
{
// Close the database connection
If (objconnection. State = connectionstate. open)
{
Objconnection. Close ();
}
// Close the dataread object
If (result. isclosed = false)
{
Result. Close ();
}
}
In the Code, two methods are provided to access the result set using the datareader object. One is to directly obtain the value of a specific field using the result ["userid"] based on the field name; another method is to write in the comment, and access the fields of the dataset in sequence using the fieldcount attribute and the getvalue method through the for loop.
Datareader
An unbuffered data stream is provided, which enables the process logic to effectively process the results returned from the data source in order. Because the data is not cached in the memory, datareader
Is a suitable choice. It is also worth noting that datareader can only read one entry at a time when reading data, which undoubtedly improves the reading efficiency and is generally applicable to the case where only one piece of data is returned. If multiple records are returned, use this object with caution.