The Command Class

Team Fly 

Page 409

The Command Class

The Command class allows you to specify the command you want to execute against the database, set its parameters (if any), and finally execute the command. So far you've seen how the DataAdapter interacts with the database with the commands that were generated by the wizard. The advantage of the DataAdapter is that it knows how to prepare the parameters of each command, execute it against the database, and populate a DataSet with the results. In this section you'll learn how to set up custom commands from within your code and execute them outside the context of the DataAdapter. The commands you can execute against a database are SQL statements (queries) and stored procedures. The CommandText property stores the SQL statement, or the name of the stored procedure, that will be executed against the database. The CommandType property specifies the type of the command, and its value is one of the members of the CommandType enumeration:

StoredProcedure The command is the name of a stored procedure.

Text The command is an SQL statement.

TableDirect The command is the name of a table. When the command is executed, it will retrieve all rows and all columns of the specified table. This member can be used only with the OLE DB data provider. You can retrieve the join of multiple tables by setting the CommandText property to a comma-delimited list of table names.

Once the Command object is configured, you can call one of the following methods to execute the command against the database:

ExecuteNonQuery This method is used to execute action queries; it returns the number of rows affected by the query.

ExecuteScalar This method is used to execute a query and returns the first row of the first column in the resultset. This value is returned by a selection query and it's usually an aggregate value, or the result of some calculations. Note that the method doesn't return the return value of a stored procedure. To read the return value, you must set up an output parameter for the stored procedure's return value.

ExecuteReader This method executes a selection query and returns a DataReader object. This object is similar to a StreamReader, in that you can use its methods to read consecutive rows in the resultset and their columns.

ExecuteXmlReader This method executes a selection query and returns an XmlReader object. The query should return its data in XML format, by using the FOR XML clause of the SELECT statement. The XmlReader allows you to read the elements and attributes of the XML document returned by SQL Server.

As you can see, the Command object doesn't provide any methods for filling DataSets. We use the Command object to execute action queries against the database and retrieve the number of rows that were affected. You can also use its ExecuteReader method to retrieve the results of a selection query and use them to populate a custom structure at the client. Strictly speaking, it's possible to use the ExecuteReader method to populate a DataTable manually, but there's no reason on earth to do it. The DataAdapter is much more efficient in filling DataTables. The use of the DataReader class with the ExecuteReader method is demonstrated in the section ''Using the DataReader," later in this chapter.

Team Fly 


Visual Basic  .NET Power Tools
Visual Basic .NET Power Tools
ISBN: 0782142427
EAN: 2147483647
Year: 2003
Pages: 178

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net