C# & ADO.NET

SqlCommand.ExecuteScalar example in ASP.NET (C#)

C# & ADO.NET · By the Postback Notes editorial team · Published 2026-10-07

ExecuteScalar returns the first column of the first row of a query, as an object. It is the right tool when the answer is a single value: a count, a total, a flag, a name, a new id. The only care it needs is converting that object correctly.

A small helper

public static class Db
{
    private static string Cs => ConfigurationManager.ConnectionStrings["Db"].ConnectionString;

    public static object Scalar(string sql, params SqlParameter[] parameters)
    {
        using (var con = new SqlConnection(Cs))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddRange(parameters);
            con.Open();
            return cmd.ExecuteScalar();
        }
    }
}

Counting rows

int open = (int)Db.Scalar(
    "SELECT COUNT(*) FROM dbo.Tickets WHERE Status = @s",
    new SqlParameter("@s", SqlDbType.VarChar, 10) { Value = "open" });

lblOpen.Text = open + " open ticket(s)";

COUNT(*) returns int, so the cast is safe. COUNT_BIG(*) would return long.

Is this user name taken?

protected void cvUserName_ServerValidate(object source, ServerValidateEventArgs args)
{
    object found = Db.Scalar(
        "SELECT 1 FROM dbo.Users WHERE UserName = @u",
        new SqlParameter("@u", SqlDbType.NVarChar, 50) { Value = args.Value.Trim() });

    args.IsValid = found == null; // no row: the name is free
}

Used from a CustomValidator, this check runs on the server during validation. It improves the user experience, but two users can still pick the same name at the same moment: a unique index on UserName remains the real guarantee.

Null and DBNull

object v = Db.Scalar("SELECT MAX(Amount) FROM dbo.Payments WHERE CustomerId = @c",
    new SqlParameter("@c", SqlDbType.Int) { Value = customerId });

// MAX over zero rows returns one row containing NULL -> DBNull.Value
decimal? largest = v is DBNull || v == null ? (decimal?)null : (decimal)v;
Query resultExecuteScalar returns
No rowsnull
A row whose first column is NULL (e.g. aggregate over no rows)DBNull.Value
A valueThe value, boxed in its .NET type (int, decimal, string, DateTime…)

Casting DBNull.Value or null to a value type throws. Convert.ToInt32(DBNull.Value) also throws, while Convert.ToInt32(null) returns 0, which can hide a missing row; be explicit.

Returning the id of a new row

int newId = (int)Db.Scalar(
    @"INSERT INTO dbo.Tickets (Subject, Status) OUTPUT INSERTED.TicketId VALUES (@subject, 'open');",
    new SqlParameter("@subject", SqlDbType.NVarChar, 200) { Value = txtSubject.Text.Trim() });

Response.Redirect("Ticket.aspx?id=" + newId, false);
Context.ApplicationInstance.CompleteRequest();

OUTPUT INSERTED.TicketId returns the value with the column's own type (int). If the table has triggers, use SELECT CAST(SCOPE_IDENTITY() AS int) after the insert instead, because OUTPUT without INTO is not allowed on tables with enabled triggers.

When not to use it

If you need two values, return them as two columns and use a reader, or use output parameters; calling ExecuteScalar twice doubles the round trips. And do not run a query returning many rows just to take the first cell: write TOP (1) or an aggregate so the server does less work. The full comparison is in ExecuteReader vs ExecuteNonQuery vs ExecuteScalar.

Official documentation