9.10. Retrieving a Single Value from a Query
Problem
Given a stored procedure that returns a single value, you need the fastest way to get this data.
Solution
Use the
ExecuteScalar( )
method
to return a single value from a stored procedure.
The sample code uses the ExecuteScalar( )
method
to get the number of records in the Orders table of the Northwind
database.
The C# code is shown in Example 9-13.
Example 9-13. File: ExecuteScalarForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Data.SqlClient; // . . . String sqlText = "SELECT COUNT(*) FROM Orders"; // Create the connection and the command. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new SqlCommand(sqlText, conn); conn.Open( ); // Execute the scalar SQL statement and store results. int count = Convert.ToInt32(cmd.ExecuteScalar( )); conn.Close( ); resultTextBox.Text="Count of Orders records: " + count;
Discussion
The ExecuteScalar( )
method of the
Command
object returns a single value from the
data source rather than a table or data stream. While the
ExecuteScalar( )
method does not result in a
performance improvement when compared to retrieving a single value
using an output parameter or using a DataReader
,
it allows a single value to be returned with the least code and may
therefore improve readability and maintainability.
If the result set returns more than one result, the first column of the first row is returned ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.