4.2 Creating and Executing a Command

When creating a Command object, you have the choice of several constructors. The most useful accepts a CommandText value and a Connection. Here's an example with the SqlCommand class:

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(commandText, con);

For standard providers, there are three ways to execute a command: ExecuteNonQuery( ) , ExecuteReader( ), and ExecuteScalar( ). You choose one of these methods, depending on the type of command you are executing. For example, ExecuteReader( ) returns a DataReader and provides read-only access to query results. We examine the DataReader in Chapter 5.

Some providers include additional members. For example, the ADO.NET SQL Server provider includes an ExecuteXmlReader( ) method that retrieves data as an XML document. We'll examine this specialized version in Chapter 17, which considers ADO.NET's support for XML.

4.2.1 Executing a Command That Doesn't Return Rows

The SQL language includes several nonquery commands. The best known include UPDATE, DELETE, and INSERT. You can also use other commands to create, alter, or drop tables, constraints, relations, and so on. To execute any of these commands, just set the CommandText property with the full SQL statement, open a connection, and invoke the ExecuteNonQuery( ) method. The next sections consider examples that update, delete, and insert records.

4.2.1.1 Updating a record

The UPDATE statement, at its simplest, uses the following syntax:

UPDATE table SET update_expression WHERE search_condition

The UPDATE expression can thus modify a single record, or it can apply a change to an entire batch of records in a single table. Example 4-1 puts the UPDATE statement to work with a simple command that modifies a single field in a single category record in the Northwind database.

Example 4-1. Updating a record

// UpdateRecord.cs - Updates a single Category record

using System;
using System.Data.SqlClient;

public class UpdateRecord
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                     "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "UPDATE Categories SET CategoryName='Beverages'" +
                     "WHERE CategoryID=1";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);

        // Execute the command.
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();

        // Display the result of the operation.
        Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
    }
}

Note that the ExecuteNonQuery( ) method returns the number of rows affected, not the row itself. In order to see the results of the change, you need to either query the row or use a tool such as SQL Server's Enterprise Manager to browse the database.

If the UPDATE statement fails to update any records because the WHERE clause is too restrictive, an error isn't generated. You must examine number of affected rows to determine if this is this case. If you are adding this logic to a custom data access component, you might want to raise an exception if this happens, because it indicates that no update took place.

4.2.1.2 Deleting a record

The SQL DELETE statement simply specifies a search condition that selects one or more records to be removed:

DELETE FROM table WHERE search_condition

You can modify the previous example to delete a record simply by changing the SQL variable:

string SQL = "DELETE FROM Categories WHERE CategoryID=1";

4.2.1.3 Inserting a record

Finally, you can insert a record using a list of column names, followed by a list of column values in the same order:

INSERT INTO table (column_list) VALUES (value_list)

Once again, the console example can be adapted to insert a category record just by modifying the SQL text:

string SQL = "INSERT INTO Categories (CategoryName, Description) " +
    "VALUES ('Beverages', 'Soft drinks, coffees, teas, beers, and ales')";

Note that the category table includes a CategoryID column that is configured as a unique identity value. That means the CategoryID number is created by the data source, which ensures that duplicate IDs don't occur. For that reason, the INSERT statement doesn't include a CategoryID value. As a side effect, this code will always succeed and create a new row with identical information, but with a new CategoryID. (If you want to replace a row you deleted in the previous example, you can manually specify a CategoryID with the value of 1).

4.2.2 Executing a Command That Returns a Single Value

ExecuteScalar( ) method returns a single value. If you perform a query, this will be the first value in the first column of the first row. More likely, you'll use ExecuteNonQuery( ) to return an aggregate value, which is the result of a calculation using a subset of rows.

An aggregate function must be part of a SQL SELECT statement, which indicates the table and (optionally) a search filter and sort order:

SELECT aggregate_expression FROM tables [WHERE search_condition]
       [ORDER BY order_expression ASC | DESC]

Example 4-2 shows how an aggregate command can retrieve the total number of orders for the year 1996.

Example 4-2. Executing an aggregate function

// TotalOrders.cs - Gets the number of order records from 1996

using System;
using System.Data.SqlClient;

public class TotalOrders
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                 "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "SELECT COUNT(*) FROM Orders WHERE " +
                 "OrderDate >= '1996-01-01' AND OrderDate < '1997-01-01'";


        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);

        // Execute the command.
        con.Open();
        int result = (int)cmd.ExecuteScalar();
        con.Close();

        // Display the result of the operation.
        Console.WriteLine(result.ToString() + " rows in 1996");
    }
}

Here's the sample output for this code:

152 rows in 1996