The Command Class | C# | TrickCode

Each data provider has its Command class which is used to execute SQL commands or stored procedures to the database. To specify the command to use, the Command class has a CommandText property that accepts the SQL command or stored procedure as a string.

Each Command class implements the IDbCommand interface which exposes some properties and methods.

Property	Description
CommandText	It contains the SQL command or stored procedure or the name of a table.
CommandTimeout	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	The Command class must be hooked to an open connection which is the connection where the command is to be executed.
Parameters	A collection of parameters defined in the CommandText.

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 command and returns a forward-only read-only cursor in the form of a DataReader.
ExecuteNonQuery()	Executes the command and returns the number of rows that were affected. Often used with record UPDATE, DELETE, or INSERT statements.
ExecuteScalar()	Executes the command, and retrieves a single value. Used with aggregate functions and in cases where you want to return the first column of the first row of a result set.

IDbCommand Methods

Let’s create an example of how we can use a Command object to execute an SQL command. The command will insert a new Employee record in our database. To execute commands that don’t return rows, (such as UPDATE, DELETE, INSERT or CREATE), we use the ExecuteNonQuery method which returns the number of rows affected as an integer.

SqlCommand command = new SqlCommand();
command.CommandText = @"INSERT INTO Employees VALUES (4, 'Sam', 'Fisher', 50)";
int rows = command.ExecuteNonQuery();
Console.WriteLine("Rows Affected = {0}", rows);
Rows affected = 1

If we want to return single values from the database, such as the number of rows a table has, we can use the ExecuteScalar method. It returns an object so we need to convert the result into the proper value.

SqlCommand command = new SqlCommand();
command.CommandText = @"SELECT COUNT(*) FROM Employees";
int numberOfRows = (int)command.ExecuteScalar();
Console.WriteLine("Number of rows = {0}");
Number of Rows = 4

The SQL Expression:

SELECT COUNT(*) FROM Employees 

Uses the COUNT aggregate function to count the number of rows a table has. It accepts one parameter which is the table column that the function will count the number of data. Passing an * instructs the COUNT to determine the number of rows in a table. The result of the expression is the number of rows which is then converted to int using a cast, then printed to the user.