SqlCommand.ExecuteScalar Method
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
public:
override System::Object ^ ExecuteScalar();public override object ExecuteScalar ();override this.ExecuteScalar : unit -> objPublic Overrides Function ExecuteScalar () As ObjectReturns
The first column of the first row in the result set, or a null reference (
Nothing
in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters.
Exceptions
A SqlDbType other than Binary or VarBinary was used when Value was set to Stream . For more information about streaming, see SqlClient Streaming Support.
-or-
A SqlDbType other than Char, NChar, NVarChar, VarChar, or Xml was used when Value was set to TextReader .
-or-
A SqlDbType other than Xml was used when Value was set to XmlReader .
An exception occurred while executing the command against a locked row. This exception is not generated when you are using Microsoft .NET Framework version 1.0.
-or-
A timeout occurred during a streaming operation. For more information about streaming, see SqlClient Streaming Support.
The SqlConnection closed or dropped during a streaming operation. For more information about streaming, see SqlClient Streaming Support.
An error occurred in a Stream , XmlReader or TextReader object during a streaming operation. For more information about streaming, see SqlClient Streaming Support.
The Stream , XmlReader or TextReader object was closed during a streaming operation. For more information about streaming, see SqlClient Streaming Support.
Examples
The following example creates a SqlCommand and then executes it using ExecuteScalar. The example is passed a string representing a new value to be inserted into a table, and a string to use to connect to the data source. The function returns the new Identity column value if a new row was inserted, 0 on failure.
using System;
using System.Data;
using Microsoft.Data.SqlClient;
public class Sample
{
public void CreateSqlCommand(
string queryString, SqlConnection connection)
{
SqlCommand command = new
SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteScalar();
connection.Close();
}
Remarks
Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.
A typical ExecuteScalar query can be formatted as in the following C# example:
cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
Int32 count = (Int32) cmd.ExecuteScalar();