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.