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.