Each data provider has their Command class which is used to execute SQL commands or stored procedures to the database. Each Command class inherits from the System.Data.Common.DbCommand base class. The following are the different kinds of the Command class for every data provider.

Data Provider Command Class
Sql Server SqlCommand
OLE DB OleDbCommand
ODBC OdbcCommand

Figure 1 – Command Classes

DbCommand implements the IDbCommand interface which exposes some properties and methods as shown in the following tables.

Property Description
CommandText Specifies the SQL command or stored procedure or a name of a table.
CommandTimeout Specifies the time required to wait for the completion of a command before it throws an exception. The default is 30 seconds.
CommandType Accepts a value from the System.Data.CommandType enumeration that will determine the type of command specified in the CommandText property. It has 3 values, Text, for accepting SQL commands, StoredProcedure for stored procedures, and TableDirect to get all the rows and columns of one or multiple tables. Note that by default, Text will be used.
Connection Specifies the connection that the command is associated with. The Command class must be hooked to an open connection which is the connection where the command is to be executed.
Parameters A gathering of Parameter defined in the CommandText.

Figure 2 – IDbCommand Properties

Property Description
Cancel Tries to cancel the command being executed.
CreateParameter Creates a new Parameter object that can be added to Command. Parameters collection.
ExecuteReader Executes the direction and returns a forward-just read-just cursor as a DataReader.
ExecuteNonQuery Executes the order and returns the number of columns that were influenced. Regularly utilized with record UPDATE, DELETE, or INSERT proclamations.
ExecuteScalar Executes the direction and retrieves a single value. Utilized with total capacities and in situations where you need to restore the primary section of the principal column of an outcome set.

Figure 3 – IDbCommand Methods

We will use the SQL Server provider’s SqlCommand class for the following examples. To create a Command object, you can simply use its parameterless constructor.

SqlCommand command = new SqlCommand();

A command is useless without specifying the CommandText which contains an SQL statement that the command will execute.

command.CommandText = "SELECT * FROM Students";

Alternatively, you can immediately specify the command text when creating the Command object using an overloaded constructor of SqlCommand.

SqlCommand command = new SqlCommand("SELECT * FROM Students");

Another required thing that the Command object should have is the Connection object where the command will be executed too. When you have created a Connection(for example, a SqlConnection object), then you can assign its instance to the Connection property of the IDbCommand.

SqlConnection connection = new SqlConnection();
SqlCommand command = new SqlCommand();
command.Connection = connection;

An overloaded version of the constructor allows you to specify the command text and the connection to be used by the command.

SqlCommand command = new SqlCommand("SELECT * FROM Students", connection);

The DbConnection class also offers a CreateCommand() method which returns a DbCommand with the connection already hooked to it. You would then be able to utilize the CommandText property to indicate the order.

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Students";

To execute commands, you first need an open connection. For reading the contents of a database, you can use the ExecuteReader()method which returns a DataReader object that can be used to step through each row of a database table. For updating, inserting, deleting, and any non-query commands, you can use the ExecuteNonQuery() method. These strategies will be found in real life in a later exercise.

Different properties and techniques for the Command classes are held for later exercises. But for now, the ones introduced here are enough to execute basic SQL statements.