SqlCommand.ExecuteScalar Method
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.
Namespace: System.Data.SqlClient
Assembly: System.Data (in System.Data.dll)
Return Value
Type: System.ObjectThe 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.
Implements
IDbCommand.ExecuteScalar()| Exception | Condition |
|---|---|
| InvalidCastException | A SqlDbType other than Binary or VarBinary was used when Value was set to Stream. For more information about streaming, see SqlClient Streaming Support. A SqlDbType other than Char, NChar, NVarChar, VarChar, or Xml was used when Value was set to TextReader. A SqlDbType other than Xml was used when Value was set to XmlReader. |
| SqlException | 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. A timeout occurred during a streaming operation. For more information about streaming, see SqlClient Streaming Support. |
| InvalidOperationException | The SqlConnection closed or dropped during a streaming operation. For more information about streaming, see SqlClient Streaming Support. |
| IOException | An error occurred in a Stream, XmlReader or TextReader object during a streaming operation. For more information about streaming, see SqlClient Streaming Support. |
| ObjectDisposedException | The Stream, XmlReader or TextReader object was closed during a streaming operation. For more information about streaming, see SqlClient Streaming Support. |
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();
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.
static public int AddProductCategory(string newName, string connString) { Int32 newProdID = 0; string sql = "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " + "SELECT CAST(scope_identity() AS int)"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@Name", SqlDbType.VarChar); cmd.Parameters["@name"].Value = newName; try { conn.Open(); newProdID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return (int)newProdID; }
Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)
The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.
