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|
Figure 1 – Command Classes
DbCommand implements the IDbCommand interface which exposes some properties and methods as shown in the following tables.
|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
|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.