ADO.NET 3.5 Cookbook, 2nd Edition

3.10. Testing Whether a Query Returns an Empty Result Set

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.

Using both nonempty and empty result sets, the solution creates and fills a DataTable and uses the Count property of the DataRowCollection to determine whether 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 in Program.cs in the project TestQueryReturnEmptyResultSet is shown in Example 3-13.

Example 3-13. File: Program.cs for TestQueryReturnEmptyResultSet solution

using System; using System.Data; using System.Data.SqlClient; namespace TestQueryReturnEmptyResultSet { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM Person.Contact"; string sqlSelectEmpty = "SELECT * FROM Person.Contact WHERE ContactID = 0"; Console.WriteLine("---QUERY RETURNS NON-EMPTY RESULT SET---"); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable( ); da.Fill(dt); Console.WriteLine("DataTable has records = {0}", dt.Rows.Count > 0); using (SqlConnection connection = new SqlConnection(sqlConnectString)) ...