Retrieving data as objects using Enterprise library 5.0 database application block

CodeProject
Enterprise library 5 provided new extension which is called Accessors. Accessors execute the given query with parameters mapping and parameter values and also transform the result using output mapper you specified.

There are two types of accessors. SprocAccessor for stored procedure and SqlStringAccessor for SQL string. The most interesting part of accessors is mapping.
Here I give example with SqlStringAccessor. Lets see an simple example of using CreateSqlAccessor.
public IEnumerable<Company> GetCompanies()
      {
          return _database.CreateSqlStringAccessor<Company>(GetCompaniesSQL());
      }
Here company is my created DTO and column definition match with properties of Company class. Here in this case I have not given any custom output mapper and it used default mapper which matches property name and type with column of database and returns me IEnumarable of Customer.
There are two types of output mapper. Row mapper which takes each rows and transform into object so that it returns sequence of these objects.Another one is Result set mappers, takes entire result set and generates  a complete object graph.
Now problem is, I have a column in my Company table “Action” which stores value as Integer but in our code this “Action” is defined as an Enum. So here default mapping is not possible and we need to define a custom row mapper for converting the type of int to Enum type. 
Database application block provides a MapBuilder that make it easy to create a custom output mapper. MapBuilder expose a method BuildAllProperties which creates default output mapping .  For details about output mapping you can see the MSDN article http://msdn.microsoft.com/en-us/library/ff664486(v=pandp.50).aspx.  Now lets see the implementation of row mapping for “Action” column.

public IRowMapper<Company> GetCompanyRowMapper()
        {
          return  MapBuilder<Company>.MapAllProperties().Map(m => m.Action).WithFunc(
                    rec => (CompanyAction)Enum.ToObject(typeof(CompanyAction), rec.GetInt32(rec.GetOrdinal("Action")))).
                    Build();
        }

When we call MappAllProperties ad it gives IMapBuilderContext and after calling build it create RowMapping. Here after getting IMapBuildContext the property “Action” of Company class is mapped with  a  delegate function which works on IDataRecord and convert the value to enum. Here database value 1 is converted with CompanyAction enum value. Now  the GetCompanies function will look like this.

public IEnumerable<Company> GetCompanies()
      {
          return _database.CreateSqlStringAccessor<Company>(GetCompaniesSQL(),
           GetCompanyRowMapper()).Execute();
      }

Accessors takes rowmapper as input and it returns all companies. But if I need to get a company with company Id only which will return a single company then I also have to give company id as input parameter and create a parameter mapping.
To create a custom parameter mapping I have implemented IParameterMapper interface and mapping is assigned inside AssignParameters method body.
private class CompanySelectParameterMapper : IParameterMapper
       {
           public void AssignParameters(DbCommand command, object[] parameterValues)
           {
               DbParameter parameter = null;
               parameter = command.CreateParameter();
               parameter.ParameterName = "@Id";
               parameter.Value = parameterValues[0];
               command.Parameters.Add(parameter);
           }
       }

Here it convert DbParameter for inputs and assign this to command. I have shown here simple implementation of this mapping.

So the function for getting a single company with company id is

public Company GetCompanyById(int id)
       {
           return _database.CreateSqlStringAccessor<Company>(GetCompanyById(), new CompanySelectParameterMapper(),GetCompanyRowMapper()).Execute(id).SingleOrDefault();
       }

Here you can see I have created an object of parameter mapper and in the Execute() function the values of parameters is defined. So the AssignParameter will be called when Accessor will call the Execute method and populate the command with parameter value.
Here you have seen how to retrieve data as object and how to define custom output and parameter mapping with Accessors. As it create default output row mapping so we do not need to give extra effort to create O/ R mapping all time. SprocAccessor also provide same sets of feature as SqlStringAccessor provides.