Retrieving Data as Objects

  • Article

This scenario demonstrates one of the ways that you can use the data accessors provided with the block to read data from a data store and return it as a sequence of objects of the type you specify. The Data Access Application Block provides two types of accessors, for stored procedures or for use with SQL statements, in addition to a range of classes that help you to map parameters to the query and map the returned data to the type of object you require. For information about these classes, see Returning Data as Objects for Client Side Querying.

Typical Goals

In this scenario, the goal is to retrieve data as a sequence of objects that you can use within your code or manipulate using a client-side query language such as LINQ. The example assumes you will execute a stored procedure within the database that selects the required data.

Solution

Use the ExecuteSprocAccessor method of the Database class. Specify the type of the objects to return, and pass into the method as parameters the name of the stored procedure and any parameter values you need to send to this stored procedure. The ExecuteSprocAccessor method returns a sequence of objects of the type you specify.

If the types of all the parameters for the stored procedure are standard data types, and the names of the columns in the database match the names of the properties in the returned object type, use the simplest overload of the ExecuteSprocAccessor method shown here. This assumes you want to return a sequence of objects from the stored procedure named CustomerList of your custom type Customer (defined elsewhere).

// Create and execute a sproc accessor that uses default parameter and output mappings
var results = db.ExecuteSprocAccessor<Customer>("CustomerList", 2009, "WA");

'Usage
' Create and execute a sproc accessor that uses default parameter and output mappings
Dim results = db.ExecuteSprocAccessor(Of Customer)("CustomerList", 2009, "WA")

If you need to perform custom processing on the parameters you pass to the stored procedure, such as generating individual DbParameter objects from a custom data structure, create a custom parameter mapper that creates and adds them to the parameter collection. Then specify this parameter mapper in the call to the ExecuteSprocAccessor method, as shown here.

// Use a custom parameter mapper and the default output mappings
IParameterMapper paramMapper = new YourCustomParameterMapper();
var results = db.ExecuteSprocAccessor<Customer>("Customer List", paramMapper, yourCustomParamsArray);

'Usage
' Use a custom parameter mapper and the default output mappings
Dim paramMapper As IParameterMapper = New YourCustomParameterMapper()
Dim results = db.ExecuteSprocAccessor(Of Customer)("Customer List", paramMapper, yourCustomParamsArray)

If the names of the columns returned by the stored procedure do not match the names of the properties of the custom type you want returned, create an output row mapper than maps the columns to the properties of the custom type. You can use the MapBuilder class to define these mappings. Then specify this output row mapper in the call to the ExecuteSprocAccessor method, as shown here.

// Use the default parameter mappings and a custom output mapper
IRowMapper<Customer> rowMapper = MapBuilder<Customer>.MapAllProperties() 
                                .MapByName(x => x.CustomerName)
                                .DoNotMap (x => x.Orders)
                                .Build();
var results = db.ExecuteSprocAccessor<Customer>("Customer List", rowMapper, 2009, "WA");

'Usage
' Use the default parameter mappings and a custom output mapper
Dim rowMapper As IRowMapper(Of Customer) 
rowMapper = MapBuilder(Of Customer).MapAllProperties() _
            .MapByName(Function(x) x.CustomerName) _
            .DoNotMap(Function(x) x.Orders) _
            .Build()
Dim results = db.ExecuteSprocAccessor(Of Customer)("Customer List", rowMapper, 2009, "WA")

Usage Notes

Consider the following when you use the Data Access Application Block data accessors:

  • The Execute method of data accessors, and the methods of the Database class that create and execute data accessors, return the results as an IEnumerable sequence. Sequences that implement IEnumerable can be manipulated in many ways; the IEnumerable interface and Enumerable extensions define a wide range of methods and properties that such sequences should expose.
  • One common use of accessors is to retrieve data so that you can perform additional processing on it using a query language such as LINQ. For information about LINQ syntax and usage, see LINQ: .NET Language-Integrated Query on MSDN.
  • Both the SqlStringAccessor and the SprocAccessor use deferred loading to stream the results returned from the database. This means that large data sets are not loaded into memory immediately, and the accessor will re-fetch data as you execute queries. However, the result of this is that the accessor creates a new connection to the database every time you iterate over the returned IEnumerator instance, which can cause performance issues if your client-side query code performs several operations on the data.

For detailed information about using data accessors, see Returning Data as Objects for Client Side Querying.