none
Working With CheckListBox And Loading User Information Using EF RRS feed

  • Question

  • Hello,

    Please i am a little bit stuck at loading user information in windows form using EF. The part where i got stuck is loading the list of all departments available in the department table and checking the user_detail table to check the department a user belongs to.

    A user can belong to one or more department. So, its a many to many relationship. But the junction table is user_detail which holds the user_id and depatment_id (user with related department)

    I need to load this information unto a checklistbox.

    Here is the code i have so far.

     private void LoadAllDeptWithRelatedDept(user user)
            {
                //load all available department from [department table]
                //and also check or mark the box which the user
                //belong to from the [user_details table]
               
                using (MyTestEntities db = new MyTestEntities())
                {
                    var result =
                        from ud in db.user_detail
                        join d in db.departments on ud.department_id equals d.department_id
                        join u in db.users on ud.user_id equals u.user_id
                        where ud.user_id == user.user_id
                        select ud;
                    foreach (var item in result)
                    {
                        checkedListBox1.Items.Add(new UserDeptDto()
                        {
                            Id = item.department_id,
                            DepartmentName = item.department.department_name                     
    
                        });
    
                        //TODO: set the chkListbox
                        int index = checkedListBox1.Items.IndexOf(item);
    
                        if (index >= 0)
                        {
                            checkedListBox1.SetItemChecked(index, true);
                        }
    
                    }
    
                    
                }
     private void UpdateUser(user user)
            {
    
                user.firstname = txtFirstname.Text.Trim();
                user.lastname = txtLastname.Text.Trim();
                user.username = txtUsername.Text.Trim();
                user.password = txtPassword.Text.Trim();
    
                //update code for also checklistbox
    
                using (MyTestEntities db = new MyTestEntities())
                {
                    db.Entry(user).State = EntityState.Modified;
                    db.SaveChanges();
                    MessageBox.Show("User Updated Successfully!", "Message");
                    Close();
                }
                RefreshData();
            }

    I can successfully get all department which a user belong to. But the idea is 

    1. Load all available department in the [department table] to the checklist box

    2. Now check/tick the department name which the user belong to when loading all depts

    3. Then update this info

    I have created a sample project to replicate this issue

    Link: https://1drv.ms/f/s!Aq-hLZmB9gUFh_IkC_EKnR_bDjGzKg

    Appreciate any help. 

    Friday, November 23, 2018 2:14 AM

Answers

  • Hi Godymn,

    Please check the following code.

    public partial class UserAccount : Form
        {
            private readonly user _user;
            private MyTestEntities db = new MyTestEntities();
            public UserAccount(user user)
            {
                InitializeComponent();
    
                LoadDeptToControl();
                _user = user;
                LoadUserObjectToControl(_user);
            }
    
            
    
            private void LoadUserObjectToControl(user user)
            {
                txtFirstname.Text = user.firstname;
                txtLastname.Text = user.lastname;
                txtEmail.Text = user.email;
                txtUsername.Text = user.username;
                txtPassword.Text = user.password;
    
                LoadAllDeptWithRelatedDept(user);
            }
    
            
            private void LoadAllDeptWithRelatedDept(user user)
            {
                //load all available department from [department table]
                //and also check or mark the box which the user
                //belong to from the [user_details table]
               
                using (MyTestEntities db = new MyTestEntities())
                {
                    var result =
                        from ud in db.user_detail
                        join d in db.departments on ud.department_id equals d.department_id
                        join u in db.users on ud.user_id equals u.user_id
                        where ud.user_id == user.user_id
                        select ud;
    
                    foreach (var item in result)
                    {
    
                        for (int i = 0; i < checkedListBox1.Items.Count; i++)
                        {
                            UserDeptDto deptDto = (UserDeptDto)checkedListBox1.Items[i];
    
                            if (deptDto.DepartmentName == item.department.department_name && deptDto.Id == item.department.department_id)
                            {
                                checkedListBox1.SetItemChecked(i, true);
                            }
                        }
                    }
                }
    
            }
    
            private void btnUpdate_Click(object sender, EventArgs e)
            {
                UpdateUser(_user);
            }
            private void UpdateUser(user user)
            {
    
                var newUser = db.users.Find(user.user_id);
    
                newUser.firstname = txtFirstname.Text.Trim();
                newUser.lastname = txtLastname.Text.Trim();
                newUser.username = txtUsername.Text.Trim();
                newUser.password = txtPassword.Text.Trim();
    
                
    
                if(newUser.user_detail != null)
                {
                    newUser.user_detail.Clear();
                }
    
                foreach (var item in checkedListBox1.CheckedItems)
                {
                    UserDeptDto citem = (UserDeptDto)item;
                    user_detail user_Detail = new user_detail();
    
                    user_Detail.department_id = citem.Id;
                    newUser.user_detail.Add(user_Detail);
                }
                
                db.SaveChanges();
                MessageBox.Show("User Updated Successfully!", "Message");
                Close();
                
                RefreshData();
            }
            
            private void RefreshData()
            {
                UsersForm.fromUsersForm.dataGridView1.DataSource = db.users.ToList();
            }
    
            private void LoadDeptToControl()
            {
                var deptlist = db.departments.ToList();
                foreach (var item in deptlist)
                {
                    checkedListBox1.Items.Add(new UserDeptDto()
                    {
                        Id = item.department_id,
                        DepartmentName = item.department_name
    
                    });
    
                   
    
                }
            }
    
        }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, November 26, 2018 8:53 AM
    Moderator

All replies

  • Hello,

    Please i am a little bit stuck at loading user information in windows form using EF. The part where i got stuck is loading the list of all departments available in the department table and checking the user_detail table to check the department a user belongs to.

    A user can belong to one or more department. So, its a many to many relationship. But the junction table is user_detail which holds the user_id and depatment_id (user with related department)

    I need to load this information unto a checklistbox.

    Here is the code i have so far.

     private void LoadAllDeptWithRelatedDept(user user)
            {
                //load all available department from [department table]
                //and also check or mark the box which the user
                //belong to from the [user_details table]
               
                using (MyTestEntities db = new MyTestEntities())
                {
                    var result =
                        from ud in db.user_detail
                        join d in db.departments on ud.department_id equals d.department_id
                        join u in db.users on ud.user_id equals u.user_id
                        where ud.user_id == user.user_id
                        select ud;
                    foreach (var item in result)
                    {
                        checkedListBox1.Items.Add(new UserDeptDto()
                        {
                            Id = item.department_id,
                            DepartmentName = item.department.department_name                     
    
                        });
    
                        //TODO: set the chkListbox
                        int index = checkedListBox1.Items.IndexOf(item);
    
                        if (index >= 0)
                        {
                            checkedListBox1.SetItemChecked(index, true);
                        }
    
                    }
    
                    
                }
     private void UpdateUser(user user)
            {
    
                user.firstname = txtFirstname.Text.Trim();
                user.lastname = txtLastname.Text.Trim();
                user.username = txtUsername.Text.Trim();
                user.password = txtPassword.Text.Trim();
    
                //update code for also checklistbox
    
                using (MyTestEntities db = new MyTestEntities())
                {
                    db.Entry(user).State = EntityState.Modified;
                    db.SaveChanges();
                    MessageBox.Show("User Updated Successfully!", "Message");
                    Close();
                }
                RefreshData();
            }

    I can successfully get all department which a user belong to. But the idea is 

    1. Load all available department in the [department table] to the checklist box

    2. Now check/tick the department name which the user belong to when loading all depts

    3. Then update this info

    I have created a sample project to replicate this issue

    Link: https://1drv.ms/f/s!Aq-hLZmB9gUFh_IkC_EKnR_bDjGzKg

    Appreciate any help. 

    Hello,

    Please, anybody with an idea on how to solve this and work with Checklistbox?. Will sincerely appreciate.

    Monday, November 26, 2018 3:59 AM
  • Hi Godymn,

    Please check the following code.

    public partial class UserAccount : Form
        {
            private readonly user _user;
            private MyTestEntities db = new MyTestEntities();
            public UserAccount(user user)
            {
                InitializeComponent();
    
                LoadDeptToControl();
                _user = user;
                LoadUserObjectToControl(_user);
            }
    
            
    
            private void LoadUserObjectToControl(user user)
            {
                txtFirstname.Text = user.firstname;
                txtLastname.Text = user.lastname;
                txtEmail.Text = user.email;
                txtUsername.Text = user.username;
                txtPassword.Text = user.password;
    
                LoadAllDeptWithRelatedDept(user);
            }
    
            
            private void LoadAllDeptWithRelatedDept(user user)
            {
                //load all available department from [department table]
                //and also check or mark the box which the user
                //belong to from the [user_details table]
               
                using (MyTestEntities db = new MyTestEntities())
                {
                    var result =
                        from ud in db.user_detail
                        join d in db.departments on ud.department_id equals d.department_id
                        join u in db.users on ud.user_id equals u.user_id
                        where ud.user_id == user.user_id
                        select ud;
    
                    foreach (var item in result)
                    {
    
                        for (int i = 0; i < checkedListBox1.Items.Count; i++)
                        {
                            UserDeptDto deptDto = (UserDeptDto)checkedListBox1.Items[i];
    
                            if (deptDto.DepartmentName == item.department.department_name && deptDto.Id == item.department.department_id)
                            {
                                checkedListBox1.SetItemChecked(i, true);
                            }
                        }
                    }
                }
    
            }
    
            private void btnUpdate_Click(object sender, EventArgs e)
            {
                UpdateUser(_user);
            }
            private void UpdateUser(user user)
            {
    
                var newUser = db.users.Find(user.user_id);
    
                newUser.firstname = txtFirstname.Text.Trim();
                newUser.lastname = txtLastname.Text.Trim();
                newUser.username = txtUsername.Text.Trim();
                newUser.password = txtPassword.Text.Trim();
    
                
    
                if(newUser.user_detail != null)
                {
                    newUser.user_detail.Clear();
                }
    
                foreach (var item in checkedListBox1.CheckedItems)
                {
                    UserDeptDto citem = (UserDeptDto)item;
                    user_detail user_Detail = new user_detail();
    
                    user_Detail.department_id = citem.Id;
                    newUser.user_detail.Add(user_Detail);
                }
                
                db.SaveChanges();
                MessageBox.Show("User Updated Successfully!", "Message");
                Close();
                
                RefreshData();
            }
            
            private void RefreshData()
            {
                UsersForm.fromUsersForm.dataGridView1.DataSource = db.users.ToList();
            }
    
            private void LoadDeptToControl()
            {
                var deptlist = db.departments.ToList();
                foreach (var item in deptlist)
                {
                    checkedListBox1.Items.Add(new UserDeptDto()
                    {
                        Id = item.department_id,
                        DepartmentName = item.department_name
    
                    });
    
                   
    
                }
            }
    
        }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, November 26, 2018 8:53 AM
    Moderator
  • Hi Godymn,

    Please check the following code.

    public partial class UserAccount : Form
        {
            private readonly user _user;
            private MyTestEntities db = new MyTestEntities();
            public UserAccount(user user)
            {
                InitializeComponent();
    
                LoadDeptToControl();
                _user = user;
                LoadUserObjectToControl(_user);
            }
    
            
    
            private void LoadUserObjectToControl(user user)
            {
                txtFirstname.Text = user.firstname;
                txtLastname.Text = user.lastname;
                txtEmail.Text = user.email;
                txtUsername.Text = user.username;
                txtPassword.Text = user.password;
    
                LoadAllDeptWithRelatedDept(user);
            }
    
            
            private void LoadAllDeptWithRelatedDept(user user)
            {
                //load all available department from [department table]
                //and also check or mark the box which the user
                //belong to from the [user_details table]
               
                using (MyTestEntities db = new MyTestEntities())
                {
                    var result =
                        from ud in db.user_detail
                        join d in db.departments on ud.department_id equals d.department_id
                        join u in db.users on ud.user_id equals u.user_id
                        where ud.user_id == user.user_id
                        select ud;
    
                    foreach (var item in result)
                    {
    
                        for (int i = 0; i < checkedListBox1.Items.Count; i++)
                        {
                            UserDeptDto deptDto = (UserDeptDto)checkedListBox1.Items[i];
    
                            if (deptDto.DepartmentName == item.department.department_name && deptDto.Id == item.department.department_id)
                            {
                                checkedListBox1.SetItemChecked(i, true);
                            }
                        }
                    }
                }
    
            }
    
            private void btnUpdate_Click(object sender, EventArgs e)
            {
                UpdateUser(_user);
            }
            private void UpdateUser(user user)
            {
    
                var newUser = db.users.Find(user.user_id);
    
                newUser.firstname = txtFirstname.Text.Trim();
                newUser.lastname = txtLastname.Text.Trim();
                newUser.username = txtUsername.Text.Trim();
                newUser.password = txtPassword.Text.Trim();
    
                
    
                if(newUser.user_detail != null)
                {
                    newUser.user_detail.Clear();
                }
    
                foreach (var item in checkedListBox1.CheckedItems)
                {
                    UserDeptDto citem = (UserDeptDto)item;
                    user_detail user_Detail = new user_detail();
    
                    user_Detail.department_id = citem.Id;
                    newUser.user_detail.Add(user_Detail);
                }
                
                db.SaveChanges();
                MessageBox.Show("User Updated Successfully!", "Message");
                Close();
                
                RefreshData();
            }
            
            private void RefreshData()
            {
                UsersForm.fromUsersForm.dataGridView1.DataSource = db.users.ToList();
            }
    
            private void LoadDeptToControl()
            {
                var deptlist = db.departments.ToList();
                foreach (var item in deptlist)
                {
                    checkedListBox1.Items.Add(new UserDeptDto()
                    {
                        Id = item.department_id,
                        DepartmentName = item.department_name
    
                    });
    
                   
    
                }
            }
    
        }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Hi Zhanglong,

    Thank you. This work just fine.

    Friday, November 30, 2018 12:49 AM