BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage Articles MicroORM - A Dynamically Typed ORM for VB and C# in about 160 Lines

MicroORM - A Dynamically Typed ORM for VB and C# in about 160 Lines

Leia em Português

ORMs are all the rage these days for a very good reason; they can make developing database-driven applications quick and painless. But ORM frameworks are very opinionated, they expect developers to follow certain rules and are quite difficult to use when the rules are broken. One of the more common rules is that stored procedures must always return a single result set with a consistent list of columns. Unfortunately there are many stored procedures where the structure of the returned data varies greatly depending on its own internal logic. For example, a stored procedure may take a parameter that indicates which columns to return and another to say if it contains all the rows or just summaries of them. Or perhaps the stored procedure’s results vary depending on some internal flag and the application needs to examine the output to determine the structure at run time.

Faced with a well entrenched set of stored procedures that were simply not designed for the type of static modeling that ORM systems are based around, most .NET developers revert back to DataTables. But with .NET 4.0’s new found support for dynamic typing, another option presents itself. What if everything including the stored procedure’s name, the SQL parameters, and the resulting objects were all handled at runtime?

Here is some example code in both VB and C#. You will note that VB requires Option Strict to be off while C# liberally uses its new “dynamic” keyword.

VB

Using con As New SqlClient.SqlConnection(connectionString)

    Dim customer = con.CallSingleProc.CustomerSelect(AccountKey:=12345)

    Console.WriteLine(customer.FirstName & " " & customer.LastName)

 

    Dim orders As IList = con.CallListProc.OrderSearch(AccountKey:=12345, MinCreatedDate:=Now.AddDays(-7), MaxCreatedDate:=Now)

    Dim totalValue = Aggregate order In orders Into Sum(CDec(order.TotalOrderValue))

    Console.WriteLine("This customer ordered a total of $" & totalValue & " last week")

    For Each order In orders

        Console.WriteLine(vbTab & "Order Key: " & order.OrderKey & " Value: $" & order.TotalOrderValue)

    Next

 

End Using

 

C#

using (var con = new SqlConnection(connectionString))

{

    var customer = con.CallSingleProc().CustomerSelect(AccountKey: 12345);

    Console.WriteLine(customer.FirstName + " " + customer.LastName);

 

    IList<dynamic> orders = con.CallListProc().OrderSearch(AccountKey: 12345, MinCreatedDate: DateTime.Now.AddDays(-7), MaxCreatedDate: DateTime.Now);

    var totalValue = orders.Sum(order => (decimal)order.TotalOrderValue);

 

    Console.WriteLine("This customer ordered a total of $" + totalValue + " last week");

    foreach (var order in orders)

    {

        Console.WriteLine("\tOrder Key: " + order.OrderKey + " Value: $" + order.TotalOrderValue);

    }

}

This looks like fairly normal .NET code, but many of those methods and properties don’t actually exist. Here is that same code with the non-existent members highlighted.

VB

Using con As New SqlClient.SqlConnection(connectionString)

    Dim customer = con.CallSingleProc.CustomerSelect(AccountKey:=12345)

    Console.WriteLine(customer.FirstName & " " & customer.LastName)

 

    Dim orders As IList = con.CallListProc.OrderSearch(AccountKey:=12345, MinCreatedDate:=Now.AddDays(-7), MaxCreatedDate:=Now)

    Dim totalValue = Aggregate order In orders Into Sum(CDec(order.TotalOrderValue))

    Console.WriteLine("This customer ordered a total of $" & totalValue & " last week")

    For Each order In orders

        Console.WriteLine(vbTab & "Order Key: " & order.OrderKey & " Value: $" & order.TotalOrderValue)

    Next

 

End Using

C#

using (var con = new SqlConnection(connectionString))

{

    var customer = con.CallSingleProc().CustomerSelect(AccountKey: 12345);

    Console.WriteLine(customer.FirstName + " " + customer.LastName);

 

    IList<dynamic> orders = con.CallListProc().OrderSearch(AccountKey: 12345, MinCreatedDate: DateTime.Now.AddDays(-7), MaxCreatedDate: DateTime.Now);

    var totalValue = orders.Sum(order => (decimal)order.TotalOrderValue);

 

    Console.WriteLine("This customer ordered a total of $" + totalValue + " last week");

    foreach (var order in orders)

    {

        Console.WriteLine("\tOrder Key: " + order.OrderKey + " Value: $" + order.TotalOrderValue);

    }

} 

At this point some traditionalists will start complaining about the risks of late binding such as the chance of making a typo that won’t be caught until runtime. While that is certainly a possibility, we are not actually any worse off. When we keep stored procedure and column names in strings, we run the exact same risk of a runtime failure being linked back to a typo.

In order for this to work we need two things. The first is a way to switch from a statically typed context to a dynamically typed context. For this we choose a set of extension methods that return “System.Object”. In Visual Basic this is enough trigger late binding, but not C#. In order for C# to switch modes, you also have to decorate the return value with the Dynamic attribute.

Public Module MicroOrm

    ''' <summary>

    ''' Calls a stored procedure that returns a scalar value.

    ''' </summary>

    ''' <returns>Null or a single value</returns>

    ''' <remarks>Only the first column of the first row of the first result set is returned; all other data is ignored. Database nulls are converted into CLR nulls.</remarks>

    <Extension()>

    Public Function CallScalarProc(ByVal connection As SqlConnection) As <Dynamic()> Object

        Return New MicroProcCaller(connection, Scalar)

    End Function

 

    ''' <summary>

    ''' Calls a stored procedure that returns a single object.

    ''' </summary>

    ''' <returns>Null or a MicroDataObject</returns>

    ''' <remarks>Only the first row of the first result set is returned; all other data is ignored. Database nulls are converted into CLR nulls.</remarks>

    <Extension()>

    Public Function CallSingleProc(ByVal connection As SqlConnection) As <Dynamic()> Object

        Return New MicroProcCaller(connection, [Single])

    End Function

 

    ''' <summary>

    ''' Calls a stored procedure that returns a list of objects.

    ''' </summary>

    ''' <returns>A List of MicroDataObject. There is one MicroDataObject per row.</returns>

    ''' <remarks>Only the first result set is returned; all other data is ignored. Database nulls are converted into CLR nulls.</remarks>

    <Extension()>

    Public Function CallListProc(ByVal connection As SqlConnection) As <Dynamic()> Object

        Return New MicroProcCaller(connection, List)

    End Function

 

    ''' <summary>

    ''' Calls a stored procedure that returns a list containing lists of objects.

    ''' </summary>

    ''' <returns>A List containing Lists of MicroDataObject. There is one list per result set and one MicroDataObject per row in a given result set.</returns>

    ''' <remarks>Database nulls are converted into CLR nulls.</remarks>

    <Extension()>

    Public Function CallMultipleListProc(ByVal connection As SqlConnection) As <Dynamic()> Object

        Return New MicroProcCaller(connection, MultipleLists)

    End Function

 

End Module

For contrast, here is one of the functions using C#.

public static class MicroOrm

{

    public static dynamic CallSingleProc(this SqlConnection connection)

    {

       return new MicroProcCaller(connection, CallingOptions.Single);

    }

}

To set the stage, here is the constructor for the MicroProcCaller class. Note that the class is marked friend (C# internal). This is done because no one should ever declare a variable of this type; it only works in a dynamic context. The class is also transitory; consumers shouldn’t hold a reference to it.

Friend Class MicroProcCaller

    Inherits Dynamic.DynamicObject

 

    Private m_Connection As SqlConnection

    Private m_Options As CallingOptions

 

    Public Sub New(ByVal connection As SqlConnection, ByVal options As CallingOptions)

        m_Connection = connection

        m_Options = options

    End Sub

End Class

 

Public Enum CallingOptions

    Scalar = 0

    [Single] = 1

    List = 2

    MultipleLists = 3

End Enum

Now that we are in the dynamic context, we need a way to translate the late-bound method call into a stored procedure call. There are numerous ways to do this, but the easiest is to inherit from DynamicObject and override the TryInvokeMember method. The steps that need to be taken are numbered.

  1. Decide if this function is responsible for managing the life cycle of the connection object.
  2. Create a SqlCommand using the method name as the stored procedure name. The name of the method that was called can be found in the “binder”.
  3. Since stored procedure calls using Data.SqlClient do not support unnamed parameters, ensure that all arguments have names.
  4. Iterate through the argument array, creating SqlParameter objects as you go.
  5. Build the results and store them in the result parameter. (The details for this will be shown later.)
  6. Return true, indicating that the method was successfully processed.

Public Overrides Function TryInvokeMember(

    ByVal binder As System.Dynamic.InvokeMemberBinder,

    ByVal args() As Object,

    ByRef result As Object) As Boolean

 

    Dim manageConnectionLifespan = (m_Connection.State = ConnectionState.Closed)

    If manageConnectionLifespan Then m_Connection.Open()

 

    Try

        Using cmd As New SqlClient.SqlCommand(binder.Name, m_Connection)

            cmd.CommandType = CommandType.StoredProcedure

 

            If binder.CallInfo.ArgumentNames.Count <> binder.CallInfo.ArgumentCount Then

                Throw New ArgumentException("All parameters must be named")

            End If

 

            For i = 0 To binder.CallInfo.ArgumentCount - 1

                Dim param As New SqlClient.SqlParameter

                param.ParameterName = "@" & binder.CallInfo.ArgumentNames(i)

                param.Value = If(args(i) Is Nothing, DBNull.Value, args(i))

                cmd.Parameters.Add(param)

            Next

 

            Select Case m_Options

                Case CallingOptions.Scalar

                    result = ExecuteScalar(cmd)

                Case CallingOptions.Single

                    result = ExecuteSingle(cmd)

                Case CallingOptions.List

                    result = ExecuteList(cmd)

                Case CallingOptions.MultipleLists

                    result = ExecuteMultpleLists(cmd)

                Case Else

                    Throw New ArgumentOutOfRangeException("options")

            End Select

        End Using

    Finally

        If manageConnectionLifespan Then m_Connection.Close()

    End Try

 

    Return True

End Function

ExecuteScalar is pretty simple, the only reason it has its own method is for consistency.

Private Function ExecuteScalar(ByVal command As SqlCommand) As Object

    Dim temp = command.ExecuteScalar

    If temp Is DBNull.Value Then Return Nothing Else Return temp

End Function

For the rest of variants, consumers are expecting real properties or at least something that looks like properties. One option would be to code-gen the classes based on the runtime contents of the result sets. But runtime code generation is expensive and we don’t gain much because no consumers can reference our class by name. So in keeping with the theme of dynamic code, we instead use the prototypical dynamic object.

Friend Class MicroDataObject

    Inherits Dynamic.DynamicObject

    Private m_Values As New Dictionary(Of String, Object)(StringComparer.OrdinalIgnoreCase)

 

    Public Overrides Function TryGetMember(ByVal binder As System.Dynamic.GetMemberBinder, ByRef result As Object) As Boolean

        If m_Values.ContainsKey(binder.Name) Then result = m_Values(binder.Name) Else Throw New System.MissingMemberException("The property " & binder.Name & " does not exist")

        Return True

    End Function

 

    Public Overrides Function TrySetMember(ByVal binder As System.Dynamic.SetMemberBinder, ByVal value As Object) As Boolean

        SetMember(binder.Name, value)

        Return True

    End Function

 

    Public Overrides Function GetDynamicMemberNames() As System.Collections.Generic.IEnumerable(Of String)

        Return m_Values.Keys

    End Function

 

    Friend Sub SetMember(ByVal propertyName As String, ByVal value As Object)

        If value Is DBNull.Value Then m_Values(propertyName) = Nothing Else m_Values(propertyName) = value

    End Sub

 

End Class

Again, since no class should ever take a dependency on this object we mark it as Friend (C# internal). This leaves the three overrides for managing properties: one to set properties, one to get properties, and one to list the names of the properties. There is also a back-door for initializing the class using statically-typed code.

Private Function ExecuteSingle(ByVal command As SqlCommand) As Object

    Using reader = command.ExecuteReader

        If reader.Read Then

            Dim dataObject As New MicroDataObject

            For i = 0 To reader.FieldCount - 1

                dataObject.SetMember(reader.GetName(i), reader.GetValue(i))

            Next

            Return dataObject

        Else

            Return Nothing

        End If

    End Using

End Function

 

Private Function ExecuteList(ByVal command As SqlCommand) As List(Of MicroDataObject)

    Dim resultList = New List(Of MicroDataObject)

    Using reader = command.ExecuteReader

        Do While reader.Read

            Dim dataObject As New MicroDataObject

            For i = 0 To reader.FieldCount - 1

                dataObject.SetMember(reader.GetName(i), reader.GetValue(i))

            Next

            resultList.Add(dataObject)

        Loop

    End Using

    Return resultList

End Function

 

Private Function ExecuteMultpleLists(ByVal command As SqlCommand) As List(Of List(Of MicroDataObject))

    Dim resultSet As New List(Of List(Of MicroDataObject))

 

    Using reader = command.ExecuteReader

        Do

 

            Dim resultList = New List(Of MicroDataObject)

            Do While reader.Read

                Dim dataObject As New MicroDataObject

                For i = 0 To reader.FieldCount - 1

                    dataObject.SetMember(reader.GetName(i), reader.GetValue(i))

                Next

                resultList.Add(dataObject)

            Loop

            resultSet.Add(resultList)

 

        Loop While reader.NextResult

    End Using

 

    Return resultSet

End Function

 There is plenty of room for tweaking your newly created “Micro ORM”. Possible features include adding support for output parameters, an option to send in a parameterized query instead of a stored procedure name, and support for other databases.

Rate this Article

Adoption
Style

BT