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.