P3.NET

Simplifying ADO.NET Data Access, Part 5

This is a continuation of the series on simplifying data access using ADO.NET.  We have a little cleanup to do around parameters before finishing up the series.

Generic Parameters

In the last article we defined the DataParameter class to represent a (database agnostic) parameter and value. At the time we used object for the type of the parameter value. This is convenient for working with parameters in a generic manner but for clients we want to use a strongly typed parameter so we will create a new, derived type from DataParameter that accepts the type of the parameter value. Under the hood we will continue to use the non-generic version but consumers of the code will use the generic version, in most cases.

public class DataParameter<T> : DataParameter
{
    public DataParameter ( string name ) : base(name, DbTypeMapper.ToDbType(typeof(T))
                                                , ParameterDirection.Input)
    { }

    public DataParameter ( string name, ParameterDirection direction ) : base(name,
                                    DbTypeMapper.ToDbType(typeof(T)), direction)
    { }

    public T TypedValue
    {
        get
        {
            if (Convert.IsDBNull(Value) || (Value == null))
                return default(T);

            return (T)Convert.ChangeType(Value, typeof(T));
        }

        set
        {
            Value = value;
        }
    }
}

The only hard part is mapping the CLR type to a DbType. To help with this we introduce a mapping class (included in the source). For most cases the mapping class is sufficient. If you need something more complicated or extensible then feel free to replace the mapper with something else. Under the hood the mapper simply uses a dictionary to map CLR types to the corresponding enum value.

Fluent Parameters

With strongly typed parameters defined we can now switch our focus to making parameter creation more fluent. Depending upon the kind of parameter we can expose a fluent API to allow the core properties to be set.

  • Input parameters – name, type and value
  • Output parameter – name, type
  • input/output parameter – name, type and value
  • Return value – ignored

While a client could use a (raw) DataParameter<T> we’ll create a couple of derived types to represent each of the parameter kinds along with a simple fluent interface to set the core properties. Here’s the code for an input parameter.

public class InputParameter
{
    private InputParameter ( string name )
    {
        if (name == null)
            throw new ArgumentNullException("name");
        if (String.IsNullOrEmpty(name))
            throw new ArgumentException("Name cannot be empty.", "name");

        m_name = name;
    }

    public static InputParameter Named ( string name )
    {
        return new InputParameter(name);
    }

    public InputParameter<T> WithValue<T> ( T value )
    {
        return new InputParameter<T>(m_name).WithValue(value);
    }

    private readonly string m_name;
}

The InputParameter type is a builder and therefore does not derive from DataParameter. This ensures that the type is defined by setting a value for the parameter. The builder ultimately returns a concrete type of InputParameter based upon the value assigned to it.

public class InputParameter<T> : DataParameter<T>
{
    public InputParameter(string name) : base(name, ParameterDirection.Input)
    { }

    public InputParameter<T> WithValue(T value)
    {
        this.Value = value;

        return this;
    }
}

Now the client code becomes much clearer as to what parameters are being passed in and what are being returned.

var cmd = new AdhocQuery("SELECT Id, Name FROM Users where Id = @id").WithParameters(
                         InputParameter.Named("id").WithValue(id)
                         );

CAVEAT: The fluent interface around creating non-generic parameters has changed of recent so there may be some bugs that have not been found in the newer code.

Next Article

We are finished with parameters but we still have a few more areas to simplify so we will defer until next time.

Download the Demo Code

Download the Library Code

Comments

  1. Hi Michael, can I get your email address? I would like to ask a question on another article, but I have the idea that we can’t reply on older articles (perhaps because of migration to net P3Net platform?)

    Tnx,
    Mark.