Sometimes your asked to design an application that will contain ‘X’ number of checkboxes. The DBA is a hardball and tells you no way am I creating 30 columns for your checkboxes, ill create one and you figure it out. What a punk but lets see what we can do about this. We could do one of two things, we could have the DBA create a vertical approach where we store the value of each checkbox in its own row, or we can have the DBA create a bit column and we bit flag this baby. Well being the title of this article has “bit column”, you know what I’m going to talk about. That’s right, we are creating a bit column. Just a tad bit about bit flags since we are not really focused on explaining them in this article is that they are powers of 2. Meaning our values stored as enums will increment to the power of 2 starting with the value 1.
Example:
public enum CheckBoxValues
{
first = 1,
second = 2,
third = 4,
fourth = 8,
fifth = 16,
sixth = 32,
seven = 64,
eight = 128,
nine = 256
}
So here we show only 9 values, you however would continue on down the path of 30 as per this title. But for clarity we will keep this simple. Seeing that our values of the enum go from 1-256 does not mean your values will be stored in the db this way. These are flag values using the power of 2, our db values will be the int value of the enum. So “first” int value is 1 and “nine” int value is 9. Now that we have that clear lets move on.
Ok, so we now have a bit column called “CheckedValues”, sounds good to me. We need to store all our check box values into this one column. The way we handle this is we treat the checkbox checked value as its enum value. So for example we would have a check box called chkFirst and its checked value would be 1, chkNine would have a checked value of 9 and so on. The checked values must match or be handled as the enum value (such as (int)CheckBoxValues.third will return 3). This is important because we need to sum these values up. So assume we are done and have all our checkboxes in place. We are now ready to store/retrieve these values. When the save button is hit, your code will then iterate through the checked check boxes and sum up their values. So lets say first, second, and fourth are checked then our value would be 7 that gets stored in the database (1+2+4 = 7).
Setting the checkbox check state from the db value is just as easy. Create a simple method like so:
private bool IsChecked(int dbValue, int enumValue)
{
return ((dbValue & enumValue) == enumValue);
}
Then you would simply handle the checked value like:
chkFirst.Checked = IsChecked(7, (int)CheckBoxValues.first);
chkSecond.Checked = IsChecked(7, (int)CheckBoxValues.second);
chkThrid.Checked = IsChecked(7, (int)CheckBoxValues.third);
chkFourth.Checked = IsChecked(7, (int)CheckBoxValues.fourth);
...
I am passing 7 since that is what the DB Value is in our example. How is this working? Well its actually quite simple. We are getting the BitAnd value of the enum and checking if its turned on in our db value. So what is happening in the backend is something like this. The binary value of 7 is 0111. The IsChecked method is comparing this value to the passed enum flag value. So a check looks something like this for second: 0111 & 0010 == 0010. It passes because the 1 & 0 = 0 and 1 & 1 = 1. So 0111 & 0010 does in fact = 0010 because starting from right to left we see 1 & 0 = 0, 1 & 1 = 1, 1 & 0 = 0, 0 & 0 = 0, total that up and we get 0010 (Remember I went right to left, so do the same totaling). So that tells us this flag is on so check the checkbox. The value 7 would check our 1, 2, and 4 enum values. The exact ones we checked. If the db value is 5 then first and fourth would be checked and so on. This is a 32 bit column so naturally you could only store 32 bits of values in it but 30 checkboxes would easily fit in just one column.
Hope this helps you out there.
