SqlDataReader is not returning data, but SQL Server Profiler works – C# Issue

Question

I'm trying to retrieve data from SQL Server using C#, but I can't get the SqlDataReader to return any results. However, the same query works fine in SQL Server Profiler, returning the expected data. I'm not sure why SqlDataReader isn't working, even though the query itself seems fine.

using System;
using System.Data.SqlClient;

public class Program
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT top(10) * FROM myTable and many joins...";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                Console.WriteLine(reader[0].ToString());
            }

            reader.Close();
        }
    }
}

The code runs, but no data is returned. When I run the same query in SQL Server Profiler, I can see the results being returned.

Things I've tried:

• I verified that the query is correct
• I confirmed that the database connection is working and established
• I tried using ExecuteScalar and ExecuteNonQuery instead of ExecuteReader, but it didn't help

I see the query works fine in SQL Server Profiler and returns the expected data, but in the C# code, no data is returned.

Answer 1

I had faced like this problem, and after removing top(n), the issue was resolved.