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
