Having established a connection, you next need to be able to carry out commands against the database.

A Command represents a directive issued to a data provider to do some form of action against the data store.

A command typically declares an SQL statement that either performs a select query to return a result set, an action query to update records from the database ( add, edit, or delete ), or create and modify table structures.

The following table shows examples of typical queries you can use in a command directive.

Commands can be explicitly stated, or issued as a call to a procedure stored in the database itself. A command can also pass parameters and return values.

ADO.NET provides both the SqlCommand and OleDbCommand classes for use with the appropriate data source. To set up a command, you initialize a new instance of the appropriate Command class using one of the methods provided by its constructor.

Basically, a command is instantiated using a given query ( the SQL statement to execute ) and the connection object ( with which to execute the command ). These parameters can be specified either after or while declaring the command object, but before issuing a call to execute the command.

The following code snippets demonstrate how to format a command that does a typical database query on the given connection.

NOTE: This example uses one of the overloaded versions of the SqlCommand constructor. For other examples that may be available, see the individual overload topics.

String query = "SELECT * FROM Authors";
SqlCommand myCmd = new SqlCommand ( query, myConn );

Dim query As String = "SELECT * FROM Authors"
Dim myCmd As new SqlCommand ( query, myConn ) 

The following example declares a simple SqlCommand and displays some properties of the command object.

 Show me 

NOTE: This example uses one of the overloaded versions of the OleDbCommand constructor. For other examples that may be available, see the individual overload topics.

String query = "SELECT * FROM Authors";
OleDbCommand myCmd = new OleDbCommand ( query, myConn );

Dim query As String = "SELECT * FROM Authors"
Dim myCmd As new OleDbCommand ( query, myConn ) 

The following example declares a simple OleDbCommand and displays some properties of the command object.

 Show me 

Setting up commands are but one part of the process. To carry out the action, you need to execute the command. Both the SqlCommand and OleDbCommand classes expose several Execute methods you can use to perform the intended action.

• To return a single value, use ExecuteScalar.
• To execute commands that do not return rows, use ExecuteNonQuery.
• To read a result set returned as a stream of data, use ExecuteReader.

Throughout this workshop, you will encounter examples using the various Execute methods, details of each taken up in a corresponding tutorial.

For now, we shall focus on how to use ADO.NET commands to retrieve data from a database into either a DataReader or a DataSet, the two classes you will most often work with when building data-driven Web applications.