Database Reference
In-Depth Information
9.
When the form loads, click the button Count Rows. The result should look like
Figure 13-4.
Figure 13-4.
Executing a scalar command
How It Works
All you do is add a call to
ExecuteScalar()
within a call to the TextBox's
AppendText
method:
txtScalar.AppendText("Number of Product is :");
// Execute Scalar query with ExecuteScalar method
txtScalar.AppendText(cmd.ExecuteScalar().ToString());
ExecuteScalar()
takes the
CommandText
property and sends it to the database using the command's
Connection
property. It returns the result as a single object, which you display with the TextBox's
AppendText
method.
ExecuteScalar()
method's return type is
object
, the base class of all types in the .NET Framework,
which makes perfect sense when you remember that a database can hold any type of data. So, if you
want to assign the returned object to a variable of a specific type
(int
, for example), you must cast the
object to the specific type. If the types aren't compatible, the system will generate a runtime error that
indicates an invalid cast.
The following is an example that demonstrates this idea. In it, you store the result from
ExecuteScalar()
in the variable
count
, casting it to the specific type
int
.
int count = (int) cmd.ExecuteScalar();
txtScalar.AppendText ("Number of Products is: "+ count);
If you're sure the type of the result will always be an
int
(a safe bet with
COUNT(*)
), the previous code
is safe. However, if you left the cast to
int
in place and changed the
CommandText
of the command to the
following:
select Name
from Production.Product
where ProductNumber='BA-8327'
then
ExecuteScalar()
would return the string "
Bearing Ball
" instead of an integer, and you'd get this
exception:
Unhandled Exception: System.InvalidCastException:
Specified cast is not valid.
