Simplifying ADO.NET Data Access, Part 4

This is a continuation of a series on simplifying the use of ADO.NET. In the last article we added the ability to cleanly separate query definition from the underlying data provider. But we left out parameters which are generally critical (and specific) to ADO.NET providers. In this article we will add support for parameters and add a fluent interface to make it easy to use.

Commands and parameters in ADO.NET

In ADO.NET you create a parameter by creating a provider-specific instance of DbParameter and then set the values accordingly.

var cmd = new SqlCommand("SELECT Id, Name FROM Roles where Id = @id");
cmd.Parameters.AddWithValue("@id", id);

This has several disadvantages.

• The instance is tied to the provider
• The parameter can be associated with only one command making reuse harder
• The parameter name is provider-specific making it harder to build generic queries
• There is no type checking to ensure that the parameter value matches the parameter type

DataParameter

As with the other areas we will create a generic type to represent parameters called DataParameter. It will expose the standard properties common to all parameters such as name, type, value, direction, precision, etc.

public class DataParameter
{   
   private DataParameter ()    
   {
      IsNullable = true;        
      SourceVersion = DataRowVersion.Current;    
   }

   public DataParameter ( string name, DbType type ) : this(name, type, ParameterDirection.Input)    
   { }    

   public DataParameter ( string name, DbType type, ParameterDirection direction ) : this()    
   {        
      Name = name;        
      DbType = type;        
      Direction = direction;    
   }

   public DbType DbType { get; private set; }    
   public ParameterDirection Direction { get; private set; }    

   public bool IsNullable { get; set; }    
   public string Name { get; set; }    
   public int Precision { get; set; }    
   public int Scale { get; set; }        
   public int Size { get; set; }    
   public string SourceColumn { get; set; }       
   public DataRowVersion SourceVersion { get; set; }
    
   public object Value { get; set; }
}

Ignoring error checking, it is simply a collection of properties. Notice that the type and direction are fixed at creation time.  The type is of type DbType which is the database-agnostic list of types. If you need to support provider-specific types then you’ll have to create a derived type as will be discussed later. Finally note that the parameter name does not contain any provider prefixes like @, ? or colon (:).

To hook everything up we need only add a Parameters property to DataCommand. We’ll use a KeyedCollection so that the parameters are accessible by name as well.

public class DataCommand 
{    
   public DataCommand ( string commandText, CommandType type )    
   {
      ...
      Parameters = new DataParameterCollection();    
   } 

   public DataParameterCollection Parameters { get; private set; }
}

We can now update the original code with a more generic version.

var cmd = new DataCommand("SELECT Id, Name FROM Roles where Id = @id", CommandType.Text);
cmd.Parameters.Add(new DataParameter("id", DbType.Int32) { Value = id });

Converting to ADO.NET

Now that the generic code is in place we now need to hook up the provider back end. This is more complicated but ultimately not that hard. Most of the changes are in the ConnectionManager.CreateCommandBase abstract method.

• For each parameter associated with the command create a provider-specific instance
• Copy the property values making any adjustments based upon the provider such as the data type and parameter name
• For input or input-output parameter copy the value

For the generic DbProviderFactoryConnectionManager we wrote it is straightforward.

protected override DbCommand CreateCommandBase ( DataCommand command )
{
    DbCommand cmdDb = Factory.CreateCommand();
    ...
    foreach (var parm in command.Parameters)
    {
        cmdDb.Parameters.Add(CreateParameterBase(parm, cmdDb));
    };

    return cmdDb;
}

CreateParameterBase is a new virtual method that handles the heavy lifting. It determines if the provider requires any formatting of a parameter name and applies it accordingly. Refer to the code if you are interested in the details.

protected virtual DbParameter CreateParameterBase (DataParameter source, DbCommand command)
{
    DbParameter target = command.CreateParameter();

    target.ParameterName = FormatParameterName(source.Name);
    target.DbType = source.DbType;
    target.Direction = source.Direction;
    target.Size = source.Size;
    target.SourceColumn = source.SourceColumn;
    target.SourceVersion = source.SourceVersion;

    switch (source.Direction)
    {
        case ParameterDirection.InputOutput:
        case ParameterDirection.Input: target.Value = source.Value ?? DBNull.Value; break;
    };

    return target;
}

One small change is also needed in ConnectionManager.PrepareCommandCore to handle null. Any parameter that has no value is set to DBNull.Value so that the database will properly see the null value.

Passing the parameter is only part of the solution. It is also necessary to get any output parameter values after the call is made. UpdateParameterCore is a new method in ConnectionManager that is responsible for copying any output parameter value back to the original DataParameter.

private static void UpdateParameterCore (DbCommand command, DataCommand target)
{
    for (int nIdx = 0; nIdx < target.Parameters.Count; ++nIdx)
    {
        switch (command.Parameters[nIdx].Direction)
        {
            case ParameterDirection.InputOutput:
            case ParameterDirection.Output:
            case ParameterDirection.ReturnValue:
            {
                target.Parameters[nIdx].Value = command.Parameters[nIdx].Value;
                break;
            };
        };
    };
}

Every place a command is created is updated to call this method after the connection is closed (output parameters don’t get their value until the command is cleaned up).

Custom Commands

At this point we have everything we need to support parameterized commands but we can clean up the code a little more. In general commands are either adhoc queries or stored procedures so we can make the code a little clearer by creating derived types. For a stored procedure it is common to what to view the return value so we expose a property containing the result.

public class AdhocQuery : DataCommand
{
    public AdhocQuery (string commandText) : base(commandText, CommandType.Text)
    {
    }
}

public class StoredProcedure : DataCommand
{
    public StoredProcedure (string name) : base(name, CommandType.StoredProcedure)
    {
    }

    public int ReturnValue { get; internal set; }
}

The client could easily add a parameter for that but we can extend the existing code to determine if a stored procedure is being executed and add the parameter on the provider side. This requires a change to ConnectionManager.PrepareCommandCore. Note that we could generalize this behavior to commands in general but I’ll leave it specific to a stored procedure.

//Automatically capture the return value, if a sproc
var sproc = command as StoredProcedure;
if (sproc != null)
{
    //If there isn't a return value parameter already then add one
    if (!cmd.Parameters.OfType<DbParameter>().Any(p => p.Direction == ParameterDirection.ReturnValue))
    {
        var pReturn = cmd.CreateParameter();
        pReturn.ParameterName = "return";
        pReturn.DbType = DbType.Int32;
        pReturn.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(pReturn);
    };
};

we have to make a similar change on the ConnectionManager.UpdateParameterCore method to copy the value back to the StoredProcedure.ReturnValue property after the command executes. Refer to the code for the details.

Next Time

We are almost done. Next time we will clean up the parameter API a little by making it fluent and generic. We will also clean up the reader code to make it easier to use.

DataAccessDemo.zip