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.

How I get the number of rows in a table using C#?

Here is a step by step:

Step 1 – Create a new class: (attached click here: SQLTableRowCounter.cs)

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

namespace SQLTableRowCounter
{
    class SQLTableRowCounter
    {
        private string mCountQuery;
        private SqlConnection mConnection;
        private int mNumberOfRows;

        public SQLTableRowCounter(String inTableName, SqlConnection inConnection)
        {
            mCountQuery = "SELECT COUNT(*) FROM " + inTableName;
            mConnection = inConnection;
            mConnection.Open();
            SqlCommand mCountQueryCommand = new SqlCommand(mCountQuery, mConnection);
            mNumberOfRows = (int)mCountQueryCommand.ExecuteScalar();
        }

        public int NumberOfRows
        {
            get { return mNumberOfRows; }
            set { mNumberOfRows = value; }
        }
    }
}

Step 2 – Now create the object and get the value:

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

namespace SQLTableRowCounter
{
    class Program
    {
        static void Main(string[] args)
        {
             string connectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";
             SqlConnection connection = new SqlConnection(connectionString);
             SQLTableRowCounter qrc = new SQLTableRowCounter("TableName", connection);
             int numRows = qrc.NumberOfRows;
        }
    }
}