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

Bookmarks

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

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

  • Interesting but not ORM

    by Duraid Duraid,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    This is very interesting but in all fairness not an ORM because it's not mapping to any objects but instead is creating dynamic objects on runtime which is fundamentally different than ORM (mapping an object model to a relational model). This, instead, is a dynamic version of DataTable/DataSet.

  • Re: Interesting but not ORM

    by Stefan Wenig,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Thanks for saying this. Beyond that fact, I always shiver when someone demonstrates even creating an "ORM" that simply maps columns to fields using some whacky reflection-based code. This just affirms the common naive belief that that's what ORM is about, when it's really just the tip of the iceberg.

  • Re: Interesting but not ORM

    by Jonathan Allen,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    It is just the tip of the iceberge, that's the whole point. Dynamic typing can, it doesn't have to, but it can radically change the way we write .NET code. Five years from now we may look back at our class-based ORMs with all their configuration files and say "WTF were we thinking?". Or we may conclude that dynamic typing is just a toy and has no place in production-grade code. As .NET developers we are at a turning point and it can go either way.

  • Re: Interesting but not ORM

    by Stefan Wenig,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    I think these are two different things: One is dynamic vs. static typing. The dynamic keyword in C# serves as a bridge to dynamic languages. It doesn't make C# one, you'd have to switch to one of the Iron* languages in order to really get enough dynamic benefits to justify a paradigm shift. In C#, you'll still want to organize your code in static classes, even if you ocasionally access them dynamically. You can realize expando objects (maybe even prototypes as in JavaScript, I haven't tried how good dynamic works with delegate members). But those will be few and far between in real code.

    The ORM question is an entirely different one. There are several things that ORMs can do for you. If you think about DDD, they give you a chance to blend your business logic with your persistent data. You need classes for that. Some of them allow complex mappings between DB schemas and very different class structures, let you statically specify fetch strategies on relations, constraints that can be shared between the DB and your app (UI validation) etc. An ORM that just gives you an expando object with a field for every column (including foreign keys) might be nice for scenarios where even LINQ to SQL (not much of an ORM itself) is overkill, but for anything remotely complex? Even the various ActiveRecord require some kind of class model to be useful.
    If you just use expando objects, it's like duraid said: you basically get datasets with nice syntax. And you run into all the same problems that ORMs were built to solve: How do you manage your relations, where do you put your constraints, how do you abstract from a legacy/DBA-optimized schema, and most of all, where the heck is all that business logic going to go? Sounds like an unfriendly place to be in, not?

  • Re: Interesting but not ORM

    by Jonathan Allen,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    You can realize expando objects (maybe even prototypes as in JavaScript, I haven't tried how good dynamic works with delegate members). But those will be few and far between in real code.


    Calling a delegate wouldn't be much different than calling a stored procedure. In the simplest case you would use "TryInvokeMember" just as we did here and look up the Delegate in an internal dictionary. On the client side, your code would look no different that a static method call.

    As to how often this will really be used, I can't answer that. All I can do it point to what is possible and let you decide from there.


    How do you manage your relations, where do you put your constraints, how do you abstract from a legacy/DBA-optimized schema, and most of all, where the heck is all that business logic going to go? Sounds like an unfriendly place to be in, not?


    You have lots of options here. You can use the stored procedures for any schema changes and business logic. This would be most applicable to systems where they aren't going to let you use a real ORM.

    Another option is to use prototypes where in that information is encoded within a base object. It would have the skeleton and important properties like the primary key. Properties that you only need some of the time would still by dynamically generated. This would be an alternative to the anonymous classes we see in LINQ to SQL.

    A third option would be to dynamically lookup and store the database schema at runtime. This is what the "Jasper" project did. Perhaps now that dynamic typing is useful in VB and C#, that project will be resurrected.

    blogs.msdn.com/adonet/archive/2007/04/30/projec...

  • Re: Interesting but not ORM

    by Stefan Wenig,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Sure, but how can you use that dynamic stuff, provide all the information you really need, and still maintain an ease-of-use advantage over plain old static c#? But of course you're right, time will tell.

    The one thing that IMHO is missing from the whole DLR stuff is a first class native dynamic .NET language that brings real dynamic capabilities (think LISP) while being easy to learn and understand für C# programmers. Like Groovy for the Java crowd. (The Iron* languages are cool, but they appeal most to people coming from those platforms.) That could really get dynamic going here in .NET land. And it would call for lightweight dynamic ORMs. But even if those start small, there will eventually be need for more powerful systems that go beyond column/field mapping.

  • The Rise of the Micro-ORM with Sam Saffron and Rob Conery

    by Jonathan Allen,

    Your message is awaiting moderation. Thank you for participating in the discussion.

  • Source code...

    by Roland Żerek,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Hi,
    Any chance to download the source code for your implementation? It is very promising for my current job as the starting point...

    Roland

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

BT