Populate ComboBox (3 Tier Architecture)

Question

I'm quite new to 3-Tier Architecture. I'm practicing myself to create a WinForms app using 3-Tier Architecture.

I have an old project I need to convert that to 3-Tier Architecture. I have no problem with the Registration, Login, Populating the Datagridview, Selecting Data.

My problem is how can I populate my ComboBox.

void FrmLoginLoad(object sender, EventArgs e)
{
    Dropdowns.getRole(cmbUserRole);
}

Dropdown Class

public static void getRole (ComboBox dd)
{
    SQLHelper sqlConnect = new SQLHelper();
    sqlConnect.DBConnection();
    try
    {
        if (sqlConnect.con.State == ConnectionState.Closed) {
            sqlConnect.con.Open();
        }
            
        SqlCommand cmd = new SqlCommand("SELECT Role FROM tbl_IT_RoleDescription",sqlConnect.con);
            
        using (SqlDataReader dr = cmd.ExecuteReader()){
            if (dr.HasRows) {
                while (dr.Read()) {
                    dd.Items.Add(dr["Role"].ToString());
                }
            } 
            dr.Close();
        }
        sqlConnect.con.Close();
        dd.SelectedIndex = 0;
    }
    catch(Exception ex)
    {
        MessageBox.Show("Error : " + ex.Message + "\n\nSend this issue to EUC Dev Team?", "Intake Tool", MessageBoxButtons.YesNo, MessageBoxIcon.Error);
            sqlConnect.con.Close();
    }   
}

I tried to convert to 3-Tier Architecture, PL

private void frmLogin_Load(object sender, EventArgs e)
{
    BLL_UserRole.getRole(cmbUserRole);
}

BLL

public void getRole(ComboBox cmbUserRole)
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT Role FROM tbl_IT_RoleDescription";
    db.exeReader(cmd);
}

DAL

public DataTable exeReader(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    try
    {
        cmd.Connection = getCon();
        SqlDataReader dr;

        dr = cmd.ExecuteReader();
        dt.Load(dr);
        con.Close();

    }
    catch (Exception ex)
    {
        MessageBox.Show("Error : " + ex.Message + "\n\nSend this issue to EUC Dev Team?", "Intake Tool", MessageBoxButtons.YesNo, MessageBoxIcon.Error);
            con.Close();
    }
    return dt;
}

I tried to research this, All of the results are hardcoded or they just insert data in the collection properties of ComboBox. But I want is the data of ComboBox is coming from DB.

Answer 1

I think I have a solution for you. It will great to return the DataTable object to PL Layer. However, if you don't want to do that then you can check my dummy code and try it. You can also check that similar problem link LINK

public void getRole(ComboBox cmbUserRole)
{
            DataSet myDataSet = new DataSet();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Role FROM tbl_IT_RoleDescription";
            var temp=db.exeReader(cmd);
            myDataSet.Tables.Add(temp);
            cmbUserRole.DataSource = myDataSet.Tables["tbl_IT_RoleDescription"].DefaultView;
            cmbUserRole.DisplayMember = "Role";
}

Note: Please check the link and let me know it works or not.