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 SqlCommand("SELECT * FROM ORDERS", conn);
conn.Open( );
SqlDataReader orderReader = cmd.ExecuteReader( );

// Test Orders DataReader for records.
result.Append("DataReader " + ORDERS_TABLE + ": Has records = " +
 orderReader.HasRows + Environment.NewLine);

// Test Orders DataReader for records.
bool readerHasRecords = orderReader.Read( );
result.Append("DataReader " + ORDERS_TABLE + ": Has records = " +
 readerHasRecords + Environment.NewLine);

orderReader.Close( );
conn.Close( );

resultTextBox.Text = result.ToString( );

Discussion

The DataTable contains a DataRowCollection object that contains all DataRow objects in the table. The DataRowCollection has a Count property that returns the number of rows in the table. The Count property for an empty table has a value of 0.

The HasRows property of the DataReader returns a Boolean value indicating whether the DataReader has any records.

Another way is to use the Read( ) method to advance the DataReader to the next record. This returns a value of true if a record is available and false otherwise . The first call to the Read( ) method will indicate whether any records were returned by the DataReader . This was the only way to determine whether the DataReader contained any records prior to the introduction of the HasRows property in .NET Framework 1.1.