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 as a scalar value. A null reference is returned if the result set is empty or if the result set is a Ref Cursor when using the Oracle .NET data provider.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net