none
Handling DbNull in value-type properties of business layer objects RRS feed

  • Question

  • I'm sure this is a very common topic for discussion, but I was hoping some of the forum members could explain the ways you handle NULL values with value-type properties.  Please be aware that I am only interested in how this is doen through a "roll your own" approach -- so I would prefer that solutions that involve ORM tools not be included, though an explanation of how they approach the issue would be appreciated.  For the sake of this discussion assume the following:

    1) A database table with a nullable int column "Foo".  The null value is necessary to indicate absence/presence of a value, and DBA does NOT want to use a "default" value to approximate null (like 0 or -1).

    2) A business object that reflects this DB table which has a property "Foo" of type Int32.  This value is used by the BL clients in both databinding and directly through code.


    I'm trying to keep the BL structure a little general/vague here so that answerers can fill on the blanks, so let's say there is a method that retrieves the record into a datatable/datarow (wherein a NULL value is DbNull).  How do we preserve that value of NULL within the BO?  Also, what are the ways that a client of the BL (e.g. UI application) can set Foo to either null or zero?

    I've been striggling with this a little bit, trying to think through the issue on my own, and the only way I can think of so far is that there is an explicit method on the BO that allows the client to set a property to Null...  with in turn would be handled by some kind of internal variable on the BO like a dictionary keyed to the column name.  So in addition to my asking for general advice, are there inherent risks to my approach?

    Thanks in advance,
    -DB
    Friday, February 5, 2010 1:42 PM

Answers

  • Apparently my having been relegated to the purgatory of VB in the 2.0 framework has sheltered me from truly useful new features like built-in nullable types that can act as reference types , starting with VB 9.0.  Here's a short article on the feature:

    http://bartdesmet.net/blogs/bart/archive/2007/08/30/visual-basic-9-0-feature-focus-nullable-types.aspx

    Man, am I really irritated now.  Management doesn't want to pony up for the handful of VS2008 TS licenses so I'm stuck with my anti-pattern for now.  Rrrrr.

    It's against my nature to mark my own response as answer but in this case I think this would be the answer here.  I do this with a full acknowledgement that the other contributors offered excellent advice that was much appreciated.

    • Marked as answer by Dig-Boy Friday, February 12, 2010 4:53 PM
    Friday, February 12, 2010 4:53 PM

All replies

  • That's exactly what nullable types are for.

    http://msdn.microsoft.com/en-us/library/1t3y8s4s%28VS.80%29.aspx
    Friday, February 5, 2010 3:02 PM
  • I am aware of nullable types, I find them terribly awkward though. 

    So I assume you are saying each property should be of a nullable type, so Property Foo As Nullable(Of Int32).  Are all UI controls able to bind correctly to nullable types?  In other words, will a textbox bound to a numeric type set Foo to DbNull when the text is blank?  Also, every time I want to use property Foo in code will I have to check HasValue() first?  I understand the BL itself can be written to accomodate nullables, but its the dynamic needs of various UIs that leave me wondering if using nullables is on par with a "work around".

    Can you elaborate a bit more please?

    Friday, February 5, 2010 3:35 PM
  • You can do:

    Property Foo as Int32?

    The "?" is a short-cut to Nullable(Of T). That saves much time.

    Yes, I agree with e36M3 that this one of the uses of nullable types. Almost all of the value types in my applications use nullables for two reasons:

    1) They can retain a null value for the database.

    2) I can determine if the user has not set the value. I see "not set" different from a 0 value, for example.

    Yes, binding works fine with nullable types.

    Yes, you often need to check .HasValue or is nothing.

    Hope this helps.


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Friday, February 5, 2010 3:51 PM
  • Hi Deborah.  I was just browsing around and came upon many article/threads suggesting that binding to, say, textboxes with nullable types is problematic.  For instance, here:

    http://stackoverflow.com/questions/376361/best-way-to-databind-a-winforms-control-to-a-nullable-type

    ...  and one solution is to try to implement a converter component into the form's designer, as here:

    http://www.thejoyofcode.com/Databinding_and_Nullable_types_in_WinForms.NET.aspx


    Are you saying that you do not experience any of the control binding issues described here?  If so, how?  I am working with VS2005, targeting 2.0 Framework, and my company needs it written in VB (so using the "?" style will not work for me).  I have already been creating somehwat simple custom controls, so I don't expect too much of a problem getting them to play well with nullables.

    And I guess I can see that the pain of checking HasValue constantly may be a necesary evil to be able to work with nullables. 
    Friday, February 5, 2010 4:00 PM
  • To be fair I have not used VB.NET in a while, but I would think you should be able to check Is Nothing on nullable type (structure) instead of having to use HasValue.  Not that it makes a huge difference but it brings it more in line with typical object work.  I also haven't used them in a binding scenario outside of the GridView that I can remember so perhaps there might be some issues you'd have to work through.  That said it's still probably less work than rolling your own, and certainly more straight forward being an industry "standard' approach.
    Friday, February 5, 2010 4:14 PM
  • Not sure I put value types in TextBoxes very often ...

    I often use nullables for enums, which I then bind to ComboBoxes. Don't recall ever having a problem with those.

    I also use them for foreign keys, which I never show in the UI.

    So maybe I never saw this problem because I was lucky enough to work for four years on an application that tracked the majority of numeric data via enum values.

    Thinking about it ... I did have a problem with the DatePicker because it does not understand the null. It worked OK (didn't generate an error), but could not default to "empty". You had to instead use the (very-non-user-friendly) checkbox.)

    If I have some free time ... maybe I will give it a try with TextBoxes to better see the issue.
    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Friday, February 5, 2010 4:15 PM
  • Hi Deborah.  I apprecite your willingness to discuss this further.  Here is a simple example to get started with.  The controls are built-in so you only need to create a new form and paste the code.  Remember -- this is a VB forms project :)

    I'm interested to see if there is an assumption I making here that is faulty (i.e. how to set up the class/property).

    When you run this code it locks up on the textbox when you clear it's text -- I believe this is a product of an internal attempt to convert to the nullable type (which of course fails).  However, if you change Test to type Int32 to wrap the nullable type (return ValueOrDefault), it still exhibits the same behavior.

    Any insight you have would be much appreciated.

    -DB
    Friday, February 5, 2010 5:03 PM
  • WHoops -- just realized no code got posted...
     
     
     
    Public Class Form1
    
        Private WithEvents Button1 As New Button
        Private WithEvents TextBox1 As New TextBox
        Private foo1 As New Foo
        Private WithEvents BindingSource1 As New System.Windows.Forms.BindingSource
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            BindingSource1.DataSource = foo1
            foo1.Test = 123
    
            Me.Controls.Add(Button1)
            Button1.Location = New Point(40, 40)
    
            Me.Controls.Add(TextBox1)
            TextBox1.Location = New Point(40, Button1.Bottom + 10)
            TextBox1.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.BindingSource1, "Test", True))
    
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'Should be empty string when textbox is empty -- but it locks up the control because value grab fails.
            MsgBox(foo1.Test.ToString)
        End Sub
    
    End Class
    
    Public Class Foo
        Private m_test As Nullable(Of Int32)
        Public Property Test() As Nullable(Of Int32)
            Get
                Return m_test
            End Get
            Set(ByVal value As Nullable(Of Int32))
                m_test = value
            End Set
        End Property
    End Class

     
     
    Friday, February 5, 2010 7:04 PM
  • Hi,


    what you can also take a look at is something called the null object pattern. I wrote a blog on it here



    Hope that solves atleast part of your problem

    HTH

    technocrat_aspire
    Sunday, February 7, 2010 3:59 PM
  • Hi technocrat.  I was not aware of that pattern -- it looks like something I could have used a few times in the past and I will definitely put that in the ol' toolbox.  In fact, in my current project there probably is a need to supply null-like objects for the client, so you may have provided me assistance with a different issue.

    However, I'm having a hard time seeing how it can be applied to my current situation simply because I am dealing with business objects that are already in a non-null state, and my issue is with distinguishing between value type properties that can also be "null", or DbNull specifically - value types just do not allow for this (obviously).  Am I missing something here?  If so, can you elaborate? 
    Sunday, February 7, 2010 7:10 PM
  • Apparently my having been relegated to the purgatory of VB in the 2.0 framework has sheltered me from truly useful new features like built-in nullable types that can act as reference types , starting with VB 9.0.  Here's a short article on the feature:

    http://bartdesmet.net/blogs/bart/archive/2007/08/30/visual-basic-9-0-feature-focus-nullable-types.aspx

    Man, am I really irritated now.  Management doesn't want to pony up for the handful of VS2008 TS licenses so I'm stuck with my anti-pattern for now.  Rrrrr.

    It's against my nature to mark my own response as answer but in this case I think this would be the answer here.  I do this with a full acknowledgement that the other contributors offered excellent advice that was much appreciated.

    • Marked as answer by Dig-Boy Friday, February 12, 2010 4:53 PM
    Friday, February 12, 2010 4:53 PM