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.
- Decide if this function is responsible for managing the life cycle of the connection object.
- 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”.
- Since stored procedure calls using Data.SqlClient do not support unnamed parameters, ensure that all arguments have names.
- Iterate through the argument array, creating SqlParameter objects as you go.
- Build the results and store them in the result parameter. (The details for this will be shown later.)
- 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
Community comments
Interesting but not ORM
by Duraid Duraid,
Re: Interesting but not ORM
by Stefan Wenig,
Re: Interesting but not ORM
by Jonathan Allen,
Re: Interesting but not ORM
by Stefan Wenig,
Re: Interesting but not ORM
by Jonathan Allen,
Re: Interesting but not ORM
by Stefan Wenig,
The Rise of the Micro-ORM with Sam Saffron and Rob Conery
by Jonathan Allen,
Source code...
by Roland Żerek,
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.
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.
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.
www.hanselminutes.com/default.aspx?ShowID=282
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