How do I handle a DBNull to Boolean conversion in my XSD DataSet?

Question

In my database, I have a few columns in one of my tables that are bit (boolean) values. They are allowed to be NULL since the fields are not always going to contain data.

I've gone through the process of creating an XSD DataSet using the table and made sure that the AllowDBNull field is set to True.

However, when I pull a down record from the database into the datatable using the configured GetData method, I run into the following error:

[InvalidCastException: Conversion from type 'DBNull' to type 'Boolean' is not valid.]

Do I need to specify something other than in the DefaultValue field for the column, or is there some other field to set?

I am in the development phase still with this project, so if changing the fields to a char and using a Y/N/NULL option is preferred, I'm not too adverse to going that way.

Answer 1

IMHO you shouldn't allow nulls in a bit/boolean field.

Mike Hadlow has a good post on this here:

http://mikehadlow.blogspot.com/2006/10/nullability-voodoo.html

Boolean is by definition a bi-state type. By making it nullable, you are adding a third state. It will come back to haunt you at some point.

Answer 2

I have to disagree with Iain. Three value logic is just as viable as 2 value logic. The person in the table is alive or dead or you don't know if he's alive or dead. If mike had his way, you couldn't use a bit here. You'd need an FK to a table of status, 1= Alive, 2=Dead, 3=Unknown.

The whole purpose of the bit is to save space, you could always make the column a CHAR(1) with a Constraint limiting the values to T or F, Y or N (or U).

But I think Iain is misinterpreting what Mike is saying. Mike's not discouraging the use of Nulls, after all he thinks the EndDate column should be null until the process is done. He's just saying don't imbue a null value in the column with mystic properties... like it's the key for knowing if a process is in a certain state.

In fact, Nulls in a database have certain very useful side-effects. Oracle (maybe other databases) doesn't index nulls. SO if you have a column where only one value is important -- like say you have an employee list you never purge and an Is_Active_Employee Bit Column that's indexed. In this case, for an established business, the number of employees you've had is >>> than the number of employees you have now. Say you have 1% 1's and 99% 0's.

That index is 99x larger than it needs to be. It's indexing all the 1's which is fine since they are 1% of the table but it's also loaded with 0's for rows you COULD NEVER use that index to find. If you want's all the inactive employees, the indexed would be ignored and a FTS would be performed. So 1 and NULL would be MUCH more efficient from a database perspective.