Building Output Mappers

An output mapper takes the result set returned from a database (in the form of rows and columns) and converts the data into a sequence of objects. There are two different types of output mappers that you can use to transform the returned data. Row mappers transform each row into an object, so that the accessor can return a sequence of these objects. Result set mappers take the entire result set and generate a complete object graph that represents the result set as objects.

The following sections of this topic provide more information about the default mapping capabilities and the two types of output mapper:

• Using the Default Row Mapper
• Defining Custom Row Mappings
• Creating Result Set Mappers

For information about using output mappers with an accessor, see Creating and Using Accessors and Executing Queries without Creating an Accessor.

Using the Default Row Mapper

When you create a SprocAccessor or SqlStringAccessor, or execute a SprocAccessor or SqlStringAccessor using the methods of the Database class, you can use overloads that do not require you to provide an output mapper instance. In these cases, the block uses a default mapping that simply matches each property of the object type you are retrieving with the columns in the data set based on the names of the properties and columns. If a property does not match any column in the data set, the block raises an InvalidOperationException. If a column in the data set does not match any property on the object, the block just ignores that column. In addition, the default output mapping will not map properties of the target object that are defined as a collection type.

If you need to vary this default behavior, you must define custom mappings between the data set extracted from the database and the properties of the objects you want returned by the accessor. The following sections of this topic describe how you can do this.

Defining Custom Row Mappings

A row mapper takes a single row of data in the data set returned from the database and converts it into an instance of the object type you are retrieving. The row mapper exposes a function named MapRow that is executed once for each row in the data set, and the object it returns each time is added to the IEnumerable sequence of objects that the assessor will return to the calling code. The following code shows the interface for row mappers.

public interface IRowMapper<TResult>
{
  TResult MapRow(IDataRecord row);
}

'Usage
Public Interface IRowMapper(Of TResult)
  Function MapRow(row As IDataRecord) As TResult
End Interface

You can write code to implement this interface. However, the Data Access Application Block provides a MapBuilder class that makes it easy to create custom output mappings. The MapBuilder class exposes a method named BuildAllProperties that creates a default mapping based on the property and column names of TResult, as described in the previous section of this topic.

// Create a set of default mappings
IRowMapper<Customer> mapper = MapBuilder<Customer>.BuildAllProperties();

'Usage
' Create a set of default mappings
Dim mapper As IRowMapper(Of Customer) =  MapBuilder(Of Customer).BuildAllProperties()

However, the MapBuilder class also provides a range of methods and functions that you can use to create mappings. You usually start either by creating a set of default mappings using the BuildAllProperties method (as shown above) and then modify them as required, or you can start with an empty set of mappings and add those you require. The following example creates an empty mapping by using the MapNoProperties method, which returns an IMapBuilderContext. In order to get an IRowMapper you must also call the Build method.

// Start with no mappings
IRowMapper<Customer> mapper = MapBuilder<Customer>.MapNoProperties().Build();

'Usage
' Start with no mappings
Dim mapper As IRowMapper(Of Customer) = MapBuilder(Of Customer).MapNoProperties().Build()

Mapping Functions

After you create an IRowMapper mapping instance, you can use the methods of the MapBuilder class to modify the mappings or add new mappings. The MapBuilder class provides a fluent interface, allowing you to chain the methods together in one statement. The following table shows the methods available in the MapBuilder class.

Some of these methods are more useful when you start with MapAllProperties, and some are more useful when you start with MapNoProperties. For example, DoNotMap is likely to be useful only if you start with MapAllProperties and then use it to reduce the mapped set. If you map the same item more than once, the last mapped value will be used.

Using these methods, you can specify the following mapping rules:

• You can specify a mapping for a property to a column with the same name.
• You can specify a mapping for a property to a column with a different name.
• You can specify that a property will not be mapped to any column.
• You can specify a function that will be called to generate the value for a property. These must be instance properties that are public and writable, and which cannot be indexers or reference a collection.

After you specify the mappings you require, you call the Build method of the MapBuilder class to generate the IRowMapper instance you can use in your code. The following example shows how you can use the mapping methods and the fluent interface of the MapBuilder class to create a custom row mapping.

// Start with a mapping for each property based on the name
// then modify the mappings as required, and finally call Build. 
IRowMapper<Customer> mapper = MapBuilder<Customer>.MapAllProperties() 
                              .MapByName(x => x.CustomerName)
                              .DoNotMap (x => x.Orders)
                              .Map(x => x.Region).ToColumn("StateOrCounty")
                              .Map(x => x.Time).WithFunc(x => DateTime.Now)
                              .Build();

'Usage
' Start with a mapping for each property based on the name
' then modify the mappings as required, and finally call Build. 
Dim mapper As IRowMapper(Of Customer) 
mapper = MapBuilder(Of Customer).MapAllProperties() _
         .MapByName(Function(x) x.CustomerName) _
         .DoNotMap(Function(x) x.Orders) _
         .Map(Function(x) x.Region).ToColumn("StateOrCounty") _
         .Map(Function(x) x.Time).WithFunc(Function(x) DateTime.Now) _
         .Build()

Creating Result Set Mappers

Row mappings, as described in the previous sections of this topic, generate a single instance of the object type you specify for each row of data in the data set extracted from the database. The result is a sequence of instances of the same type of object. However, there are times when this approach is too simplistic for the data you need in your application. For example, you may require a hierarchy of objects, or a complex object graph containing related types.

In this case you can use a result set mapper instead of a row mapper. A result set mapper exposes a method named MapSet that is executed only once for the complete data set (as opposed to a row mapper, which is executed once for each row). The following code shows the interface for a result set mapper.

public interface IResultSetMapper<TResult>
{
  IEnumerable<TResult> MapSet(IDataReader reader);
}

'Usage
Public Interface IResultSetMapper(Of TResult)
  Function MapSet(reader As IDataReader) As IEnumerable(Of TResult)
End Interface

The accessor passes an instance of a class that implements the IDataReader interface to the MapSet method. Code in this method can iterate over the rows in the DataReader and generate the entire sequence of objects that conforms to any complex structure you require. The only limitation is that the object graph must be returned as an IEnumerable sequence. This approach means that you have complete control over the mapping process for the entire data set.

After you create a result set mapper, you can use it in place of a row mapper when you create a SprocAccessor or SqlStringAccessor, or execute a SprocAccessor or SqlStringAccessor using the methods of the Database class.