ADO.NET Cookbook

2.11. Testing for No Records

Problem

You need to determine whether any records were returned from a query that you just executed.

Solution

Use the DataRowCollection.Count property, the DataReader.HasRows property, or the DataReader.Read( ) method.

The sample code creates and fills a DataTable and uses the Count property of the DataRowCollection to determine if the query used to create the table returned any rows. Next, a DataReader is created and both the HasRows property and the Read( ) method are used to determine whether the query used to create the DataReader returned any rows.

The C# code is shown in Example 2-13.

Example 2-13. File: NoRecordTestForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; // Table name constants private const String ORDERS_TABLE = "Orders"; // . . . StringBuilder result = new StringBuilder( ); // Fill the Orders DataTable. SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); DataTable orderTable = new DataTable(ORDERS_TABLE); da.Fill(orderTable); // Test Orders DataTable for records. bool tableHasRecords = orderTable.Rows.Count > 0; result.Append("DataTable " + ORDERS_TABLE + ": Has records = " + tableHasRecords + Environment.NewLine); // Create the Orders DataReader. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new ...