Fast Answers

When you need to get data from your database, you naturally start by coding a SELECT statement to return one or more rows—a rowset—containing the answer to your question. Sometimes you want this kind of comprehensive information; other times you simply want a yes or no answer. When you ask, "Are seats available on the next flight to Cleveland?" you're not asking for a list of those seats—only whether seats are obtainable. In some cases, you might want to return a number from a query. For example, you might want to count the number of open seats on that plane to Cleveland because you're trying to book a whole soccer team on the flight. Again, such an answer doesn't require SQL Server to return a rowset. Of course, these queries can't rationalize the wisdom of going to Cleveland in the first place—that would require a system far more sophisticated than any known database management system (DBMS).

So, when you want to return information from SQL Server, you can use a SQL query to return an OUTPUT parameter or RETURN integer—or you can capture the RowsAffected value by using an action command. Action commands are simply SQL statements that change the database—UPDATE, INSERT, and DELETE statements are examples of action commands. These SQL statements alter the database rows (without generating a rowset) and return a RowsAffected value that tells you how many rows were changed, added, or deleted. You can use this value to verify that the right number of rows were changed. Some developers suppress returning the RowsAffected value by using SET NOCOUNT ON. Suppressing the value improves performance—slightly—and makes managing complex queries easier, but if you turn off the value, you have to determine on your own whether the action command did what you asked.

Regardless of the data-access interface you use, you'll improve application performance if you avoid rowset construction whenever possible. In COM-based ADO, returning rowsets is especially expensive, and in ADO.NET, rowsets slow performance just as much as in ADO. To avoid creating rowsets, don't use the SELECT statement in your SQL query—even to return a scalar value—because SELECT generates a rowset. Instead, call a stored procedure that returns an OUTPUT or ReturnValue parameter, as the following examples show.

Two Efficient Methods

To execute an ADO.NET action command and return a result set that doesn't include a rowset, you have several alternatives—not all of which are efficient. In ADO.NET, all solutions use one of the Command objects as implemented by ADO.NET's .NET Data Providers (e.g., SqlCommand, OleDbCommand, OdbcCommand, OracleCommand). The following two methods are efficient as long as you use them correctly.

The ExecuteNonQuery method. The ExecuteNonQuery method is designed specifically for action commands, so it returns a rows-affected count from the action command's @@RowsAffected value. Using ExecuteNonQuery is your best choice when you're working with action commands because this method can also return OUTPUT and ReturnValue parameters. Thus, the .NET provider doesn't have to construct a rowset on the server end or process the rowset on the client end, and the ADO.NET Parameter objects that contain the returned values are available immediately after the query is executed.

The ExecuteScalar method. This method executes the query and returns the first column of the first row of the rowset (if the query succeeds). Notice that the ExecuteScalar method returns a scalar object, which can be anything—a string, a number, a float, or any other kind of object. This approach assumes that the SQL query contains a SELECT statement. Because ADO.NET accepts only the first column of the first row, it makes sense to limit the rowset to one value as you do when you use SELECT to return a server variable. But because you're still using a SELECT statement in this case, returning a scalar object isn't as efficient as returning OUTPUT or ReturnValue parameters.

Let's look at some code that demonstrates both of these methods. The code in Listing 1 uses the ExecuteScalar method to execute a stored procedure (which Listing 2 shows) that adds a new row to the Northwind Orders table and returns a scalar object (an integer) containing the new OrderID that SQL Server generates.

The code that Listing 3 shows uses the ExecuteNonQuery method of the SqlCommand object to return the RowsAffected value from the stored procedure in Listing 4 and the remaining returned parameters. Notice that Listing 3 shows two ways to return values from the stored procedure: using an OUTPUT parameter and using a ReturnValue parameter. You can use these parameters to return new row identity values, scalar objects (numbers or strings), or any information you're interested in. Remember: OUTPUT parameter management is far faster than returning a rowset.

If you avoid executing queries that contain SELECT statements when your question has a numerical or yes-or-no answer, your application will run faster and scale better.

You don't need to use a SELECT statement to find out whether seats are still available on that flight to Cleveland—unless you're trying to get a list of the people in the seats nearby. Perhaps Drew Carey is in one of them...