The Only Data Access Layer You'll Ever Need?, Page 3

Writing a One-Size-Fits-Many Reader

Another benefit to the DataAccess class is that developers can handle basic reads with a single read event handler that uses reflection. Because the example defined the DataAccess methods as generic methods also, the same read can read one object or a collection of objects. To support non-composite reads of single objects or collections, I added methods to the DataAccess class (see Listing 4).

Listing 4: Added Methods to the DataAccess Class

Public Shared Function SafeRead(Of T)(ByVal field As T, _
   ByVal reader As IDataReader, ByVal name As String) As T

   If (reader(name).Equals(System.DBNull.Value) = False) Then
      Dim result As Object = reader(name)
      Return CType(Convert.ChangeType(result, GetType(T)), T)
   Else
      Return field
   End If

End Function


' read the public properties and use these to read field names
Public Shared Function OnReadAny(Of T As New)(ByVal reader _
   As IDataReader) As T

   Dim genericType As Type = GetType(T)

   Dim properties() As PropertyInfo = _
      genericType.GetProperties(BindingFlags.Instance _
      Or BindingFlags.Public)

   Dim prop As PropertyInfo
   Dim obj As T = New T()

   For Each prop In properties

      Try
         Dim columnName As String = GetColumnName(prop)

         If (reader(columnName).Equals(System.DBNull.Value) = False) Then
            Dim value As Object = reader(columnName)
            prop.SetValue(obj, value, Nothing)
         End If

      Catch ex As Exception
         Debug.WriteLine("Couldn't write " + prop.Name)
      End Try

   Next

   Return obj
End Function

Private Shared Function GetColumnName(ByVal prop As PropertyInfo) _
   As String

   Debug.Assert(prop Is Nothing = False)
   If (prop Is Nothing) Then Return ""

   Dim attributes() As Object = prop.GetCustomAttributes(True)
   Dim attr As Object
   For Each attr In attributes
      If (TypeOf attr Is MYVB.BusinessObjects.ColumnNameAttribute) Then
         Debug.WriteLine("Uses ColumnNameAttribute")
         Return CType(attr, _
            MYVB.BusinessObjects.ColumnNameAttribute).ColumnName
      End If
   Next

   Return prop.Name

End Function


' you can do this - no appreciable difference between this and
' non-reflected version. if db field names don't match public
' properties, you can use an attribute to indicate the field
' name
Public Shared Function OnReadAnyList(Of T As New)(ByVal reader _
   As IDataReader) As List(Of T)
   If (reader Is Nothing) Then Return New List(Of T)()

   Dim list As List(Of T) = New List(Of T)()

   While (reader.Read())
      list.Add(OnReadAny(Of T)(reader))
   End While

   Return list
End Function

Note that I added a method called GetColumnName. This method looks for custom attributes. By default, the code in Listing 4 looks for column names that match property names, but this may not always be the case. You can manually map columns to properties with a custom attribute.

I know that code that uses reflection may be slower than code that doesn't, but I tested this code and the reflection did not seem noticeably slower than non-Reflective readers.

Creating the Custom Column-Matching Attribute

Listing 5 defines a custom attribute that permits me to specify a column name for a property. You can use this attribute when you want a property name like CustomerName and some helpful DBA creates a database column like CustNm (or some such nonsense).

Listing 5: A Custom Attribute to Tag a Property with a Mismatched Column Name

<AttributeUsage(AttributeTargets.Property, _
AllowMultiple:=False, Inherited:=True)> _
Public Class ColumnNameAttribute
   Inherits System.Attribute

   Private FColumnName As String

   Public Sub New(ByVal columnName As String)
      Me.FColumnName = columnName
   End Sub

   Public Property ColumnName() As String
      Get
         Return FColumnName
      End Get
      Set(ByVal value As String)
         FColumnName = value
      End Set
   End Property

End Class

Tip: Use whole words and complete names for things as much as possible, and be wary of any code, tool, or language that does not support real words.

Reviewing Write Behaviors

Write behaviors work pretty much like reads. You need to use the factory to create a parameter object for every field you'd like to write. If you are writing composite objects, you custom write a class (for example, the CustomerAccess class) and will need to kick open a connection and create a transaction. You can manage inserts versus updates by examining the primary key field in the stored procedure. And, of course, you will need to write your own stored procedures.