nullable db bit columns

checlark posted on Thursday, November 08, 2007

I have a SQL Server 2005 table that contains a bit column which is used as a boolean value. In my code I am using a nullable bool to represent this column. When adding a new row the db will show "NULL" in the column which is the desired behavior. Now when I grab that row to poulate the CSLA BO the SafeDataReader reads the value as false and I need it to return as null. I am using the dr.GetBoolean() method and have also tried to use the dr.GetByte() as well. In my case this column may or may not be set and if not then it is called an invariant. An example might be a person that is entered into my table and the sex of that person is unknown so the column should be null and it is when inserted but an update to that person will set the column to false due to the SafeDataReader returning false instead of null. Returning a false or female represented by a 0 is incorrect. Anyone run into this scenario?

triplea replied on Thursday, November 08, 2007

I prefer using 'Y'/'N' so don't have that much experience with bit columns. But you might want to check out the CSLA Contrib project in Codeplex since a SmartBool struct is provided which works pretty much like SmartDate. So I am pretty sure it will help you overcome the issues you are facing.

tetranz replied on Thursday, November 08, 2007

The SafeDataReader does not directly support nullable types. The regular .NET DataReader doesn't either.

Read it as an object and cast it.

_sex = (bool?) dr.GetValue("Sex")

By the way, the Binding Format and Parse events are useful if you want to bind this to a tristate checkbox.

Ross

ward0093 replied on Thursday, November 08, 2007

I am doing the samething you are for a lot of different Datatypes... I had to create and override the CSLA SmartDateReader to handle this... It might at first glance seem like a lot of extra work but it works great and in ALL my BO objects I call the same function to load the value regardless of the data type and whether or not is a Nullable Type.

MyBusinessCore.Core.SmartDataReader

In my new object I created a simple function used for all type of data, nullable or not. Here is my function below:

I call is like this:

mUserCreated = .GetValue(Of Nullable(Of Guid))("UserCreated")

mIsDeleted = .GetValue(Of Boolean)("IsDeleted")

mIsMaleGender = .GetValue(Of Nullable(Of Boolean))("GenderIsMale")

The new function is my overriden SmartDateReader looks like this:

Public Overridable Shadows Function GetValue(Of T)(ByVal name As String) As T

Return GetValue(Of T)(GetOrdinal(name))

End Function

Public Overridable Shadows Function GetValue(Of T)(ByVal i As Integer) As T

Dim rtn As Object = Nothing

Dim typeOfT As Type = GetType(T)

'///// IF WE HAVE A [NULL] VALUE COMING OUT OF THE DATABASE /////

If IsDBNull(i) Then

If typeOfT.IsGenericType Then

Return Nothing 'IF NULLABLE TYPE, RETURN NOTHING FOR ALL FIELDS

ElseIf typeOfT Is GetType(Boolean) Then

rtn = Convert.ToBoolean(False)

ElseIf typeOfT Is GetType(Byte) Then

rtn = System.Byte.MinValue

ElseIf typeOfT Is GetType(Char) Then

rtn = Char.MinValue

ElseIf typeOfT Is GetType(DateTime) OrElse typeOfT Is GetType(Date) Then

rtn = Date.MinValue

ElseIf typeOfT Is GetType(Decimal) Then

rtn = Decimal.Zero

ElseIf typeOfT Is GetType(Double) Then

rtn = Convert.ToDouble(0)

ElseIf typeOfT Is GetType(Guid) Then

rtn = System.Guid.Empty

ElseIf typeOfT Is GetType(Int16) Then

rtn = Convert.ToInt16(0)

ElseIf typeOfT Is GetType(Int32) Then

rtn = Convert.ToInt32(0)

ElseIf typeOfT Is GetType(Int64) Then

rtn = Convert.ToInt64(0)

ElseIf typeOfT Is GetType(UInt16) Then

rtn = Convert.ToUInt16(0)

ElseIf typeOfT Is GetType(UInt32) Then

rtn = Convert.ToUInt32(0)

ElseIf typeOfT Is GetType(UInt64) Then

rtn = Convert.ToUInt64(0)

ElseIf typeOfT Is GetType(String) Then

rtn = Nothing

Else

Throw New NotSupportedException("The data type: " & typeOfT.Name & " is not supported." & NewLine()& "This method [" & System.Reflection.MethodBase.GetCurrentMethod().Name & "] in object [" & System.Reflection.MethodBase.GetCurrentMethod.ReflectedType.Name & "] is not supported.")

End If

Return CType(rtn, T)

End If

'///// VALID DATABASE VALUE COMING OUT OF THE DATABASE /////

'///// SPECIAL DB FIELD CONVERSIONS ////

If typeOfT Is GetType(Nullable(Of System.UInt16)) Then

If DataReader.GetInt16(i) < 0 Then Return Nothing

rtn = Convert.ToUInt16(DataReader.GetInt16(i))

ElseIf typeOfT Is GetType(Nullable(Of UInt32)) Then

If DataReader.GetInt32(i) < 0 Then Return Nothing

rtn = Convert.ToUInt32(datareader.GetInt32(i))

ElseIf typeOfT Is GetType(Nullable(Of UInt64)) Then

If DataReader.GetInt64(i) < 0 Then Return Nothing

rtn = Convert.ToUInt64(DataReader.GetInt64(i))

ElseIf typeOfT Is GetType(UInt16) Then

rtn = Misc.Strings.IIF(Of UInt16)(DataReader.GetInt16(i) < 0, Convert.ToUInt16(0), Convert.ToUInt16(DataReader.GetInt16(i)))

ElseIf typeOfT Is GetType(UInt32) Then

rtn = Misc.Strings.IIF(Of UInt32)(DataReader.GetInt32(i) < 0, Convert.ToUInt32(0), Convert.ToUInt32(DataReader.GetInt32(i)))

ElseIf typeOfT Is GetType(UInt64) Then

rtn = Misc.Strings.IIF(Of UInt64)(DataReader.GetInt64(i) < 0, Convert.ToUInt64(0), Convert.ToUInt64(DataReader.GetInt64(i)))

Else

rtn = GetValue(i)

End If

Try

Return CType(rtn, T)

Catch ex As Exception

Throw New InvalidCastException("The data type: " & typeOfT.Name & " will not cast from the database value: " & GetDataTypeName(i) & "[" & GetFieldType(i).Name & "]." & NewLine() & "This method [" & System.Reflection.MethodBase.GetCurrentMethod().Name & "] in object [" & System.Reflection.MethodBase.GetCurrentMethod.ReflectedType.Name & "] is not supported.")

End Try

End Function

Blarm replied on Thursday, November 08, 2007

This is the sort of code I use with NULLs

Private mRatingViewings As Nullable(Of Int16) = Nothing

Public Property RatingViewings() As Nullable(Of Int16)
Get
CanReadProperty("RatingViewings", True)
Return mRatingViewings
End Get
Set(ByVal value As Nullable(Of Int16))
CanWriteProperty("RatingViewings", True)
If mRatingViewings.HasValue <> value.HasValue OrElse _
(mRatingViewings.HasValue AndAlso value.HasValue AndAlso
mRatingViewings.Value <> value.Value) Then
mRatingViewings = value
PropertyHasChanged("RatingViewings")
End If
End Set
End Property

DataPortal_Fetch:
If Not dr.IsDBNull("RatingViewings") Then mRatingViewings = dr.GetInt16("RatingViewings")

DoInsertUpdate:
cmd.Parameters.AddWithValue("@RatingViewings", IIf(Not mRatingViewings.HasValue, DBNull.Value, mRatingViewings))

I am hoping to build this as an option into CSALGen at some time as I use frequently use NULLs in the database

Bill

ward0093 replied on Friday, November 09, 2007

Hey Bill...

You may know this already... but i found out that if your SP has a default value already set (i.e. @RatingViewings as smallint = null)

then you can skip the IIF check:

cmd.Parameters.AddWithValue("@RatingViewings", mRatingViewings)

just a thought... i did it the same thing for the longest time but discovered you didn't have to if a Default Value was established.

ward0093

ward0093 replied on Friday, November 09, 2007

oops... obviously, only with Nullable Values (i.e. Private mRatingViewings as Nullable(of int16) = nothing)

ward0093

Blarm replied on Sunday, November 11, 2007

Thanks ward I'll give that a try.

Bill