Introduction
Publishing is a small world. One of the first books I wrote (15 years ago) was written with Chris Denny in the role of editor. I’d like to welcome Chris to developer.com. He’ll be the editor for this column, but the mistakes are still mine. Welcome, Chris.
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.
