DAL: ExecuteScalar

Posted Sat, Sep 25 2010 12:54 by Deborah Kurata

If you want to retrieve one field from a table, you can use the ExecuteScalar method. This method executes a sql statement or stored procedure and returns a single value.

Be sure to import the namespace: System.Data.SqlClient

In C#:

decimal amt;
// Open the connection
using (SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=AcmeRentals;Integrated Security=True"))
{
    cnn.Open();

    // Define the command
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = cnn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Select TotalAmount From Invoice Where InvoiceID = 1";

        // Execute the command
        amt = (decimal)cmd.ExecuteScalar();
    }

}
Console.WriteLine(amt);

In VB:

Dim amt As Decimal
' Open the connection
Using cnn As New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=AcmeRentals;Integrated Security=True")
    cnn.Open()

    ' Define the command
    Using cmd As New SqlCommand()
        cmd.Connection = cnn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "Select TotalAmount From Invoice Where InvoiceID = 1"

        ' Execute the command
        amt = CDec(cmd.ExecuteScalar())
    End Using
End Using
Debug.WriteLine(amt)

Be sure to replace the name of my database ("AcmeRentals") with the name of your database and replace the name of my SQL server (".\SQLExpress") with the name of your SQL server.

Enjoy!

Filed under: , , , ,

Comments

# re: DAL: ExecuteScalar

Wednesday, October 13, 2010 7:16 AM by Mark Stega

Nice post that came very coincidentally a day before I needed this functionality.  The only thing I suggest is changing the declaration of amt to 'decimal? amt'  and the cast on the execute to '(decimal?') since a null can be returned (and whatever is equivalent in VB).

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: