Dynamic Stored Procedure Execution

 

With the imminent arrival of the dynamic keyword to C# I think we will be seeing more prototypes of ways of putting dynamic objects to use within .NET.  One interesting use is Phil Haack's method of HTML encoding properties that are predicated with an underscore.  After reading about this I wanted to try creating my own dynamically driven class.

Disclaimer:  I'm still on the fence over the use of dynamic in C#.  I'm not advocating the use of this code.  I just wanted to see if it was possible and what it would look like.

One problem with ORMs is mapping stored procedures.  This is usually a manual process because stored procedures are inherently not typed.  So I was envisioning something along the lines of:

IList<Person> people = DataContext.Sproc.ExecuteGetList<Person>.spGetPeopleByDOB(dob: myDate);

For the purpose of this prototype, I decided not to use any real data context.  That could be added in easily enough.  For now, I'll be rolling my own data layer.

Step 1 - Create a simple DataContext:

Step 2 - Create the Sproc class

This class will basically be a factory for different dynamic stored procedure executers.  This way we can easily differentiate between ExecuteScalar, ExecuteNonQuery, getting an object, and getting a list of objects.  For now, I'm going to implement ExecuteScalar<T>:

Step 3 - Create the DynamicExecuteScalar<T> class

The real magic here is the use of TryInvokeMember.  This is the function that will be invoked at runtime whenever someone calls a method on your DynamicObject.  So when I call myDynamicExecuteScalar.CallingSomeMethod() I'm actually calling TryInvokeMember. 

Inside of TryInvokeMember we use binder.Name to get the name of the method called (eg. "CallingSomeMethod").  Then we use the binder.CallInfo.ArgumentNames and args[] to get a list of named parameters that we can turn into our sql parameters.

Once you have this code wired up, you can get a scalar for any stored procedure from your code simply by calling:

And using this as a starting point you can add ExecuteNonQuery, ExecuteGetList<T>, etc.  You can also modify the data context to be more extensible and mockable.