Using dynamic for Stored Procedures

It can be quite cumbersome to call an SP with ado.net when you also need to supply parameters, you usually end up with this:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Text = "MyStoredProcedure";
    var firstParameter = new SqlParameter("FirstParameter", value);
    var secondParameter = new SqlParameter("SecondParameter", DBNull.Value);
    cmd.Parameters.Add(firstParameter);
    cmd.Parameters.Add(secondParameter);
    var reader = cmd.ExecuteReader();
}

There is quite a lot of ceremony in there to make a simple SP call, using a dynamic object we can reduce this down to:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    dynamic myStoredProcedure = new DynamicStoredProcedure("MyStoredProcedure");
    myStoredProcedure.FirstParameter = value;
    myStoredProcedure.SecondParameter = null;
    var reader = myStoredProcedure.ExecuteReader(connection);
}

As you can see it still requires the caller to look after the connection so that it can manage it properly and make sure that it is closed however now using the dynamic keyword we can make calls to properties that don’t exist but instead are resolved dynamically and treated as a SqlParameter using the name of the property as the name of the parameter and the value is used as the parameter value, also nulls are automatically converted to DBNull.Value as the value for the parameter.

The DynamicStoredProcedure object declaration looks like this:

public class DynamicStoredProcedure : DynamicObject
{
	protected string m_Name = string.Empty;
	protected IDictionary<string, SqlParameter> m_Parameters = new Dictionary<string, SqlParameter>();
	
	public DynamicStoredProcedure(string storedProcedureName)
	{
		m_Name = storedProcedureName;
	}

	public void AddParameter(SqlParameter parameter)
	{
		if (m_Parameters.ContainsKey(parameter.ParameterName))
		{
			m_Parameters[parameter.ParameterName] = parameter;
		}
		else
		{
			m_Parameters.Add(parameter.ParameterName, parameter);
		}
	}

	public override bool TrySetMember(SetMemberBinder binder, object value)
	{
		var convertedValue = (value == null) ? DBNull.Value : value;

		if (m_Parameters.ContainsKey(binder.Name))
		{
			m_Parameters[binder.Name].Value = convertedValue;
			return true;
		}

		var param = new SqlParameter(binder.Name, convertedValue);
		m_Parameters.Add(binder.Name, param);
		return true;
	}
	
	public SqlDataReader ExecuteReader(SqlConnection connection)
	{
		var cmd = CreateCommand(connection);
		var reader = cmd.ExecuteReader();
		return reader;
	}

	public T ExecuteScalar<T>(SqlConnection connection)
	{
		var cmd = CreateCommand(connection);
		var result = cmd.ExecuteScalar();
		var type = typeof(T);

		if (result.GetType().IsAssignableFrom(type))
			return (T)result;

		throw new InvalidOperationException(string.Format("Cannot convert result [{0}] to type [{1}]", result, type));
	}

	public void ExecuteNonQuery(SqlConnection connection)
	{
		var cmd = CreateCommand(connection);
		cmd.ExecuteNonQuery();
	}

	private SqlCommand CreateCommand(SqlConnection connection)
	{
		var cmd = connection.CreateCommand();
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.CommandText = m_Name;
		cmd.Parameters.AddRange(m_Parameters.Values.ToArray());
		return cmd;
	}
}

I have added the standard Execute methods that you find on SqlCommand There are also times were you need to add a parameter explicitly for instance for output parameters and if you want more control over the configuration of the parameter in this case I have added an explicit AddParameter for these cases.

Advertisement