c# - Display records in database that uses a join with where clause in datagridview

Question

What I am trying to do here is:

1. Display ALL Employees (in the datagridview)
2. Display Employees that HAVE health insurance records (in the datagridview)
3. Display Employees WITHOUT health insurance records(in the datagridview)

I can now display all of my employees to the datagridview(dgvEmp) with this stored procedure:

IF @action_type = 'DisplayAllEmployees'  
BEGIN
    SELECT     e.employee_id, e.employee_name, e.city, e.department, e.gender, 
                       h.health_insurance_provider, h.plan_name, h.monthly_fee, h.insurance_start_date 
    FROM       dbo.Employee e
    LEFT JOIN  dbo.EmployeeHealthInsuranace h ON h.employee_id = e.employee_id
END

and this function (in my winforms):

private void FetchEmpDetails( string readType ) {
    //Load/Read Data from database
    using ( SqlConnection con = new SqlConnection( connectionStringConfig ) )
    using ( SqlCommand sqlCmd = new SqlCommand( "spCRUD_Operations", con ) ) {
        try {
            con.Open();
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue( "@action_type", readType );
            sqlCmd.Connection = con;
            SqlDataAdapter sqlSda = new SqlDataAdapter( sqlCmd );
            sqlSda.Fill( dt );

            dgvEmp.AutoGenerateColumns = false;//if true displays all the records in the database
                    
            dgvEmp.Columns[ 0 ].DataPropertyName = "employee_id";
            dgvEmp.Columns[ 1 ].DataPropertyName = "employee_name";
            dgvEmp.Columns[ 2 ].DataPropertyName = "city";
            dgvEmp.Columns[ 3 ].DataPropertyName = "department";
            dgvEmp.Columns[ 4 ].DataPropertyName = "gender";

            dgvEmp.Columns[ 5 ].DataPropertyName = "health_insurance_provider";
            dgvEmp.Columns[ 6 ].DataPropertyName = "plan_name";
            dgvEmp.Columns[ 7 ].DataPropertyName = "monthly_fee";
            dgvEmp.Columns[ 8 ].DataPropertyName = "insurance_start_date";
            dgvEmp.Columns[ 8 ].DefaultCellStyle.Format = "MMMM dd, yyyy";

            dgvEmp.DataSource = dt;
            } catch ( Exception ex ) {
                    MessageBox.Show( "Error: " + ex.Message );
            }
    }
}

I can display all by calling the function: FetchEmpDetails( "DisplayAllEmployees" );

But, when I try to display Employees that HAVE health insurance records or display Employees WITHOUT health insurance records (with the function call through winforms), I can't get them to display at the dataGridView. THE DATA GRID VIEW IS JUST BLANK.

This is the Stored Procedure:

    ELSE IF @action_type = 'WithHealthInsuranceRecords'  
    BEGIN
            SELECT     e.employee_id, e.employee_name, e.city, e.department, e.gender, 
                       h.health_insurance_provider, h.plan_name, h.monthly_fee, h.insurance_start_date 
            FROM       dbo.Employee e
            INNER JOIN  dbo.EmployeeHealthInsuranace h ON h.employee_id = e.employee_id
            WHERE      h.monthly_fee > 0
    END

    ELSE IF @action_type = 'WithoutHealthInsuranceRecords'  
    BEGIN
            SELECT     e.employee_id, e.employee_name, e.city, e.department, e.gender, 
                       h.health_insurance_provider, h.plan_name, h.monthly_fee, h.insurance_start_date 
            FROM       dbo.Employee e
            LEFT JOIN  dbo.EmployeeHealthInsuranace h ON h.employee_id = e.employee_id
            WHERE      h.monthly_fee = 0
    END

But, If I run this as "New Query" in my server explorer, the expected output shows up:

---

EDIT: Thanks to everyone who commented and posted an answer, those are very helpful and I appreciate it.

When I was taking a break I found the problem, my action_type parameter was @action_type NVARCHAR(25), then I realized that the string that I passed there was > 25. I now changed it to @action_type NVARCHAR(100) and it now displays well!

question from:https://stackoverflow.com/questions/66061155/display-records-in-database-that-uses-a-join-with-where-clause-in-datagridview

Answer 1

Your query is wrong:

WHERE      h.monthly_fee = 0

This will filter the table to rows that are not null, which basically means you are doing an INNER JOIN not a LEFT.

Instead, you can either change it to:

WHERE      h.monthly_fee IS NULL;

Or better, use a NOT EXISTS query:

SELECT     e.employee_id, e.employee_name, e.city, e.department, e.gender, 
           h.health_insurance_provider, h.plan_name, h.monthly_fee, h.insurance_start_date 
FROM       dbo.Employee e
WHERE NOT EXISTS (SELECT 1
     FROM  dbo.EmployeeHealthInsuranace h
     WHERE h.employee_id = e.employee_id);