10.5. Counting Records
Problem
You want to determine how many rows that meet certain criteria are in a table.
Solution
Use the ExecuteScalar( )
method of the
Command
object to determine the number of records
in the table.
The sample code executes the COUNT
function on the
results of a query returning rows from the Orders table in the
Northwind sample database, where the rows match a user-specified
CustomerID.
The C# code is shown in Example 10-5.
Example 10-5. File: CountRecordForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // . . . // Create the connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); // Build the query to count, including CustomerID criteria if specified. String selectText = "SELECT COUNT(*) FROM Orders"; if(customerIdTextBox.Text.Trim( ) != "") selectText += " WHERE CustomerId='" + customerIdTextBox.Text + "'"; // Create the command to count the records. SqlCommand cmd = new SqlCommand(selectText, conn); // Execute the command, storing the results. conn.Open( ); int recordCount = (int)cmd.ExecuteScalar( ); conn.Close( );
Discussion
The ExecuteScalar( )
method of the
Command
object returns a single value from a query
rather than a table or a data stream. If the query returns a result
set, this method returns the value of the first column of the first
row.
The number of records matching certain criteria can be determined by executing ...
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.