Defining Parameter Mappers

Microsoft Enterprise Library 5.0

previous page next page

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.

Note:
The default parameter mapping mechanism is only available for the SqlDatabase and OracleDatabase classes, or for custom database classes you create where the database supports parameter discovery. The Database class exposes a Boolean property named SupportsParemeterDiscovery that your code can test (note that, for backward compatibility reasons, the misspelling of this name is preserved in the current version). If you use any other database provider, you must create a suitable implementation of the IParameterMapper interface that assigns the parameter values to the DbCommand. For more information, see Creating Custom Parameter Mappers later in this topic.

Note:

The default parameter mapping mechanism is only available for the SqlDatabase and OracleDatabase classes, or for custom database classes you create where the database supports parameter discovery. The Database class exposes a Boolean property named SupportsParemeterDiscovery that your code can test (note that, for backward compatibility reasons, the misspelling of this name is preserved in the current version). If you use any other database provider, you must create a suitable implementation of the IParameterMapper interface that assigns the parameter values to the DbCommand. For more information, see Creating Custom Parameter Mappers later in this topic.

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.

C#	Copy Code
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); } }

Copy Code

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);
  }
}

Visual Basic	Copy Code
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

Visual Basic

Copy Code

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.

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

Copy Code

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

Visual Basic	Copy Code
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")

Visual Basic

Copy Code

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.

previous page start next page