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:

sdr.Read() consumes one row and advances the reader. Do not call it if you want to pass the reader to dt.Load. — GSerg
– GSerg, Commented May 3, 2020 at 7:00
I see. Never knew that. how about sdr.HasRows()? I mean, I want the if statement to check for returned values. — Jaz
– Jaz, Commented May 3, 2020 at 7:02
Just replaced sdr.Read() with sdr.HasRows and it solved the problem. Many Thanks — Jaz
– Jaz, Commented May 3, 2020 at 7:05

GSerg · Accepted Answer · 2020-05-03 07:13:41Z

2

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.

answered May 3, 2020 at 7:13

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query not Retrieving All Rows in C# Application

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related