Retrieving a Single Value from a Query

[ Team LiB ] Recipe 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( )
Nội dung trích xuất từ tài liệu:
Retrieving a Single Value from a Query [ Team LiB ]Recipe 9.10 Retrieving a Single Value from a QueryProblemGiven a stored procedure that returns a single value, you need the fastest way to get thisdata.SolutionUse 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 theOrders table of the Northwind database.The C# code is shown in Example 9-13.Example 9-13. File: ExecuteScalarForm.cs// Namespaces, variables, and constantsusing 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;DiscussionThe ExecuteScalar( ) method of the Command object returns a single value from the datasource rather than a table or data stream. While the ExecuteScalar( ) method does notresult in a performance improvement when compared to retrieving a single value using anoutput parameter or using a DataReader, it allows a single value to be returned with theleast 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 returnedas a scalar value. A null reference is returned if the result set is empty or if the result setis a Ref Cursor when using the Oracle .NET data provider.[ Team LiB ]