Retrieving Data as Objects using Enterprise Library 5.0 Database Application Block

Enterprise library 5 provided a 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 an example with SqlStringAccessor. Let's see a simple example of using CreateSqlAccessor.

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

Here, company is my created DTO and column definition matches 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 IEnumerable of Customer. There are two types of output mapper. Row mapper which takes each row and transform into object so that it returns sequence of these objects. Another one is Result set mappers, takes the entire result set and generates a complete object graph.

Now the 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 makes it easy to create a custom output mapper. MapBuilder exposes a method BuildAllProperties which creates default output mapping . For details about output mapping, you can see this MSDN article. Now let's 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 converts 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 take 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 converts DbParameter for inputs and assigns this to command. I have shown here a 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 that 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 creates default output row mapping, so we do not need to give extra effort to create O/ R mapping all the time. SprocAccessor also provides the same set of features as SqlStringAccessor provides.