SqlCommand.ExecuteScalar Method

Definition

Namespace:

Microsoft.Data.SqlClient

Assembly:

Microsoft.Data.SqlClient.dll

Package:

Microsoft.Data.SqlClient v5.2.0

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 -> obj

Public Overrides Function ExecuteScalar () As Object

Returns

Object

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

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.

-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 .

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.

-or-

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.

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();