Microsoft & .NET.NETBetter Entities with Nullable Types

Better Entities with Nullable Types

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

There is an impedance mismatch between objects and normalized relational databases. Everyone knows this. Almost everyone has tried to fix it—think object-oriented databases—and some people have gotten famous off of proposed solutions. (Deservedly so or not.) Well, truth be told Microsoft is working on an ADO.NET Entity Framework to address this issue. The issue phrased using my words is getting data from databases into objects makes for better programming but is time consuming, tedious, and not altogether convenient. From what I know of LINQ, LINQ for Data, and the Entity Framework, they may really be on to something.

In the meantime, you have applications to write. To that end, this article is designed to give you some ideas about how to take a lot of the work out of creating an entity framework (or data access layer, if you prefer) by using some of the newer features in .NET such as generics and nullable types and some staples, like Reflection.

Using Nullable Fields for Entities

One part of the impedance mismatch is that databases support nulls for things like integers, but the .NET framework didn’t. Hence, when anyone reads a database column, you have to check for Nothing. This is a pain in the neck and often leads to bugs when populating entities from a database table.

Note: An entity is traditionally a database table. An entity class is generally a class that represents a database table. Most software domains have several entity classes for persisted data and two or three times as many non-entity classes. These are generalities. No software is good or bad based on these simplistic ratios.

Integers couldn’t traditionally be nulls (or Nothing) because they were value types and value types are not references like strings. With the Nullable(Of T) class, you now can declare fields as Nullable; this means an integer field can be declared as Nullable(Of Integer) and assigned null or a valid integer number. This takes some of the work out of populating entity classes from database entities (tables) that have null values.

Note: If you are new to generics—also called template or parameterized types—the most important thing to know is that generic types separate algorithm from type. The parameter T is used to mean that the class provides the behavior and you provide the type T.

The Nullable(Of T) class works by using a couple of methods and overloaded operators, and it does essentially what you or I would do: If the Nullable variable has a value for T, that is returned; otherwise, some equivalent default value is returned. When you write code like (Listing 1)

Listing 1: A simple console application that introduces nullable types.

Module Module1

   Sub Main()

      Dim I As Nullable(Of Integer)
      I = 5
      Console.WriteLine(I)
      Console.ReadLine()

   End Sub

End Module

I is the nullable integer type. That statement assigning 5 to I actually calls Nullable.Operator CType(ByVal value as T) As T?, which is a conversion operator converting T—in this case an Integer—into a Nullable (Of T). Listing 2 shows the disassembled conversion operator (disassembled by Reflector).

Listing 2: Assigning a non-nullable to a nullable invokes a shared conversion operator converting T to T?. T? is C#’s shorthand notation for nullable types.

Public Shared Widening Operator CType(ByVal value As T) As T?
   Return New T?(value)
End Function

You can use the generic Nullable class to define an entity class for the Northwind Employees table. You don’t need Nullable for nvarchars (basically strings), but you can use them for value types such as Integers and DateTime values. Listing 3 demonstrates an abridged entity class for the Employees table, called Employee.

Listing 3: The Employee class using Nullable types.

Public Class Employee

   Private FEmployeeID As Nullable(Of Integer)
   Public Property EmployeeID() As Nullable(Of Integer)
      Get
         Return FEmployeeID
      End Get
      Set(ByVal Value As Nullable(Of Integer))
         If (Value.HasValue = False) Then
            Console.WriteLine("Employee ID is null")
         End If
         FEmployeeID = Value
      End Set
   End Property

   Private FLastName As String
   Public Property lastName() As String
      Get
         Return FLastName
      End Get
      Set(ByVal Value As String)
         FLastName = Value
      End Set
   End Property

   Private FFirstName As String
   Public Property FirstName() As String
      Get
         Return FFirstName
      End Get
      Set(ByVal Value As String)
         FFirstName = Value
      End Set
   End Property

   Private FBirthDate As Nullable(Of DateTime)
   Public Property BirthDate() As Nullable(Of DateTime)
      Get
         Return FBirthDate
      End Get
      Set(ByVal Value As Nullable(Of DateTime))
         FBirthDate = Value
      End Set
   End Property

   Private FPhoto As Byte()
   Public Property Photo() As Byte()
      Get
         Return FPhoto
      End Get
      Set(ByVal Value As Byte())
         FPhoto = Value
      End Set
   End Property


   Public Overrides Function ToString() As String
      Return String.Format("Employee: {0} {1}, {2} is in {3}", _
         FEmployeeID, FLastName, FFirstName, FRegion)
   End Function

   Private FRegion As String
   Public Property Region() As String
      Get
         Return FRegion
      End Get
      Set(ByVal Value As String)
         FRegion = Value
      End Set
   End Property

End Class

In the listing, EmployeeID and BirthDate are nullable types. All of the other types are strings or the photo which is a byte array, both of which can already be assigned to null (Nothing). (In real life a primary key will likely only be null when you create a new entity and employee records generally have birth dates, but the listing demonstrates how to apply the Nullable class.)

Now, if you happen to read a null value for the BirthDate or EmployeeID, you can safely assign it to the field without all of that flabberlanche code, checking for Nothing. Unfortunately, Nullable(Of Date) can be assigned Nothing but Nullable(Of Date) cannot be assigned System.DBNull.Value. So, you have solved one problem, but you need a solution for DBNull too.

Implementing a SafeRead(Of T) Method

Generic classes and methods are now used all over the place in .NET. Generics are useful. Any time you can find a general algorithm that will work on a variety of types, use Generics.

In my daily work, I routinely use macros, snippets, CodeRush, and CodeDOM code generators. By establishing a pattern of completing a task, it makes it easier to automate that task, or at least do it very quickly. For this reason, the SafeRead(Of T) method was developed (by me).

SafeRead is implemented to accept a type, some kind of ADO.NET container such as a DataRow, a field name, and a default value. Then, SafeRead attempts to read the field as long as it isn’t null. If the field contains a null, the default value is used. Instead of code that looks like this:

Dim v As Object = reader("EmployeeID")
Dim employeeID As Integer
If (v Is Nothing Or v Is System.DBNull.Value) Then
   employeeID = Convert.ToInt32(v)
Else
   employeeID = -1    ' default value
End If

repeated over and over, the checking code is converged into a single method and the workload is pleasantly reduced. Listing 4 contains my implementation of SafeRead(Of T).

Listing 4: A generic method named SafeRead that takes the grunt work out of reading field values and checking for null.

Public Shared Function SafeRead(Of T)(ByVal fieldname As String, _
   ByVal reader As SqlDataReader, ByVal defaultValue As T) As T

   Dim v As Object = reader(fieldname)
   If (v Is Nothing Or v Is System.DBNull.Value) Then
      Return defaultValue
   Else
      Return Convert.ChangeType(v, GetType(T))
   End If
End Function

First, you read the field. In this example, from a SqlDataReader. (You can extend SafeRead to accept an IDataReader, making SafeRead provider agnostic.) Then, you check for Nothing or DBNull. If the field is any variation of nothingness, the default value you provided is returned. If the data is good, you use Convert.ChangeType and the parameterized type T to convert to the correct data type.

It is worth noting here that VB permits you to get loosey goosey with data types, and you probably can get away with assigning the Object type from an indexed SqlDataReader to a specific type. However, it is worth noting that being specific is a more portable and reliable way to write code in general. To use SafeRead, call it with parameters that look like this:

Dim employeeID As Integer = _
   SafeRead(Of Integer)("EmployeeID", reader, -1)

Creating a Field Descriptor Attribute

Suppose that you want to eliminate some more overhead for building entity types. For example, you could use metadata to provide the field name, field type, and default value. Then, each entity’s field can carry with it the information it needs for SafeRead. Listing 5 contains an implementation of a custom attribute named FieldDescriptorAttribute.

Listing 5: A custom attribute that can be used to assist with reading the entity fields.

<AttributeUsage(AttributeTargets.Property)> _
Public Class FieldDescriptorAttribute
   Inherits Attribute

   ''' <summary>
   ''' Initializes a new instance of the FieldDescriptorAttribute
   ''' class.
   ''' </summary>
   ''' <param name="fieldName"></param>
   ''' <param name="fieldType"></param>
   Public Sub New(ByVal fieldName As String, _
      ByVal fieldType As Type, ByVal defaultValue As Object)
      FFieldName = fieldName
      FFieldType = fieldType
      If (FFieldType Is GetType(DateTime)) Then
         FDefaultValue = DateTime.MinValue
      Else
         FDefaultValue = defaultValue
      End If
   End Sub

   Private FFieldName As String
   Public Property FieldName() As String
      Get
         Return FFieldName
      End Get
      Set(ByVal Value As String)
         FFieldName = Value
      End Set
   End Property

   Private FFieldType As Type
   Public Property FieldType() As Type
      Get
         Return FFieldType
      End Get
      Set(ByVal Value As Type)
         FFieldType = Value
      End Set
   End Property

   Private FDefaultValue As Object
   Public Property DefaultValue() As Object
      Get
         Return FDefaultValue
      End Get
      Set(ByVal Value As Object)
         FDefaultValue = Value
      End Set
   End Property
End Class

The custom attribute class FieldDescriptorAttribute is simply a container for arguments to SafeRead. The only noteworthy aspect is that I check for DateTime and set the default value in the custom attribute because I couldn’t remember (or figure out) how to initialize an attribute with a literal Date. (If any of you smart readers know how to do this, drop me a line at pkimmel@softconcepts.com.) Having defined the FieldDescriptorAttribute, you can define a custom entity class (based on any table, but Employees was used) and adorn the property/entity fields with the attribute (see Listing 6).

Listing 6: An employee class that uses the FieldDescriptorAttribute, which can be used by SafeRead with Reflection.

Public Class Employee

   Private FEmployeeID As Nullable(Of Integer)
   <FieldDescriptor("EmployeeID", GetType(Integer), -1)> _
   Public Property EmployeeID() As Nullable(Of Integer)
      Get
         Return FEmployeeID
      End Get
      Set(ByVal Value As Nullable(Of Integer))
         If (Value.HasValue = False) Then
            Console.WriteLine("Employee ID is null")
         End If
         FEmployeeID = Value
      End Set
   End Property

   Private FLastName As String
   <FieldDescriptor("LastName", GetType(String), "")> _
   Public Property lastName() As String
      Get
         Return FLastName
      End Get
      Set(ByVal Value As String)
         FLastName = Value
      End Set
   End Property

   Private FFirstName As String
   <FieldDescriptor("FirstName", GetType(String), "")> _
   Public Property FirstName() As String
      Get
         Return FFirstName
      End Get
      Set(ByVal Value As String)
         FFirstName = Value
      End Set
   End Property

   Private FBirthDate As Nullable(Of DateTime)
   <FieldDescriptor("BirthDate", GetType(DateTime), Nothing)> _
   Public Property BirthDate() As Nullable(Of DateTime)
      Get
         Return FBirthDate
      End Get
      Set(ByVal Value As Nullable(Of DateTime))
         FBirthDate = Value
      End Set
   End Property

   Private FPhoto As Byte()
   <FieldDescriptor("Photo", GetType(Byte()), New Byte() {0})> _
   Public Property Photo() As Byte()
      Get
         Return FPhoto
      End Get
      Set(ByVal Value As Byte())
         FPhoto = Value
      End Set
   End Property


   Public Overrides Function ToString() As String
      Return String.Format("Employee: {0} {1}, {2} is in {3}", _
         FEmployeeID, FLastName, FFirstName, FRegion)
   End Function

   Private FRegion As String
   <FieldDescriptor("Region", GetType(String), "(unk)")> _
   Public Property Region() As String
      Get
         Return FRegion
      End Get
      Set(ByVal Value As String)
         FRegion = Value
      End Set
   End Property

End Class

Reading Entities Using Reflection

Writing code to read straightforward entities is about as boring as programming gets (other than writing comments). To speed up development time and relieve boredom, you can combine Generics, Reflection, and custom attributes and write a universal entity and entity list builder.

Listing 7 contains a one-size fits-all data access class. As it is, this class will only build entities and lists of entities, but it will build any entity. If you externalize the connection string and use the System.Data ADO.NET interfaces, this same code will build entities and list of entities for almost any database and any table.

Listing 7: A generic DataAccess class that uses Generics, custom attributes, and Reflection to read almost any entity.

Public Class DataAccess
   Private Const connectionString As String = _
      "Data Source=localhost;Initial Catalog=Northwind;" + _
      "Integrated Security=True"

   Public Shared Function GetList(Of T As New, _
      R As New)(ByVal tablename As String) As T

      Using connection As SqlConnection = _
         New SqlConnection(connectionString)
         Dim command As SqlCommand = _
            New SqlCommand("SELECT * FROM " + tablename, connection)
         connection.Open()
         Dim reader As SqlDataReader = command.ExecuteReader
         Return Create(Of T, R)(reader)
      End Using

   End Function
   Public Shared Function GetEmployeeList() As EmployeeList
      Return GetList(Of EmployeeList, Employee)("Employees")
   End Function

   Private Shared Function Create(Of T As New, U As New)( _
      ByVal reader As SqlDataReader) As T

      Dim list As IList(Of U)
      Dim gt As T = New T()
      list = gt

      While (reader.Read())
         list.Add(Create(Of U)(reader))
      End While

      Return list
   End Function

   Private Shared Function Create(Of U As New)( _
      ByVal reader As SqlDataReader) As U

      Dim o As U = New U()

      ' get the attributes and use them to read
      Dim type As Type = GetType(U)
      Dim properties() As PropertyInfo = type.GetProperties()

      ' for each field if it as a field descriptor we can read it
      For Each p As PropertyInfo In properties

         Dim attributes() As Object = _
            p.GetCustomAttributes(GetType(FieldDescriptorAttribute), _
               False)

         For Each attr As Object In attributes

            If (TypeOf attr Is FieldDescriptorAttribute) Then
               Dim descriptor As FieldDescriptorAttribute = _
                  CType(attr, FieldDescriptorAttribute)

               Dim method As MethodInfo = _
                  GetType(DataAccess).GetMethod("SafeRead")
               method = method.MakeGenericMethod(descriptor.FieldType)
               Dim val As Object = method.Invoke(Nothing, _
                  New Object() _
                  {descriptor.FieldName, reader, _
                   descriptor.DefaultValue})
               p.SetValue(o, val, Nothing)
               Exit For

            End If
         Next
      Next

      Return o
   End Function

   Public Shared Function SafeRead(Of T)(ByVal fieldname As String, _
      ByVal defaultValue As T) As T

      Dim v As Object = reader(fieldname)
      If (v Is Nothing Or v Is System.DBNull.Value) Then
         Return defaultValue
      Else
         Return Convert.ChangeType(v, GetType(T))
      End If
   End Function

End Class

The first function GetList requires two parameterized arguments, T and R, that implement a blank constructor, Sub New. T is the entity list type, like EmployeeList; U is the entity type, like Employee. The GetList shared function requires the table name to read from.

The second shared method, Create, organizes constructing the list, iterating over the reader, and constructing the entities that end up in the list. The third method does all of the heavy lifting; it creates an instance of the entity class indicated by the parameter U. Next, you get all of the properties for the entity and request the FieldDescriptorAttribute for the properties. (Whew!) For each property, you use the FieldDescriptorAttribute, and construct an instance of the generic method SafeRead using the FieldDescriptorAttribute’s knowledge about each field to initialize the field.

Some wisenheimer is going to write and talk about performance with all of this Reflection. Performance might be slightly slower, but hardware is cheap; labor is not. If you find performance isn’t where it needs to be, you can always implement code that doesn’t use Reflection, but make sure the performance really matters and is really noticeably slower. To test the code, you can use the Main sub routine shown in Listing 8.

Listing 8: A simple console application to test the code.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Threading
Imports System.Reflection

Module Module1

   Sub Main()

      Dim emp As Employee = New Employee
      emp.BirthDate = Nothing

      Dim list As EmployeeList = DataAccess.GetEmployeeList
      For Each e As Employee In list
         Console.WriteLine(e)
      Next

      Console.ReadLine()

      For Each  c As Customer In DataAccess.GetList( _
         Of CustomerList, Customer)("Customers")
         Console.WriteLine(c)
      Next

      Console.ReadLine()

   End Sub
      ... ' elided

Inheriting from List(Of T)

It isn’t necessary to create a CustomerList that inherits from List(Of Customer) or to create any subtype. A reason to do it is to support adding additionally capabilities to the new List class. In real programming, you will need helper methods and these can be added only to classes you create.

Note: With Orcas—the next version of .NET—extension methods are supported. Extension methods permit adding capabilities to an existing type. To that end, you may be able to use List(Of Customer), for example, without inheritance and add new capabilities using extension methods. Check back here for more on extension methods in coming weeks.

Summary

There are a lot of meaty bits in this article. You can use Nullable types to make fields and properties accept nulls. You can use custom attributes, Generics, and Reflection to write a one-size fits-all entity populating engine. Whether you can employ one or all of these techniques on a project depends on your judgment, but if you can, you will eliminate writing a lot of mundane code.

It is important to acknowledge that many people use ADO.NET and DataSets. This approach can work, but I seldom use it. I prefer the control provided by custom classes. It is also important to note that the impedance mismatch between normalized tables and rows and actual objects is a pickle that Microsoft is trying to solve. Check back later for more on the ADO.NET Entity Framework; we’ll see what Redmond comes up with together.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his new book UML DeMystified from McGraw-Hill/Osborne. Paul is a software architect for Tri-State Hospital Supply Corporation. You may contact him for technology questions at pkimmel@softconcepts.com.

If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org.

Copyright © 2007 by Paul T. Kimmel. All Rights Reserved.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories