CODEWITHSUNDEEP
c#winforms
RedRocket
RedRocket

Reputation: 1733

How to get the key(id) of the binded checkListBox

I am have a Windows Forms application and also using SQL to get the data from my db. I have a binded checkListBox in my Windows Forms application and I would like to get the primary key (the value) of all the checked Items in my checkListBox. What I realize is in combo box, they have a build in method 'selectedValue' I am wondering if we can do the same thing in checkListBox by looping through the checkedItems in checkListBox and get its value

Let say in my db table, I have: 

EmployeeId    Employee Name
1                 Jimmy
2                 Pam
3                 Bob
4                 Kobe

My binded checkListBox showed something like 

[x]Jimmy
[x]Pam
[]Bob
[]Kobe

Can I get the checkedItem item's id by using sql query. Now let say Jimmy and Pam is checked and I want to get their employeeId. I am not sure how to do it. Help will be appreciated.

select * from employee_table where employeeId = '"+checkListBox.SelectedValue+"'

or 

foreach(var item = item.CheckedItems{
     select * from employee_table where employeeId = '"items"';
}

Upvotes: 1

Views: 878

Answers (2)

Reza Aghaei
Reza Aghaei

Reputation: 125257

You don't need to go to database again to retrieve Id of your items.

You can create a class for items that contains an Id and a Name property and override ToString method to return the string that you want to show in CheckedListBox:

public class ItemModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public override string ToString()
    {
        return Name;
    }
}

Then when loading data, you can select the output and shape it to ItemModel and then add these ItemModels to CheckedListBox:

private void Form_Load(object sender, EventArgs e)
{
    var db = new TestDBEntities();

    //Select items that you need and shape it to ItemModel
    var list = db.Categories.Select(x => new ItemModel
                    {
                        Id = x.Id,
                        Name = x.Name
                    })
                    .ToList();

    //We cast the list to object[] because AddRange method accept object[]
    this.checkedListBox1.Items.AddRange(list.Cast<object>().ToArray());
}

Then when you need to know Id of checked items, you can simply cast each checked item to ItemModel an use its Id property:

private void button1_Click(object sender, EventArgs e)
{
    this.checkedListBox1.CheckedItems.Cast<ItemModel>()
        .ToList()
        .ForEach(item =>
        {
            MessageBox.Show(string.Format("Id:{0}, Name:{1}", item.Id, item.Name));
        });
}

Note:

If you use another method to connect to database, you can simply change this code to satisfy your requirements, for example this code uses ADO.Net objects to shape data to ItemModel:

private void CheckedListBoxSample_Load(object sender, EventArgs e)
{
    var connection = @"data source=(localdb)\v11.0;initial catalog=TestDB;integrated security=True;MultipleActiveResultSets=True;";
    var command = "SELECT Id, Name From Categories";
    var dataAdapter = new System.Data.SqlClient.SqlDataAdapter(command, connection);
    var table = new DataTable();
    dataAdapter.Fill(table);

    var list = table.Rows.Cast<DataRow>()
        .Select(row => new ItemModel
        {
            Id = row.Field<int>("Id"),
            Name = row.Field<string>("Name")
        })
        .ToList();

    this.checkedListBox1.Items.AddRange(list.Cast<object>().ToArray());
}

Upvotes: 1

CuccoChaser
CuccoChaser

Reputation: 1079

Something along these lines might help you. Basically what it does, is that it builds a parameterized query, and then adds all the selected items using SqlParameters. Then using the reader, you are able to parse each returned record. The code below will probably need some modifications for you to use it, but should help you getting started.

Although I would strongly suggest using an ORM like Entity Framework. Using an ORM you don't have to build your own queries, and allows you to use strong typed classes to "talk" to your database.

var query = "SELECT * FROM employee_table WHERE imployeeId IN (@ids)";
var ids = String.Join("," 
                MyCheckBoxList.Items
                    .Cast<ListItem>()
                    .Where(x => x.Selected)
                    .Select(x => x.Value);

using (var connection = new SqlConnection(myConnectionString))
{
    connection.Open();
    using(var command = new SqlCommand(query, connection)
    {
        command.Parameters.Add("ids", ids);
        var reader = command.ExecuteReader();
        while(reader.Read())
        {
            //get all the needed data from the reader
        }
    }
}

Upvotes: 1

Related Questions