SQL Query not Retrieving All Rows in C# Application

Question

I have a stored procedure created in my SQL Server 2012 database that selects data from multiple tables. In C#, I use this procedure to show data in a datagridview.

Issue: when I execute the query in SQL Server, I get the correct result which returns 3 rows, but in C#, it returns only 2 rows.

Query:

SELECT DISTINCT 
    Employee.Employee_No AS 'Badge'
    ,Employee.Employee_Name_Ar AS 'Emp Name'
    ,Employee.Basic_Salary AS 'Basic'
    ,Employee.Current_Salary AS 'Current'
    ,Attendance.Present
    ,Attendance.Leave
    ,Attendance.Othe_Leave AS 'OL'
    ,Pay_Slip.Salary_Amount AS 'Sal. Amt.'
    ,(ISNULL(Pay_Slip.OverTime1_Amount, 0.00) + ISNULL(Pay_Slip.OverTime2_Amount, 0.00)) AS 'O/T Amt.'
    ,(ISNULL(Pay_Slip.Salary_Amount, 0.00) + ISNULL(ISNULL(Pay_Slip.OverTime1_Amount, 0.00) + ISNULL(Pay_Slip.OverTime2_Amount, 0.00), 0.00)) AS 'Sal. & O/T'
    ,Pay_Slip.Trasnport AS 'Allow'
    ,Pay_Slip.CostofLiving AS 'O.Allow'
    ,Pay_Slip.Gross_Salary AS 'T Salary'
    ,Pay_Slip.Insurance1_Amount AS 'ss 7%'
    ,Pay_Slip.Insurance2_Amount AS 'ss 11%'
    ,(ISNULL(Pay_Slip.Insurance1_Amount, 0.00) + ISNULL(Pay_Slip.Insurance2_Amount, 0.00)) AS 'Total s.s'
    ,Pay_Slip.Tax
    ,Pay_Slip.Personal_Loans AS 'Advance'
    ,Pay_Slip.Other_Deductions AS 'Ded.'
    ,Pay_Slip.Net_Salary AS 'Net'
FROM Pay_Slip
LEFT JOIN Employee ON Pay_Slip.Employee_No = Employee.Employee_No
LEFT JOIN Attendance ON Pay_Slip.Employee_No = Attendance.Employee_No
WHERE Pay_Slip.Month = '5'
    AND Pay_Slip.Year = '2020'
    AND Attendance.Month = '5'
    AND Attendance.Year = '2020'

Executing this query in SQL Server returns 3 rows which are the employee slips on May-2020 (They all have values in May-2020).

C# code:

private void dateTimePicker_ReportDate_ValueChanged(object sender, EventArgs e)
{
    try
    {
        DateTime date = dateTimePicker_ReportDate.Value;

        String Month = dateTimePicker_ReportDate.Value.ToString("MM");
        String Year = dateTimePicker_ReportDate.Value.ToString("yyyy");

        String str = "server=localhost;database=EasyManagementSystem;User Id=Jaz;Password=Jaz@2020;Integrated Security=True;";

        String query = "Execute EMP_PAY_ATT_Selection @Month, @Year";

        SqlConnection con = null;
        con = new SqlConnection(str);

        SqlCommand cmd= new SqlCommand(query, con);

        cmd.Parameters.Add("@Month", SqlDbType.Int).Value = Convert.ToInt32(Month);
        cmd.Parameters.Add("@Year", SqlDbType.Int).Value = Convert.ToInt32(Year);

        SqlDataReader sdr;

        con.Open();

        sdr = cmd.ExecuteReader();

        if (sdr.Read())
        {
            DataTable dt = new DataTable();
            dt.Load(sdr);
            dataGridView_Report.DataSource = dt;

            dataGridView_Report.EnableHeadersVisualStyles = false;
            dataGridView_Report.ColumnHeadersDefaultCellStyle.BackColor = Color.LightBlue;
        }
        else
        {
            dataGridView_Report.DataSource = null;
            dataGridView_Report.Rows.Clear();
        }
        con.Close();
    }
    catch (Exception es)
    {
        MessageBox.Show(es.Message);
    }
}

Again, when running this, it only returns 2 rows on the datagridview. While it should be 3 rows.

These are the tables:

Answer 1

The DbDataReader.Read method advances the reader to the next record.

There is no way to rewind a data reader. Any methods that you pass it to will have to use it from whatever record it is currently on.

If you want to pass the reader to DataTable.Load(), do not Read from it yourself. If you merely want to know if it contains records, use HasRows.