Defining Parameter Mappers

A parameter mapper takes the set of objects you want to pass to a query and converts each one into a DbParameter object. The accessor assigns these parameters to the command object it uses to access the data source. For information about using parameter mappers with an accessor, see Creating and Using Accessors and Executing Queries without Creating an Accessor.

The Data Access Application Block includes a default parameter mapping mechanism. This mechanism maps values you provide as query parameters to the DbParameter instances the query will use when executing against the database.

This default mapping uses the position of the parameters you provide in the object array, and executes the ADO.NET DeriveParameters method to discover the parameters required by the procedure. It maps your parameter(0) to the first parameter of the procedure, parameter(1) to the second parameter of the procedure, and so on. It converts the CLR types you specify into the appropriate database types for each DbParameter it populates.

The ADO.NET DeriveParameters call that resolves parameters for a stored procedure requires a round-trip to the database. The application block provides parameter information caching to mitigate the performance hit that this incurs. After the first call to a stored procedure that requires parameter discovery, the information about each parameter is saved in the parameter cache. This means that subsequent calls to the same stored procedure will not require a round- trip to the database.

Creating Custom Parameter Mappers

When you cannot, or do not wish to use the default parameter mapping feature that is available for stored procedures executed against a SqlDatabase or OracleDatabase instance, you can create a custom parameter mapper class that allows you to specify exactly how parameters are resolved by an accessor. You can use custom parameter mappers with both the SprocAccessor and the SqlStringAccessor.

To create a custom parameter mapper, you implement the IParameterMapper interface. You write code in the AssignParameters method that creates suitable parameters for your query and adds each one to the DbCommand instance in the correct order. The following code shows a simple custom parameter mapper that maps a parameter named p1 with a value you will provide in the array of object values you specify when calling the Execute method of an accessor.

public class ExampleParameterMapper : IParameterMapper
{
  public void AssignParameters(DbCommand command, object[] parameterValues)
  {
    DbParameter parameter = command.CreateParameter();
    parameter.ParameterName = "@p1";
    parameter.Value = parameterValues[0];
    command.Parameters.Add(parameter);
  }
}

'Usage
Public Class ExampleParameterMapper
  Implements IParameterMapper

  Public Sub AssignParameters(command As DbCommand, parameterValues As Object()) _
      Implements IParameterMapper.AssignParameters
    Dim parameter As DbParameter = command.CreateParameter()
    parameter.ParameterName = "@p1"
    parameter.Value = parameterValues(0)
    command.Parameters.Add(parameter)
  End Sub

End Class

The code above accepts a reference to the DbCommand you are using, and an array of parameter values that you will populate when you execute the accessor. The Accessor will call the AssignParameters method to resolve the parameters for the query, passing to it the current DbCommand instance and your array of parameter values.

The following code shows how you can use the custom parameter mapper described above to pass a value to a parameter named p1 in a SQL statement. The code assumes you have defined the Product class elsewhere, and you have resolved an instance of the Database class you want to use and stored it in the variable named db.

string query = "SELECT * from Customers WHERE CustomerName = @p1";
IParameterMapper mapper = new ExampleParameterMapper();
var accessor = db.CreateSqlStringAccessor<Product>(query, mapper);
accessor.Execute("myValue");

'Usage
Dim query As String = "SELECT * from Customers WHERE CustomerName = @p1"
Dim mapper As IParameterMapper = New ExampleParameterMapper()
Dim accessor = db.CreateSqlStringAccessor(Of Product)(query, mapper)
accessor.Execute("myValue")

The process is the same if you are executing a stored procedure, except in this case you will specify the name of the stored procedure, and call the CreateSprocAccessor method.