Obtaining a Single Value from a Database
You may need to return database information that is simply a single value rather than in the form of a table or data stream. For example, you may want to return the result of an aggregate function such as COUNT(*), SUM(Price), or AVG(Quantity). The Command object provides the capability to return single values using the ExecuteScalar method. The ExecuteScalar method returns as a scalar value, the value of the first column of the first row of the result set.
The following code example returns the number of records in a table using the ExecuteScalar method of a SqlCommand object. The SELECT statement uses the Transact-SQL COUNT aggregate function to return a single value representing the number of rows in the specified table.
' Assumes that connection is a valid SqlConnection object.
Dim ordersCMD As SqlCommand = New SqlCommand( _
"SELECT COUNT(*) FROM dbo.Orders", connection)
Dim count As Int32 = CInt(ordersCMD.ExecuteScalar())
// Assumes that connection is a valid SqlConnection object.
SqlCommand ordersCMD = new SqlCommand(
"SELECT Count(*) FROM Orders", connection);
Int32 count = (Int32)ordersCMD.ExecuteScalar();