Fully data-bound CheckedListBox

manytomanytest-src

Introduction

Maybe you already know that the .NET CheckedListBox doesn't provide any data binding, though it seems to be eligible in cases where you have a many-to-many data relation with a relation table; e.g., a product can be part of multiple categories, and each category can contain multiple products.

So, my task was to make the CheckedListBox bindable again.

Background

If you search for a bindable CheckedListBox on CodeProject, you will find two or three. I did this too. But when I took a closer look at them, I noticed that they don't implement a new data binding facility, but rather reactivate the listbox data binding to one single table - meaning that the list of items will be data bound. The checkboxes have to be set more or less manually. See ExCheckedListBox.aspx or http://www.codeproject.com/KB/grid/custCheckedListBox.aspx for details.

This is not what I wanted. I wanted the CheckedListBox to be fully data-bindable; e.g., holding a list of categories and automagically setting the checked states according to the data sources (one of them holding the currently selected product), and adding/removing entries of the relation table if the user (un)checks an item. Playing with the sample should make this clear.

So, the next question was how to create my own data binding. I had a basic idea on how this should work, but this article helped me a lot: http://www.codeproject.com/KB/database/DataBindCustomControls.aspx.

The BoundCheckedListBox does not bind to only one data source, it binds to three data sources and needs five data members.

Using the code

The sample shows which person loves which fruits.

So, we need:

A data source for the parent table, Personen
A data source for the child table, Fruechte
A data source for the relation table, p_f
A data member for the parent ID column, ID (Personen.ID)
A data member for the child ID column, ID (Fruechte.ID)
A data member for the child display column (holding the items shown in the ListBox), Name (Fruechte.Name)
Two data members for the two columns holding the IDs in the relation table

This is how you can set those (or via a property list in Design view):

boundCheckedListBox1.ChildDisplayMember = "Name";
boundCheckedListBox1.ChildValueMember = "fID";
boundCheckedListBox1.ParentValueMember = "pID";
boundCheckedListBox1.ParentIDMember = "ID";
boundCheckedListBox1.ChildIDMember = "ID";
boundCheckedListBox1.ParentDataSource = parentBindingSource;
boundCheckedListBox1.ChildDataSource = childBindingSource;
boundCheckedListBox1.RelationDataSource = relationBindingSource;

where each data source is a binding source, having a dataset as a data source and the corresponding table as the data member. Have a look at the source.

Points of interest

It is very important that the BoundCheckedListBox gets a currency manager from the data source and not a property manager (as that does not provide a list, an exception will be thrown). I figured out that using a DataSet.Table as the data source only wants to give me the property manager. So, I made use of binding sources.

Set the default values which don't throw exceptions if a new DataRow is created and no values are set. I can only call the currency manager's AddNew method, and the values are set after that.

Make use of the EndEdit functions of the binding sources. I have a button adding a new element to the child table and textboxes setting the name. E.g.:

EventHandler dataChangedHandler = new EventHandler(dataChanged);
....
button_kat_add.Click += dataChangedHandler;
button_kat_del.Click += dataChangedHandler;
....
textBox_kat_name.LostFocus += dataChangedHandler;
....

void dataChanged(object sender, EventArgs e) {
     dataTable1BindingSource.EndEdit();
     ....BindingSource.EndEdit();
     ....BindingSource.EndEdit();
     ....relationBindingSource.EndEdit();
}
......

private void button_kat_add_Click(object sender, EventArgs e) {
    this.....BindingSource.AddNew();
}

private void button_kat_del_Click(object sender, EventArgs e) {
     if (kategorienBindingSource.Current != null)
         this.kategorienBindingSource.RemoveCurrent();
}

If a checkbox is unchecked, the DataRow in the relation table is deleted. So we might end up with several deleted rows with the same IDs. This should be improved by un-deleting rows.

Feel free to make comments and updates!

History

19.04.2008 - Initial version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

vbar

Systems Engineer
Otto-von-Guericke University of Magdeburg

Germany

Member

Student of Systems Engineering and Engineering Cybernetics

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

Minor bugfix

Member 4444404

17:59 5 Mar '09

With no entries in the relation table, check two entries, then uncheck the first one. Both will be unchecked. The fix is simple. Look for the line `relationDataManager.AddNew()` towards the end of the BoundCheckedListBox_ItemCheck function. At the end of the if block add `relationDataManager.EndCurrentEdit()`
Sign In·View Thread·Permalink

Re: Minor bugfix

stewart.page

8:02 9 Aug '09

Hi I'm trying to implement this in VB.net, but I'm not too sure where to start, can you help point me in the right direction please. I can see that there are no properties like those you mention in the basic control, so how do I get them to appear?
Sign In·View Thread·Permalink

Re: Minor bugfix

vbar

10:15 10 Aug '09

Hi, I do not know where to start answering your question, basically beacause it's not very precise: I'm trying to implement this in VB.net <- You answered as reply to "Minor bugfix", so with "this" do you mean this bugfix or this whole project? I can see that there are no properties like those you mention in the basic control <- which ones exactly? MfG vbar
Sign In·View Thread·Permalink

strange behavior on storing items into database

ai_mueller

4:30 22 Feb '09

Hi, not all selected items are stored into database!! Any help would be appreciated!!! Andy
Sign In·View Thread·Permalink

Re: strange behavior on storing items into database

vbar

6:31 22 Feb '09

Hi, strikly speaking, this control does not store/remove any items (of the parent/child tables). In fact it only updates the relation table, it creates or deletes rows in the relation table. You might need to use EndEdit functions. vbar
Sign In·View Thread·Permalink

Re: strange behavior on storing items into database

ai_mueller

9:38 22 Feb '09

Sorry for not being precise... >strikly speaking, this control does not store/remove any items (of the parent/child tables). In fact it only updates the relation table, it creates or deletes rows in the relation table. I already know that
Sign In·View Thread·Permalink

Re: strange behavior on storing items into database

vbar

11:11 22 Feb '09

hmm, you are not more precise now Wink | ;)

.

what about the endedit functions?

Try sth. like this to call all the endedit functions on your bindingsources:

EventHandler dataChangedHandler = new EventHandler(dataChanged);
..........
boundCheckedListBox1.LostFocus += dataChangedHandler;
..........
..........
void dataChanged(object sender, EventArgs e) {
    dataTable1BindingSource.EndEdit();
    childBindingSource.EndEdit();
    relationtableBindingSource.EndEdit();
    fKrelationBindingSource.EndEdit();
}

What's wrong in my code

Monir Sabbagh

0:37 12 Feb '09

I used LINQ to SQL, I have 3 table, Roles, Reports, Role_Report.
Evrt role may conatins many reports and every report can be in many Roles.

I show a role in Details form and the reports of the role is selected using your custom checked list control.

I can navigates the roles but the custom checked list control show a list of reports but alwayes unchecked!! so Am I missing something?

Next is the same three tables but I created the tables using in momory data and I filled the lists, then I got the same results, which is the custom checked list control show a list of reports but alwayes unchecked!!
Look at the following code list and please tell me what is wrong:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
 
namespace MemManyToMany
{
    public partial class Form1 : Form
    {
        public class Report_Role
        {
            public int ReportID { get; set; }
            public int RoleID { get; set; }
        }
 
        public class Report
        {
            public int ReportID { get; set; }
            public string ReportName { get; set; }
        }
 
        public class Role
        {
            public int RoleID { get; set; }
            public string RoleName { get; set; }
        }
 
        public List<Report> Reports = new List<Report>()
        {
            new Report { ReportID = 3, ReportName = "MCSE"    },
            new Report { ReportID = 4, ReportName = "English" },
            new Report { ReportID = 5, ReportName = "CCNA"    },
            new Report { ReportID = 7, ReportName = "MCSD"    }
        };
 
        public List<Role> Roles = new List<Role>(){
            new Role { RoleID = 1, RoleName = "Administrator"  },            
            new Role { RoleID = 2, RoleName = "Manager"        },            
            new Role { RoleID = 3, RoleName = "Salse"          },
            new Role { RoleID = 4, RoleName = "Student"        }
        };
 
        public List<Report_Role> Report_Roles = new List<Report_Role>() {
            new Report_Role { RoleID = 1, ReportID =3 },
            new Report_Role { RoleID = 1, ReportID =5 },
            new Report_Role { RoleID = 1, ReportID =7 },
            new Report_Role { RoleID = 2, ReportID =3 },
            new Report_Role { RoleID = 2, ReportID =7 },
            new Report_Role { RoleID = 4, ReportID =3 },
            new Report_Role { RoleID = 4, ReportID =5 }
        };
 

 
        public Form1()
        {
            InitializeComponent();
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
            form1_RoleBindingSource.DataSource = Roles;
            form1_Report_RoleBindingSource.DataSource = Report_Roles;
            form1_ReportBindingSource.DataSource = Reports;
 

 
            BCB.ChildDisplayMember = "ReportName";
            BCB.ChildValueMember = "ReportID";
            BCB.ParentValueMember = "RoleID";
            BCB.ParentIDMember = "RoleID";
            BCB.ChildIDMember = "ReportID";
 
            BCB.ParentDataSource = form1_RoleBindingSource;
            BCB.ChildDataSource = form1_ReportBindingSource;
            BCB.RelationDataSource = form1_Report_RoleBindingSource;
 
        }
    }
}

Re: What's wrong in my code

Monir Sabbagh

8:54 12 Feb '09

I am answering my own Wink | ;)

question!!
I read the comment inside setHaken() and I used

parentID = parentDataManager.GetItemProperties().Find(parentIDMember, false).GetValue(parentDataManager.List[parentDataManager.Position]);

instead of:

DataRowView drv = (DataRowView)this.parentDataManager.Current;
parentID = drv[this.parentIDMember];

it worked

Re: What's wrong in my code

vbar

11:22 12 Feb '09

Hi,

I could not compile this code. Basically the the syntax of the Lists was not understood by the compiler (.Net 2.0). So I converted it a bit.
Finally I had it running with the checkedlistbox with the reports in it and another normal listbox to chose the role. The problem that it did not work is basically that I made this control to work with datasets, datatables, datarows...etc. I recommend you to use a typed dataset, which can be easily designed with visual studio. Anyway, you can use these lists to, but changes to the BoundCheckedListBox class are required.
This means around lines 725, 295, 647 where the IDs are determined through a cast to DataRowView etc. it needs to be changed to what is written in the corresponding comments in the code near these lines already (different for each case). The cast is not possible for Role, Report ... to DataRowView, so we need to use the method in in comment.

Ex. for Line around 295:
Before:

try {
          //do we always have type datarowview?!
          //another solution is parentDataManager.GetItemProperties().Find(parentIDMember, false).GetValue(parentDataManager.List[parentDataManager.Position]);
          //currencymanager.current shows buggy behavior sometimes when used with tostring()
          DataRowView drv = (DataRowView)this.parentDataManager.Current;
          parentID = drv[this.parentIDMember];
} catch (Exception) { }

After:

try {
     //do we always have type datarowview?!
     //another solution is parentDataManager.GetItemProperties().Find(parentIDMember, false).GetValue(parentDataManager.List[parentDataManager.Position]);
     //currencymanager.current shows buggy behavior sometimes when used with tostring()                
     parentID = parentDataManager.GetItemProperties().Find(parentIDMember, false).GetValue(parentDataManager.List[parentDataManager.Position]);
} catch (Exception) { }

I can send the project with the changed class to you.

MfG
vbar

Re: What's wrong in my code

vbar

11:31 12 Feb '09

Oh, did not see you answered your question already before posting.
Sign In·View Thread·Permalink

Re: What's wrong in my code

Monir Sabbagh

2:14 14 Feb '09

Thank you for your answer,

I am using .net 3.5, LINQ to SQL in my program, and unfortunately your checkedListBox is not fully compatible with LINQ to SQL, even if I follow your comments in all parts of the code.
I got casting error when I click on item "to check" or "to remove the check", so, I think I must remove the use of DataRowView and change it with something else.
I have to study the code in details and I will try to make it work with LINQ, if you have any idea on how to do so, please help.
it is really good control and I am fully interested in making it fully compatible with the new technique (LINQ to SQL), please help.

Monir

Re: What's wrong in my code

vbar

8:01 15 Feb '09

There should be no use of cast to DataRowView anymore if you followed my post (and yours).
Sign In·View Thread·Permalink

Awesome control

Phil Jeffrey

20:31 10 Jul '08

Thanks very much for this, exactly what I needed.

I tried binding the data sources to NetTiers objects but couldn't get this to work. So I ended up using the TableAdapter method as described in your article, and it works beautifully.

Found that I really only needed to .Update the relation TableAdapter. Updates to parent and child tables are usually done elsewhere in my application.

Thanks again.

Smile | :)

Adjust database

De_paus_is_hip

9:25 15 May '08

First of all i'd like to say: great piece of work! this really aided me alot with creating databound checkboxes. however, i do have one question: is it possible to make the checkbox update the database aswell? instead of just updating the datatable ? yours sincerely
Sign In·View Thread·Permalink

Re: Adjust database

vbar

10:58 15 May '08

hi,
i dont know if i understand your questions correctly.
is it possible to make the checkbox update the database aswell? <- do you mean the checkbox you created with your custom databinding? which database?

why don't you use the built-in databinding of the checkbox control?

you can update your database the normal ways
a) automatic via datatableadapter, fill & update functions etc
b) manual. get changes via getchanges(), write your sql update strings, execute them depending on type of change, acceptchanges

of course, if you want your update immediatly after clicking the checkbox, you could execute the required functions to update your database on the checkchanged event of the checkbox for example. or use a save button....

if one only has a single action to do with the checkbox, which should be immediatly written to database, it's maybe easier not to use databinding. i mean, the idea of datasets is rather to hold the data locally, close the connection and commit the changes later to the database.

maybe this helps u a bit
vbar