Data Access Application Block 5.0 – Part 2

Using DataAccessor Factory methods to retrieve data using data access application block.

 The data access application block contains features that allow you to extract data using a SQL statement or a stored procedure as the query, and have the data returned to you as a sequence of objects that implements the IEnumerable interface. This allows you to execute queries, or obtain lists or arrays of objects that represent the original data in the database.

The block provides two core classes for performing this kind of query: the SprocAccessor and the SqlStringAccessor. You can create and execute these accessors in one operation using the ExecuteSprocAccessor and ExecuteSqlAccessor methods of the Database class, or create a new accessor directly and then call its Execute method.

Accessors use two other objects to manage the parameters you want to pass into the accessor (and on to the database as it executes the query), and to map the values in the rows returned from the database to the properties of the objects it will return to the client code.

The accessor will attempt to resolve the parameters automatically using a default mapper if you do not specify a parameter mapper. However, this feature is only available for stored procedures executed against SQL Server and Oracle databases. It is not available when using SQL statements, or for other databases and providers, where you must specify a custom parameter mapper that can resolve the parameters.

If you do not specify an output mapper, the block uses a default map builder class that maps the column names of the returned data to properties of the objects it creates. Alternatively, you can create a custom mapping to specify the relationship between columns in the row set and the properties of the objects.

The below code sample shows an example of creating and executing a customer accessor.

public class GetCustomerAccessor : IValueAccessorFactory<Customer, long, long>

{

    public DataAccessor<Customer> ConstructDataAccessor(Database database)

    {

        return database.CreateSprocAccessor<Customer>("usp_GetCustomerById", new GetCustomerParameterMapper(), ConstructMapper());

    }

    public IRowMapper<Customer> ConstructMapper()

    {

        IRowMapper<Customer> customerMapper = MapBuilder<Customer>.MapAllProperties()

            .Map(x => x.Id).ToColumn("CustomerIntId")

            .Map(x => x.Version).WithFunc(dataRecord =>

            {

                int index = dataRecord.GetOrdinal("Version");

                if (!dataRecord.IsDBNull(index))

                {

                    byte[] version = new Byte[(dataRecord.GetBytes(index, 0, null, 0, int.MaxValue))];

                    dataRecord.GetBytes(index, 0, version, 0, version.Length);

                    return version;

                }

                return null;

            })

            .Build();

        return customerMapper;

    }

    public object[] LoadParameters(long id)

    {

        return new Object[] { id };

    }

}

This accessor is called from a BaseRepository implementation to construct the required object

public IEnumerable Find(IAccessorFactory accessorFactory)

{

     return accessorFactory.ConstructDataAccessor(___Database)

                .Execute();

}

Test Methods

[TestMethod]

public void Can_findone_method_return_entity_for_specified_parameter_values()

{

    var baseRepository = new BaseRepository<Customer, long>();

    var customer = baseRepository.FindOne<long>(new GetCustomerAccessor(), 288);

    Assert.IsNotNull(customer, "Failed to retrieve customer");

    Assert.AreEqual<string>(customer.FirstName, "Prajeesh");

}