- Statements that Select Data
- Statements that Do Not Select Data
- Other Methods for Selecting Data
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Other Methods for Selecting Data
The ExecuteQuery function shown in Listing 3 uses an OleDbDataAdapter object to fetch data from the database. The OleDbCommand and SqlCommand objects provide several other methods you can use to read data, depending on how you want to process it.
The ExecuteReader method executes the query, and returns an OleDbDataReader or SqlDataReader object representing the data. This object contains methods for moving through the data one record at a time in a forward-only manner. The Item method returns the value of a field in the current record. A whole series of methods (GetString, GetDouble, GetDateTime, and so forth) fetch a field's value as a specific data type. The Read method moves to the next record. If you want to step through the records one at a time, possibly stopping before you reach the last one, the ExecuteReader method can be useful.
The SqlCommand object's ExecuteXmlReader method returns an XmlReader object that you can use to walk through selected XML data. The query must return data in a valid XML format so this can be a little tricky. Unless you are working with XML, it's also quite round about, and using an OleDbDataReader or SqlDataReader is much easier. For more information on using XML with Visual Basic .NET and examples that use the XmlReader, see my book Visual Basic .NET and XML (Wiley, 2002, http://www.vb-helper.com/xml.htm).
Finally, the ExecuteScalar method provides an efficient way to execute a SQL statement that returns a single value. For example, the following statement returns a single value giving the number of records in the Books table.
SELECT COUNT (*) FROM Books
You could use a data adapter's Fill method to copy this value into a DataSet or DataTable, and then dig through the data objects to find the single value. Or you could use ExecuteReader or ExecuteXmlReader to read the value. All of these methods waste a lot of time setting up objects that you really aren't going to use anyway.
The ExecuteScalar method is much easier and more efficient. It returns an object representing the first column in the first row of the result of the SQL statement. It doesn't need to build elaborate DataTable structures or OleDbDataReader objects; it just returns a single value. For example, the following Visual Basic code displays the number of records in the Books table.
oledb_command.CommandText = "SELECT COUNT (*) FROM Books" MsgBox(oledb_command.ExecuteScalar() & " records")
That's a lot easier than using the Fill statement or looping through records using a data reader.