The OleDbCommand Class

Command objects represent SQL commands or SQL stored procedures that you execute in a database. For example, to retrieve data from a database, you can create a connection object, open the connection with its Open method, and then assign the open connection object to the Connection property of a command object. You can then assign the command object to a command property of a data adapter, such as the SelectCommand property (which lets you retrieve rows of data from the database when you call the data adapter's Fill method). Besides the SelectCommand property, data adapters also support UpdateCommand, InsertCommand, and DeleteCommand properties, each of which takes connection objects that perform these various functions.

As you can guess, you use OleDbCommand objects with OLE DB connections, and SqlCommand objects with SQL Server connections. How do you place the SQL you want to use in a command object? You can either assign that text to the command object's CommandText property, or you can pass it to the command object's constructor, like this, where I'm selecting all the records in the pubs database's authors table:

Dim Command1 As OleDbCommand = _
     New OleDbCommand("SELECT * FROM authors")

Now I can set the type of the command, which, for SQL statements, is CommandType.Text (this is the default), and assign an open connection to the command's Connection property:

Dim Command1 As OleDbCommand = _
     New OleDbCommand("SELECT * FROM authors")

Command1.CommandType = CommandType.Text

Connection1.Open()
Command1.Connection = Connection1

Now this command object is ready to go. In this case, I can assign it to a data adapter's SelectCommand property and execute its SQL with the data adapter's Fill method.

You also can use the command object's built-in methods to execute commands in a database, no data adapter needed:

• ExecuteReader— Executes SQL commands that return rows. Note that this method does not return a dataset, it creates a data reader, which is much more simplistic. See "Using a Data Reader" in the Immediate Solutions.

• ExecuteNonQuery— Executes commands that do not return data rows (such as SQL INSERT, DELETE, UPDATE, and SET statements).

• ExecuteScalar— Calculates and returns a single value, such as a sum, from a database.