A Trip Down Memory Lane
Way back in the mists of time, before the rise of ORMs such as Entity Framework, LINQ-to-SQL, or NHibernate, data access was typically performed using the ADO.NET DataReader and DataSet classes. To most .NET developers, I am sure the following scenario will be familiar:
using (SqlConnection connection = new SqlConnection(
ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString))
{
string sql = "SELECT SomeColumn, AnotherColumn FROM SomeTable";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int foo = reader.GetInt32(reader.GetOrdinal("SomeColumn"));
string bar = reader.GetString(reader.GetOrdinal("AnotherColumn"));
}
}
Using connection As SqlConnection = _
New SqlConnection(ConfigurationManager.ConnectionStrings(_
"MyDatabase").ConnectionString)
Dim sql As String = "SELECT SomeColumn, AnotherColumn FROM SomeTable"
Dim command As SqlCommand = New SqlCommand(sql, connection)
connection.Open()
Dim reader As IDataReader = command.ExecuteReader()
While reader.Read()
Dim foo As Integer = reader.GetInt32(reader.GetOrdinal("SomeColumn"))
Dim bar As String = reader.GetString(reader.GetOrdinal("AnotherColumn"))
End While
End Using
Now recently, whilst having to work directly with the ADO.NET classes for the first time in a very long time, I decided to experiment with trying to leverage the Dynamic Language Runtime (DLR) to create a set of loosely-typed data-access classes which can be used in a more object-oriented manner and hopefully aid code readability to boot.
The Dynamic DataReader
In this article, we are going to look at the DynamicDataReader class, which acts as a dynamic wrapper around the System.Data.Common.DbDataReader class. However, before we start looking at the code for this class in great detail, let's take a look at one of its parent classes: the DynamicDataWrapper class:
public abstract class DynamicDataObjectWrapper<T> : DynamicObject
{
protected T Obj { get; private set; }
protected Type ObjType { get; private set; }
public DynamicDataObjectWrapper(T obj)
{
this.Obj = obj;
this.ObjType = obj.GetType();
}
public override bool TryInvokeMember(InvokeMemberBinder binder,
object[] args, out object result)
{
try
{
result = ObjType.InvokeMember(binder.Name,
BindingFlags.InvokeMethod | BindingFlags.Instance |
BindingFlags.Public, null, Obj, args);
return true;
}
catch (Exception)
{
result = null;
return false;
}
}
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
PropertyInfo property = ObjType.GetProperty(binder.Name,
BindingFlags.Instance | BindingFlags.Public);
if (property != null)
{
result = property.GetValue(Obj, null);
return true;
}
else
{
result = null;
return false;
}
}
public override bool TrySetMember(SetMemberBinder binder, object value)
{
PropertyInfo property = ObjType.GetProperty(binder.Name,
BindingFlags.Instance | BindingFlags.Public);
if (property != null)
{
property.SetValue(Obj, value, null);
return true;
}
else
return false;
}
}
Public MustInherit Class DynamicDataObjectWrapper(Of T)
Inherits DynamicObject
Private _obj As T
Private _objType As Type
Public Sub New(ByVal obj As T)
_obj = obj
_objType = obj.GetType()
End Sub
Protected ReadOnly Property Obj As T
Get
Return _obj
End Get
End Property
Protected ReadOnly Property ObjType As Type
Get
Return _objType
End Get
End Property
Public Overrides Function TryInvokeMember(binder As System.Dynamic.InvokeMemberBinder, _
args() As Object, ByRef result As Object) As Boolean
Try
result = ObjType.InvokeMember(binder.Name, _
BindingFlags.InvokeMethod Or BindingFlags.Instance Or _
BindingFlags.Public, Nothing, Obj, args)
Return True
Catch ex As Exception
result = Nothing
Return False
End Try
End Function
Public Overrides Function TryGetMember(binder As _
System.Dynamic.GetMemberBinder, ByRef result As Object) As Boolean
Dim _property As PropertyInfo = ObjType.GetProperty(binder.Name, _
BindingFlags.Instance Or BindingFlags.Public)
If Not _property Is Nothing Then
result = _property.GetValue(Obj, Nothing)
Return True
Else
result = Nothing
Return False
End If
End Function
Public Overrides Function TrySetMember(binder As _
System.Dynamic.SetMemberBinder, value As Object) As Boolean
Dim _propety As PropertyInfo = ObjType.GetProperty(binder.Name, _
BindingFlags.Instance Or BindingFlags.Public)
If Not _propety Is Nothing Then
_propety.SetValue(Obj, value, Nothing)
Return True
Else
Return False
End If
End Function
End Class
This class acts as dynamic wrapper for any non-dynamic object and, as such, inherits from DynamicObject. The wrapper overrides the TryGetMember(), TrySetMember(), and TryInvokeMember methods such that any calls made on the dynamic object are passed to the underlying wrapped object. For example, if a call is made to a method called Foo() on the dynamic object, the Foo() method (if it exists) is called on the wrapped object.
Extending this is the DynamicEnumerableDataObjectWrapper, which is a subclass of DynamicDataObjectWrapper which implements the IEnumerable interface by passing calls to the GetEnumerator() method of the wrapped object:
public abstract class DynamicEnumerableDataObjectWrapper<T> :
DynamicDataObjectWrapper<T>, IEnumerable
where T : IEnumerable
{
public DynamicEnumerableDataObjectWrapper(T obj)
: base(obj)
{
}
public virtual IEnumerator GetEnumerator()
{
return Obj.GetEnumerator();
}
}
Public MustInherit Class DynamicEnumerableDataObjectWrapper(Of T As IEnumerable)
Inherits DynamicDataObjectWrapper(Of T)
Implements IEnumerable
Public Sub New(ByVal obj As T)
MyBase.New(obj)
End Sub
Public Overridable Function GetEnumerator() As IEnumerator _
Implements IEnumerable.GetEnumerator
Return Obj.GetEnumerator()
End Function
End Class
The DynamicDataReader class is, in turn, a concrete implementation of DynamicEnumerableDataObjectWrapper which wraps a DbDataReader object:
public class DynamicDataReader : DynamicEnumerableDataObjectWrapper<DbDataReader>
{
public DynamicDataReader(DbDataReader reader)
: base(reader)
{
}
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
if (base.TryGetMember(binder, out result))
return true;
else
{
try
{
if (!Obj.IsDBNull(Obj.GetOrdinal(binder.Name)))
result = Obj.GetValue(Obj.GetOrdinal(binder.Name));
else
result = null;
return true;
}
catch (Exception)
{
result = null;
return false;
}
}
}
public override bool TryGetIndex(GetIndexBinder binder,
object[] indexes, out object result)
{
try
{
object index = indexes[0];
if (index is int)
{
int intIndex = (int)index;
if (!Obj.IsDBNull(intIndex))
result = Obj.GetValue(intIndex);
else
result = null;
return true;
}
else if (index is string)
{
string strIndex = (string)index;
if (!Obj.IsDBNull(Obj.GetOrdinal(strIndex)))
result = Obj.GetValue(Obj.GetOrdinal(strIndex));
else
result = null;
return true;
}
else
{
result = null;
return false;
}
}
catch(Exception)
{
result = null;
return false;
}
}
public static implicit operator DbDataReader(DynamicDataReader reader)
{
return reader.Obj;
}
public static explicit operator DynamicDataReader(DbDataReader reader)
{
return new DynamicDataReader(reader);
}
}
Public Class DynamicDataReader
Inherits DynamicEnumerableDataObjectWrapper(Of DbDataReader)
Public Sub New(ByVal reader As DbDataReader)
MyBase.New(reader)
End Sub
Public Overrides Function TryGetMember(binder As GetMemberBinder, _
ByRef result As Object) As Boolean
If MyBase.TryGetMember(binder, result) Then
Return True
Else
Try
If Not Obj.IsDBNull(Obj.GetOrdinal(binder.Name)) Then
result = Obj.GetValue(Obj.GetOrdinal(binder.Name))
Else
result = Nothing
End If
Return True
Catch ex As Exception
result = Nothing
Return False
End Try
End If
End Function
Public Overrides Function TryGetIndex(binder As GetIndexBinder, _
indexes() As Object, ByRef result As Object) As Boolean
Try
Dim index As Object = indexes(0)
If TypeOf (index) Is Integer Then
Dim intIndex As Integer = DirectCast(index, Integer)
If Not Obj.IsDBNull(intIndex) Then
result = Obj.GetValue(intIndex)
Else
result = Nothing
End If
Return True
ElseIf TypeOf (index) Is String Then
Dim strIndex As String = DirectCast(index, String)
If Not Obj.IsDBNull(Obj.GetOrdinal(strIndex)) Then
result = Obj.GetValue(Obj.GetOrdinal(strIndex))
Else
result = Nothing
End If
Return True
Else
result = Nothing
Return False
End If
Catch ex As Exception
result = Nothing
Return False
End Try
End Function
Public Shared Widening Operator CType(ByVal reader As _
DynamicDataReader) As DbDataReader
Return reader.Obj
End Operator
Public Shared Narrowing Operator CType(ByVal reader _
As DbDataReader) As DynamicDataReader
Return New DynamicDataReader(reader)
End Operator
End Class
Firstly, note how we override the TryGetMember() method again. In this override, we first call the base method to test whether the member exists on the wrapped object. If it does, we return its value; otherwise, we attempt to read the appropriate column from the wrapped data reader. If this fails (i.e., the named column does not exist), the method returns false, causing a RuntimeBinderException to be thrown at run-time.
Secondly, we also provide an override for the TryGetIndex() method, which allows us to retrieve column data using an indexer.
Also, note how we also provide two conversion operators for converting between the original object and its wrapper. This allows us to easily use our dynamic wrapper object in places where the framework is expecting the original wrapped object; and to quickly wrap an unwrapped object.
Some Examples
Here are some examples of our DynamicDataReader in action. All the examples use the Northwind database and are designed to show the DynamicDataReader in action. They are certainly not intended as an example of data-access best-practice (embedded SQL being a case in point)! Firstly, a custom HTTP handler to render a table of employees:
public class DataReaderExample : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
context.Response.Write("<html><body>");
context.Response.Write("<h1>Employees:</h1>");
context.Response.Write("<table><thead><tr>" +
"<th>Employee Number</th><th>" +
"Surname</th><th>First Name</th>" +
"<th>Date of Birth</th>
</tr></thead><tbody>");
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
{
string query = "SELECT EmployeeID, LastName, FirstName, " +
"Title, TitleOfCourtesy, BirthDate, HireDate, Address, " +
"City, Region, PostalCode, Country, HomePhone, " +
"Extension, Photo, Notes, ReportsTo, PhotoPath " +
"FROM dbo.Employees";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
dynamic reader = (DynamicDataReader)command.ExecuteReader();
while (reader.Read())
{
int employeeId = reader.EmployeeID;
string lastName = reader.LastName;
string firstName = reader.FirstName;
DateTime birthDate = reader.BirthDate;
context.Response.Write
(String.Format("<tr><td>{0}</td>" +
"<td>{1}</td><td>{2}</td><td>" +
"{3:dd/MM/yyyy}</td></tr>",
employeeId, lastName, firstName, birthDate));
}
}
context.Response.Write
("</tbody></table></body></html>");
}
public bool IsReusable
{
get
{
return false;
}
}
}
Public Class DataReaderExample
Implements System.Web.IHttpHandler
Sub ProcessRequest(ByVal context As HttpContext) _
Implements IHttpHandler.ProcessRequest
context.Response.ContentType = "text/html"
context.Response.Write("<html><body>")
context.Response.Write("<h1>Employees:</h1>")
context.Response.Write("<table><thead><tr>" & _
"<th>Employee Number</th><th>Surname</th>" & _
"<th>First Name</th><th>Date of Birth</th>" & _
"</tr></thead><tbody>")
Using connection As SqlConnection = New SqlConnection(_
WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
Dim query As String = "SELECT EmployeeID, LastName, FirstName, Title, " & _
"TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, " & _
"PostalCode, Country, HomePhone, Extension, " & _
"Photo, Notes, ReportsTo, PhotoPath " & _
"FROM dbo.Employees"
Dim command As SqlCommand = New SqlCommand(query, connection)
connection.Open()
Dim reader As Object = CType(command.ExecuteReader(), DynamicDataReader)
While (reader.Read())
Dim employeeId As Integer = reader.EmployeeID
Dim lastName As String = reader.LastName
Dim firstName As String = reader.FirstName
Dim birthDate As DateTime = reader.BirthDate
context.Response.Write(String.Format("<tr><td>{0}</td>" & _
"<td>{1}</td><td>{2}</td><td>{3:" & _
"dd/MM/yyyy}</td></tr>", employeeId, lastName, firstName, birthDate))
End While
End Using
context.Response.Write("</tbody></table></body></html>")
End Sub
ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
As our DynamicDataReader class implements IEnumerable, it can be used with the standard ASP.NET data controls. In this example, it is used as the data source for a GridView:
<form id="form1" runat="server">
<div>
<h1>
GridView Examples</h1>
<h2>
Using DynamicDataReader</h2>
<asp:GridView ID="dataReaderGridView" runat="server" />
</div>
</form>
private void BindDataReader()
{
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
{
string query = "SELECT CustomerID, CompanyName, ContactName, " +
"ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax " +
"FROM dbo.Customers";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
dynamic reader = (DynamicDataReader)command.ExecuteReader();
dataReaderGridView.DataSource = reader;
dataReaderGridView.DataBind();
}
}
Private Sub BindDataReader()
Using connection As SqlConnection = New SqlConnection(_
WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
Dim query As String = "SELECT CustomerID, CompanyName, ContactName, " & _
"ContactTitle, Address, City, Region, PostalCode, " & _
"Country, Phone, Fax FROM dbo.Customers"
Dim command As SqlCommand = New SqlCommand(query, connection)
connection.Open()
Dim reader As Object = CType(command.ExecuteReader(), DynamicDataReader)
dataReaderGridView.DataSource = reader
dataReaderGridView.DataBind()
End Using
End Sub
However, in most real-world multi-tier applications, we are probably most likely to be filling an object, or collection of objects, for use in the higher-level tiers of our system. The final example here shows the use of the DynamicDataReader class to fill an array of Customer objects:
public static Customer[] GetCustomers()
{
List<Customer> customers = new List<Customer>();
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
{
string query = "SELECT CustomerID, CompanyName, ContactName, " +
"ContactTitle, Address, City, Region, " +
"PostalCode, Country, Phone, Fax " +
"FROM dbo.Customers";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
dynamic reader = (DynamicDataReader)command.ExecuteReader();
while (reader.Read())
{
Customer customer = new Customer()
{
Id = reader.CustomerID,
Company = reader.CompanyName,
Name = reader.ContactName,
Title = reader.ContactTitle,
Address = reader.Address,
City = reader.City,
Region = reader.Region,
PostCode = reader.PostalCode,
Country = reader.Country,
Phone = reader.Phone,
Fax = reader.Fax
};
customers.Add(customer);
}
return customers.ToArray();
}
}
Public Shared Function GetCustomers() As Customer()
Dim customers As List(Of Customer) = New List(Of Customer)
Using connection As SqlConnection = New _
SqlConnection(WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
Dim query As String = "SELECT CustomerID, CompanyName, ContactName, " & _
"ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax " & _
"FROM dbo.Customers"
Dim command = New SqlCommand(query, connection)
connection.Open()
Dim reader As Object = CType(command.ExecuteReader(), DynamicDataReader)
While reader.Read()
Dim customer As Customer = New Customer() With _
{ _
.Id = reader.CustomerID, _
.Company = reader.CompanyName, _
.Name = reader.ContactName, _
.Title = reader.ContactTitle, _
.Address = reader.Address, _
.City = reader.City, _
.Region = reader.Region, _
.PostCode = reader.PostalCode, _
.Country = reader.Country, _
.Phone = reader.Phone, _
.Fax = reader.Fax _
}
customers.Add(customer)
End While
End Using
Return customers.ToArray()
End Function
Limitations
- If a database column has the same name as a property of the wrapped
DbDataReader class, it cannot be accessed using the dynamic properties of the wrapper. Instead, the column can be accessed via the indexer on the wrapper. - If the application is performance-critical, you may want to consider removing the Reflection calls to the wrapped object in the
TryGetMember() and TrySetMember() of the base DynamicDataObjectWrapper class.
Summary
The DynamicDataReader provides a means of retrieving data from a database in a way that involves less code and increases code readability.
The source code will be available for download with Part II.
Next: The DynamicDataSet
