Having the CheckBoxList support many-to-many relationships

Introduction

Yet again, I am surprised by the lack of basic GUI-functionality of many of the controls in ASP.NET (2.0), and how the upcoming, highly promising new generations of web controls keep disappointing us again and again! My problem this time was to allow the user to, for an item, select one or more related items from a list of checkboxes (preferably using the CheckBoxList control). This relationship is stored as a many-to-many relationship in the database, where the connecting database table contains the keys of the related items.

What I believe is a reasonable requirement of a CheckBoxList control is to, when read from the database, have the user's selections restored as previously saved. However, this is not as simple as it should be! I have seen many implementations of this on the internet, but none that uses the declarative approach using the new SqlDataSource control, I have tried to follow this time... Why must you always extend the basic controls to have basic functionality at hand!?!

I am sorry for my lack of positive attitude...but with a little creative (declarative) programming and some code fixes (non-declarative), I managed to get what I wanted (without having to create my own derived classes). So, this article contains code snippets for solving this problem! And, if you are in the same mode as I was before writing this article, perhaps this article will take you back on track again!?

Background

The most beautiful solution to this problem would be to create my own control, extending the functionality of the CheckBoxList control. My approach would have been to create a new property "DataSelectedField", to be used when databinding the control to the SqlDataSource control, as the DataTextField and DataValueField do. However, the reason I did not go by this approach is of principle not to have to do all the work myself, and that this should have been done by the CheckBoxList already! Also, I'm determined to give the declarative programming style a chance! (However, if you have implemented this, please let me know! ;-)

The Code

My database tables are A and B, having a connecting table A_B, storing the associations between them, having the foreign key columns A_B.AID and A_B.BID. The focus is A, to be related to zero-or-many B. The following Select statement retrieves all rows in B, and its potential associations to a specific A (read LEFT JOIN):

SELECT B.BID, B.Name, 
    CASE
        WHEN A_B.AID IS NULL THEN CAST(B.BID AS varchar(36)) 
    ELSE (CAST(B.BID AS varchar(36)) + '_' + CAST(A_B.AID AS varchar(36)))
    END AS DataValueField
FROM B
    LEFT OUTER JOIN A_B ON (B.BID = A_B.BID AND A_B.AID = @AID)

Besides BID and Name, the query returns the value used for knowing if the item should be pre-selected ("DataValueField"), i.e., the pre-selected item should also have a "<underscore>A_B.AID" concatenated to it. @AID is the current A we focus on. (All IDs are of type uniqueidentifier, and treated as text on the client.)

Now you know the data model. I start from the CheckBoxList and go down... The code of the CheckBoxList then, looks like this:

<asp:CheckBoxList ID="checkBoxList" runat="server"
    DataSourceID="sqlDataSource"
    DataTextField="Name"
    DataValueField="DataValueField"
    ...
</asp:CheckBoxList>

And the referred SqlDataSource code looks like this:

<asp:SqlDataSource ID="sqlDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="<the query as above>"
    InsertCommand="INSERT INTO A_B SELECT @AID, BID FROM B WHERE BID IN ({0})"
    DeleteCommand="DELETE FROM A_B WHERE AID = @AID">
    <SelectParameters>
        <asp:SessionParameter Name="AID" SessionField="ID" />
    </SelectParameters>
    <InsertParameters>
        <asp:SessionParameter Name="AID" SessionField="ID" />
    </InsertParameters>
    <DeleteParameters>
        <asp:SessionParameter Name="AID" SessionField="ID" />
    </DeleteParameters>
</asp:SqlDataSource>

Note the InsertCommand's format placeholder (i.e. {0}), which will be used later. But before we go further into saving the information to the database, we must prepare the CheckBoxList for showing pre-selections, like so:

foreach (ListItem listItem in checkBoxList.Items)
{
    if (listItem.Value != string.Empty)
    {
        string [] split = listItem.Value.Split(new Char[] {'_'});
        listItem.Value = split[0];
        listItem.Selected = (split.Length > 1) ? true : false;
    }
}

This is where the DataValueField is transformed into Value and Selected (Checked, as it is transformed to from the ListItem to the CheckBox control) fields. This code should be executed after the control is data bound, meaning after the DataBound event has been fired.

So, now we have the list rendered, we go back to the saving part. The key problem of a connecting table is that the "update" to be made when the user saves, in real life, is a delete of unchecked items and insert of the newly checked items. We need to handle this ourselves, using both insert and delete commands, triggering the corresponding methods. So, instead of using the SqlDataSource's predefined commands, we trigger the updates manually from code (but we still have "declared" the queries).

This is the code for controlling updates, and to adjust the insert command:

// Run the DELETE-statement first.
sqlDataSource.Delete();

// Construct the sql INSERT-statement for selected items:
StringBuilder sb = new StringBuilder();
foreach (ListItem listItem in checkBoxList.Items)
{
    if (listItem.Selected)
    {
        if (sb.Length > 0)
            sb.Append(",");
        sb.Append("'");
        sb.Append(listItem.Value);
        sb.Append("'");
    }
}
if (sb.Length > 0)
{
    sqlDataSource.InsertCommand =
        string.Format(
        sqlDataSource.InsertCommand, 
        sb.ToString());
    sqlDataSource.Insert();
}

The code is straightforward, beginning with deleting all, and then constructing the IN-statement list for performing string.Format on the format placeholder, and then adding all selected items in the list. This code may be placed anywhere, e.g., in the Click event of a Button.

History

• 2007-06-29: Initial version...feel free to give feedback!