How do I time the execution of an SQL Query in C#?

Question

I'm trying to just find out the execution time of my SQL Server query using c#. I thought a timer would work; however I'm new to c# and have been having trouble figuring it out. I've searched through several questions on this site, and on other sites trying to find out how to time the execution of my query.

Here is my code:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        Console.WriteLine("Executing query...");
        string customerID = "ID_HERE";
        using (SqlConnection connection = new SqlConnection("CONNECTION_STRING"))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(
                "SELECT col0, col1, col2, col3, col4, col5, col6, col7, col8, col9 FROM Table_name WHERE col1 LIKE @ID", connection))
            {

                command.Parameters.Add(new SqlParameter("ID", customerID));

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int col0 = reader.GetInt32(0);
                    int col1 = reader.GetInt32(1);
                    string col2 = reader.GetString(2);
                    string col3 = reader.GetString(3);
                    int col4 = reader.GetInt32(4);
                    int col5 = reader.GetInt32(5);
                    short col6 = reader.GetInt16(6);
                    string col7 = reader.GetString(7);
                    string col8 = reader.GetString(8);
                    int col9 = reader.GetInt32(9);
                    Console.WriteLine("col0 = {0}, col1 = {1}, col2 = {2}, col3 = {3}, col4 = {4}, col5 = {5}, col6 = {6}, col7 = {7}, col8 = {8}, col9 = {9}",
                        col0,
                        col1,
                        col2,
                        col3,
                        col4,
                        col5,
                        col6,
                        col7,
                        col8,
                        col9
                        );
                }
            }
        }
        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }

}

I'm not sure how to add a timer to this that only times the execution of the query, or even where to put it. I've tried finding other posts about it but none of the ones I've found are similar to this; I could also just be really bad at my searches. Any kind of help would be appreciated, and if you need any more information let me know. I renamed a few things for security, but this is how the code is otherwise. To note: I'm using this for testing purposes, and not for production, so only a few people will ever actually see this but it's necessary.

Answer 1

The SQL Query starts executing at SqlCommand.ExecuteReader(), and it's finished executing after the SqlDataReader.Read() returns false. Note that if the SQL Server is across a slow network or there are a large number of results, this won't accurately measure the time spent waiting on the SQL Server.

So

using System;
using System.Data.SqlClient;
using System.Diagnostics;

class Program
{
    static void Main()
    {
        Console.WriteLine("Executing query...");
        string customerID = "ID_HERE";
        using (SqlConnection connection = new SqlConnection("CONNECTION_STRING"))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(
                "SELECT col0, col1, col2, col3, col4, col5, col6, col7, col8, col9 FROM Table_name WHERE col1 LIKE @ID", connection))
            {

                command.Parameters.Add(new SqlParameter("ID", customerID));
                var sw = new Stopwatch();

                sw.Start();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int col0 = reader.GetInt32(0);
                    int col1 = reader.GetInt32(1);
                    string col2 = reader.GetString(2);
                    string col3 = reader.GetString(3);
                    int col4 = reader.GetInt32(4);
                    int col5 = reader.GetInt32(5);
                    short col6 = reader.GetInt16(6);
                    string col7 = reader.GetString(7);
                    string col8 = reader.GetString(8);
                    int col9 = reader.GetInt32(9);
                    Console.WriteLine("col0 = {0}, col1 = {1}, col2 = {2}, col3 = {3}, col4 = {4}, col5 = {5}, col6 = {6}, col7 = {7}, col8 = {8}, col9 = {9}",
                        col0,
                        col1,
                        col2,
                        col3,
                        col4,
                        col5,
                        col6,
                        col7,
                        col8,
                        col9
                        );
                }
                var elapsed = sw.Elapsed;

                Console.WriteLine($"Query Executed and Results Returned in {elapsed.Seconds}sec");
            }
        }
        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }

}