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:
virtual System::Object ^ ExecuteScalar();public override object ExecuteScalar ();public object ExecuteScalar ();override this.ExecuteScalar : unit -> objabstract member ExecuteScalar : unit -> obj
override this.ExecuteScalar : unit -> objPublic Overrides Function ExecuteScalar () As ObjectPublic 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.
Implements
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.
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;
}
Public Function AddProductCategory( _
ByVal newName As String, ByVal connString As String) As Integer
Dim newProdID As Int32 = 0
Dim sql As String = _
"INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " _
& "SELECT CAST(scope_identity() AS int);"
Using conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.Add("@Name", SqlDbType.VarChar)
cmd.Parameters("@Name").Value = newName
Try
conn.Open()
newProdID = Convert.ToInt32(cmd.ExecuteScalar())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
Return newProdID
End Function
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();
