Database Reference
In-Depth Information
Executing Commands
Commands aren't much use unless you can execute them, so let's look at that now. Commands have
several different methods for executing SQL. The differences between these methods depend on the
results you expect from the SQL. Queries return rows of data
(result sets
),
but the
INSERT
,
UPDATE
, and
DELETE
statements don't. You determine which method to use by considering what you expect to be
returned (see Table 13-1).
Table 13-1.
Command Execution Methods
If the Command Is Going to Return ...
You Should Use ...
ExecuteNonOuery
Nothing (it isn't a query)
ExecuteReader
ExecuteScalar
Zero or more rows
Only one value
The SQL you just used in the example should return one value, the number of employees. Looking
at Table 13-1, you can see that you should use the
ExecuteScalar()
method of
SqlCommand
to return this
one result. Let's try it.
Executing Commands with a Scalar Query
ExecuteScalar
is the method that is used to execute those SQL statements that consist of scalar
functions. Scalar functions are functions that return only one value from an entire set of rows in a table.
For example,
Min( )
,
Max( )
,
Sum( )
,
Count( )
, and so on, are a few examples of scalar functions. If you
execute a query such as
Select Min(Salary)
from Employee, then no matter how many rows you have in
the table, only one row will be returned. Now let's see how the
ExecuteScalar( )
method works with
such a SQL query.
Try It: Using the ExecuteScalar Method
To use the
ExecuteScalar
method, follow these steps:
1.
Select the ADO.NET_Command project, right-click and choose Add Windows
Form. From the opened dialog, make sure Windows Form is selected, and
rename
Form1.cs
to
CommandScalar.cs
. Click OK to add this form to the
ADO.NET_Command project.
2.
Select the CommandScalar form by clicking the form's title bar, and set the
Size property's Width to 385 and Height to 126.
3.
Drag a Label control to the form and position it toward the left side of the
form. Select this Label control, navigate to the Properties window, and set the
following properties:
•
Set the Name property to lblRowCount.
•
Set the Location property's X to 4 and Y to 35.
