The Command Object

The object requires instantiation like other C# objects. A new instance declaration performs this task:

SqlCommand = new SqlCommand(“select ItemNomenclature from Items”,
conn);

The line of code above instantiates the object, and accepts a string parameter holding the command for execution and a connection object reference. SqlCommand also has overloads, a topic discussed later.

The SQL select command retrieves data sets for review. Performing this task with the SqlCommand object requires the use of the ExecuteReader method, which returns an SqlDataReader object. The example below demonstrates use of the SqlCommand object to obtain the SqlDataReader object:

//Instantiate the command using a query and connection
SqlCommand cmd = new SqlCommand(“select ItemNomenclature from Items”,
conn);

//Call ExecuteReader to obtain query results
SqlDataReader rdr = cmd.ExecuteReader();

Perform insertion of data through using the ExecuteNonQuery method of the SqlCommand object. The following example demonstrates its use:

//The command string
string insertString = @ “
insert into Items
(ItemNomenclature, ItemDescription)
values ('Misc.', 'All items with no clear category')”;

//Instantiate the command using a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);

//Call ExecuteNonQuery to send the command
cmd.ExecuteNonQuery();

Instantiation in the example above differs in the use of a variable as the first parameter of the SqlCommand constructor. Calling the command only requires calling “cmd.” SQL Server automatically adds primary key fields, so they can be omitted in code; furthermore, adding them causes an exception.

The ExecuteNonQuery method also finds use in data updates. Review an example of its use below:

//The command string
string updateStr = @“
update Items
set ItemNomenclature = 'NoCat'
where ItemNomenclature = 'Misc.'”;

//Instantiate the command using a query and connection
SqlCommand cmd = new SqlCommand(updateStr);
						
//Establish the connection property
cmd.Connection = conn;
						
//Call ExecuteNonQuery to send the command
cmd.ExecuteNonQuery();
					

Deletion operations also use the ExecuteNonQuery method. The following example demonstrates record deletion with ExecuteNonQuery:

//The command string
string deleteStr = @“
delete from Items
where ItemNomenclature = 'NoCat'”;
						
//Instantiate the command
SqlCommand cmd = new SqlCommand();
						
//Establish the CommandText property
cmd.CommandText = deleteStr;
						
//Establish the Connection property
cmd.Connection = conn;
						
//Call ExecuteNonQuery to send the command
cmd.ExecuteNonQuery();

Some operations only involve retrieving a single value from a database such as an average, sum, count, or other similar value. ExecuteReader does not offer the most efficient way to perform such tasks. The best option consists of allowing the database to calculate and return the desired value. The following example demonstrates how to use the ExecuteScalar method to retrieve a single value:

//Instantiate the command
SqlCommand cmd = new SqlCommand(“select count(*) from Items”, conn);

//Call ExecuteNonQuery to send the command
int count = (int)cmd.ExecuteScalar();