1
\$\begingroup\$

I have inherited an application that has code to save and populate values in an ASP.NET checkboxlist control. The existing code is very lengthy and does not appear to be efficient to me. I plan on rewriting this application and have complete control over the database design.

All of the checkboxlist items are stored in an SQL table as bit fields. Each row of checkboxlist values are assigned to a user ID. Once again, my goal is to find a more efficient way to save and populate selected checkboxlist items with less code. I'm open to changing the table design, using Linq or lambda if it helps. I'm new to ASP.NET but there must be a better way of doing this.

Current table design
UserID  AA  BC  AC
1       1    1  0 
2       0    1  1
3       1    1  0 
4       1    1  1

'Code to populate selected checkboxlist items(there are a total of 22 bit values)

Dim CodeData as datarow
CodeData = CodeDataTable.Rows(0)
For Each li As ListItem In Me.cblCodes.Items
                    If li.Text = "AA" Then
                       If Not CodeData.IsOneNull Then
                          If CodeData.One = True Then
                               li.Selected = True
                        End If
                    End If
                End If
                  If li.Text = "BC" Then
                    If Not CodeData.IsTwoNull Then
                        If CodeData.Two = True Then
                            li.Selected = True
                        End If
                    End If
                End If
         If li.Text = "AC" Then
                    If Not CodeData.IsTwoNull Then
                        If CodeData.Two = True Then
                            li.Selected = True
                        End If
                    End If
                End If  
            ......

Next

'Code to save checkboxlist values
 Dim AA As Boolean = False
 Dim BC As Boolean = False
 Dim AC As Boolean = False
 Dim AD As Boolean = False


 AA = Me.cblCodes.Items.FindByText("AA").Selected()
 BC = Me.cblCodes.Items.FindByText("BC").Selected
 AC = Me.cblCodes.Items.FindByText("AC").Selected
 AD = Me.cblCodes.Items.FindByText("AD").Selected

 CodeTableAdapter.UpdateCodes(UserID,AA,BC,AC,AD.......)
\$\endgroup\$
  • \$\begingroup\$ If nought else, you can use AndAlso and stop check for True: If li.Text = "AA" AndAlso Not CodeData.IsOneNull AndAlso CodeData.One Then li.Selected = True. \$\endgroup\$ – HABO Jun 15 '13 at 12:20
  • \$\begingroup\$ Ok, but is it a better approach for me to change the table structure to just store the value in one column? I would have to use a for loop and delete all items before inserting each time the page is saved. Just not sure about the best approach. \$\endgroup\$ – user2301181 Jun 16 '13 at 23:57
  • \$\begingroup\$ If you really need to support nullable bits then I would keep them as separate bit columns. Breaking the repeating code out into a small Sub might help with readability and reliability (DRY). \$\endgroup\$ – HABO Jun 17 '13 at 0:14
  • \$\begingroup\$ I think the code was designed this way to prevent the need to loop thru tons of table rows on page load. There could be 20+ selected checkboxes for one user id. They developer thought the bit field approach would be easier on the database(one row for each user) \$\endgroup\$ – user2301181 Jun 17 '13 at 10:29
2
\$\begingroup\$

I think it's reasonable to look at whether a single column design is possible - it certainly makes life easier:

For Each DataRow dr in CodeDataTable.Rows
    Dim li as ListItem = Me.cblCodes.Items.FindByText(CType(dr["Text"], String))
    li.Selected = True
Next

You could alternatively do a two column design, with a Text varchar and a Value bit:

For Each DataRow dr in CodeDataTable.Rows
    Dim li as ListItem = Me.cblCodes.Items.FindByText(CType(dr["Text"], String))
    li.Selected = CType(dr["Value"], Boolean)
Next

But even the wide table could be done with less repetitive code by using DataTable.Columns to get the names of the columns:

Dim codeData as DataRow = CodeDataTable.Rows(0)
For Each DataColumn dc in CodeDataTable.Columns
    Dim li as ListItem = Me.cblCodes.Items.FindByText(dc.Name)
    If Not codeData.IsNull(dc) AndAlso CType(codeData[dc], Boolean) Then
        li.Selected = True
    End If
Next
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Your recommendations are extremely helpful and I've learned a lot from your post. Thanks! \$\endgroup\$ – user2301181 Jun 18 '13 at 3:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.