Calling stored procedures from ADO.Net leads to verbose code with lots of syntactic overhead. It tends to look something like this:
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter()
{
ParameterName = "@return",
Direction = ParameterDirection.ReturnValue,
DbType = DbType.Int32
});
comm.Parameters.Add(new SqlParameter("@param1", 17));
comm.Parameters.Add(new SqlParameter()
{
ParameterName = "@outparam",
Direction = ParameterDirection.Output,
DbType = DbType.String,
Size = -1
});
comm.ExecuteNonQuery();
And all this to represent a call more succinctly written as:
result = MySProc 17, out outparamWe can achieve something close to this in C#, using lambda expression trees. Assuming that we will use a static method, and that we also need to pass in a connection, our call will look like:
StoredProcedures.Exec(
() => MySProc(17, out outparamvalue),
connection);
For this to work, we will start by specifying the signature of the stored procedure with a normal .net method. Like so:
public int MySProc(int param1, out string outparam)
{
}
and so, the signature for our Exec-method becomes:
public static int Exec(Expression<Func<int>> expr,
SqlConnection conn)
The Expression<> thing is what causes the compiler to stop doing what it normally does, which is to compile C# into MSIL, and instead give you the expression tree.
Focusing on the interesting bits, and leaving the details for the full code sample at the end, we start by noting that we are only interested in expressions that are method calls:
var callExpr = (MethodCallExpression)expr.Body;
The method name is simply callExpr.Method.Name. On to find the arguments!
foreach (var arg in callExpr.Arguments)
The arguments are themselves expressions, and that’s not really what we want right now. Although “17” in our example would be easy enough to handle, this really could be an arbitrarily complex expression. Thankfully, .Net lets us compile lambda expressions into delegates that we can then call to get the value of the expression. There are two minor details to get this working: First, the expression is strongly typed (as e.g. integer) and we need the boxed value, so we wrap the expression in a cast to object. Second, we create a lambda expression with our expression as the body. The type of the lambda expression is a function taking no arguments and returning an object. The code is:
private static object GetValue(Expression expr)
{
var valueExpr = Expression.Lambda<Func<object>>(
Expression.Convert(expr, typeof(object)),
new ParameterExpression[] { });
var deleg = valueExpr.Compile();
return deleg();
}
A bit more of a problem is handling out parameters. The argument – outparamvalue in our example above – is (typically) a local variable in the calling method. How do we assign a value to that? We need to look deeper into how the C# compiler treats anonymous methods. While this topic is worthy of a blog post of its own, the following brief summary will have to do: The compiler creates a class for the anonymous method, and the local variables become fields on that class. Thus, the out parameter expression becomes a field reference.
With that in mind, we can find the field and assign it using reflection:
private static void SetValue(Expression outarg, object value)
{
var member = (MemberExpression)outarg;
object source = GetValue(member.Expression);
var field = (FieldInfo)member.Member;
field.SetValue(source, value);
}
And we are done. The full code, as promised, is:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace StoredProcedureCaller
{
public class StoredProcedures
{
private static object GetValue(Expression expr)
{
var valueExpr = Expression.Lambda<Func<object>>(
Expression.Convert(expr, typeof(object)),
new ParameterExpression[] { });
var deleg = valueExpr.Compile();
return deleg();
}
private static void SetValue(Expression outarg, object value)
{
var member = (MemberExpression)outarg;
object source = GetValue(member.Expression);
var field = (FieldInfo)member.Member;
field.SetValue(source, value);
}
private static object ConvertToDbValue(object val)
{
if (val == null)
return DBNull.Value;
else
return val;
}
private static object ConvertFromDbValue(object val)
{
if (val == DBNull.Value)
return null;
else
return val;
}
public static int Exec(Expression<Func<int>> expr,
SqlConnection conn)
{
if (expr.Body.NodeType != ExpressionType.Call)
throw new ArgumentException("StoredProcedures.Exec() can only be used on method calls");
var callExpr = (MethodCallExpression)expr.Body;
var command = new SqlCommand(){
CommandText = callExpr.Method.Name,
CommandType = System.Data.CommandType.StoredProcedure,
Connection = conn
};
command.Parameters.Add(new SqlParameter()
{
ParameterName = "@return",
DbType = DbType.Int32,
Direction = ParameterDirection.ReturnValue
});
var paramlist = callExpr.Method.GetParameters();
var i = 0;
foreach (var arg in callExpr.Arguments)
{
var param = paramlist[i];
var sqlparam = new SqlParameter();
sqlparam.ParameterName = "@" + param.Name;
if (param.IsOut)
{
sqlparam.Direction = ParameterDirection.Output;
sqlparam.Size = -1;
}
else
{
sqlparam.Direction = ParameterDirection.Input;
sqlparam.Value = ConvertToDbValue(GetValue(arg));
}
command.Parameters.Add(sqlparam);
i++;
}
command.ExecuteNonQuery();
i = 1;
foreach (var arg in callExpr.Arguments)
{
var sqlparam = command.Parameters[i];
if (sqlparam.Direction == ParameterDirection.Output)
SetValue(arg, ConvertFromDbValue(sqlparam.Value));
i++;
}
return (int)command.Parameters[0].Value;
}
}
}
