How do I get the number of rows returned from a SQL Query in C#?

Having used other languages where this is much simpler, I was surprised at how “not simple” this was in C#. I expected it to be a little more complex than in some scripting language such as PHP, but it was way more complex.

Here is how I do it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CountRows
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a string to hold the database connection string
            string sdwConnectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";

            // Create a string to hold the database connection string
            string query = "SELECT * FROM MyTable";

            // Pass both strings to a new SqlCommand object.
            SqlCommand queryCommand = new SqlCommand(query, sdwDBConnection);

            // Create a SqlDataReader
            SqlDataReader queryCommandReader = queryCommand.ExecuteReader();

            // Create a DataTable object to hold all the data returned by the query.
            DataTable dataTable = new DataTable();
            dataTable.Load(queryCommandReader);

            // The DataTable object has a nice DataTable.Rows.Count property that returns the row count.
            int rowCount = rowCount = dataTable.Rows.Count;
        }
    }
}

Now doing it this way, you also have the data available in the DataTable dataTable object so you don’t have to go to the database and get it again.