ADO.NET Programming in Visual Basic .NET By Steve Holzner, Bob Howell
Table of Contents
Chapter 4. The ADO .NET Class Library
This class is used to execute commands against the database. The command can be an SQL statement, a stored procedure, or the name of a table or view. The Command class contains a collection of parameter objects for use in passing values to a stored procedure or SQL statement. The class also contains its own connection object. You can pass in an already created connection object or you can create one directly in the Command class. Either way, the Command class only holds a reference to a connection object so there are no excess connections that will be opened to the server.
There are three different kinds of commands that can be executed by the Command class. These commands are executed by calling one of four execute methods :
ExecuteReader Returns data to the client as rows. This would typically be an SQL select statement or a stored procedure that contains one or more select statements. This method returns a DataReader object that can be used to fill a DataTable object or used directly for printing reports and so forth.
ExecuteNonQuery Executes a command that changes the data in the database, such as an update, delete, or insert statement, or a stored procedure that contains one or more of these statements. This method returns an integer that is the number of rows affected by the query.
ExecuteScalar This method only returns a single value. This kind of query returns a count of rows or a calculated value.
ExecuteXMLReader (SqlClient classes only) Obtains data from an SQL Server 2000 database using an XML stream. Returns an XML Reader object.
The DataReader Class
The DataReader class is used to get the results of a select query as a stream of data from the server. The Read method of the DataReader is used to load a row at a time of data into a buffer. The columns in the row are accessed using the ordinal index of the column. You can also obtain schema information using the GetSchemaTable method. This method returns a DataTable object containing the column information. This information can then be used to create a DataTable to hold the rows returned by the query. The DataReader class can only move forward through the data stream because it opens a forward-only cursor on the server. This is the most efficient way of accessing data.
The XMLReader Class
Similar to the DataReader, the XMLReader gets data from a SQL Server 2000 database, but in XML format. This is useful for accessing SQL Server via HTTP if it is behind a secure firewall. This class can only be used with the SqlClient on SQL Server 2000 databases. In addition, you must format your SQL query using the For XML clause.
