Data Access Using Dynamics - Part I: The DynamicDataReader

MBigglesworth79

5.00/5 (2 votes)

25 May 2011CPOL3 min read

20.5K

The DynamicDataReader provides a means of retrieving data from a database in a way that involves less code and increases code readability.

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:

Shrink ▲

// C#
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"));
        // Do some stuff with the data.
    }
}

VB.NET

Shrink ▲

' Visual Basic
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"))
        ' Do some stuff with the data.
    End While
End Using

C#
VB.NET

// C#
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"));
        // Do some stuff with the data.
    }
}

Shrink ▲

' Visual Basic
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"))
        ' Do some stuff with the data.
    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:

Shrink ▲

// C#
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;
    }
}

VB.NET

Shrink ▲

' Visual Basic
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

C#
VB.NET

Shrink ▲

// C#
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;
    }
}

Shrink ▲

' Visual Basic
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:

Shrink ▲

// C#
public abstract class DynamicEnumerableDataObjectWrapper<T> : 
       DynamicDataObjectWrapper<T>, IEnumerable
       where T : IEnumerable
{
    public DynamicEnumerableDataObjectWrapper(T obj)
        : base(obj)
    {
    }

    public virtual IEnumerator GetEnumerator()
    {
        return Obj.GetEnumerator();
    }
}

VB.NET

Shrink ▲

' Visual Basic
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

C#
VB.NET

// C#
public abstract class DynamicEnumerableDataObjectWrapper<T> : 
       DynamicDataObjectWrapper<T>, IEnumerable
       where T : IEnumerable
{
    public DynamicEnumerableDataObjectWrapper(T obj)
        : base(obj)
    {
    }

    public virtual IEnumerator GetEnumerator()
    {
        return Obj.GetEnumerator();
    }
}

Shrink ▲

' Visual Basic
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:

Shrink ▲

// C#
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);
    }
}

VB.NET

Shrink ▲

' Visual Basic
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

C#
VB.NET

Shrink ▲

// C#
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);
    }
}

Shrink ▲

' Visual Basic
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:

Shrink ▲

// C#
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;
        }
    }
}

VB.NET

Shrink ▲

' Visual Basic
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

C#
VB.NET

Shrink ▲

// C#
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;
        }
    }
}

Shrink ▲

' Visual Basic
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:

ASP.NET

Shrink ▲

<!-- ASP.NET -->
<form id="form1" runat="server">
<div>
    <h1>
        GridView Examples</h1>
    <h2>
        Using DynamicDataReader</h2>
    <asp:GridView ID="dataReaderGridView" runat="server" />    
</div>
</form>

Shrink ▲

// C#
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();
    }
}

VB.NET

Shrink ▲

' Visual Basic
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

<!-- ASP.NET -->
<form id="form1" runat="server">
<div>
    <h1>
        GridView Examples</h1>
    <h2>
        Using DynamicDataReader</h2>
    <asp:GridView ID="dataReaderGridView" runat="server" />    
</div>
</form>

Shrink ▲

// C#
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();
    }
}

Shrink ▲

' Visual Basic
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:

Shrink ▲

// C#
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();
    }
}

VB.NET

Shrink ▲

' Visual Basic
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

C#
VB.NET

Shrink ▲

// C#
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();
    }
}

Shrink ▲

' Visual Basic
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)