Returning Data as Objects for Client Side Querying

Developers often want to retrieve data in a format that better matches the real-world objects that the data represents, rather than in the rows and columns format of a DataReader or DataSet. This is typically the case when implementing common design patterns such as Domain Model, Table Module, and Repository.

The Data Access Application Block includes support for this scenario through a class called an accessor. Accessors accept information that is required to extract the data, and mappings that indicate how the input parameters correspond to the parameters of the underlying query. They use output mappings that define how the returned columns map to the properties of the objects the developer wants to work with, and return a sequence of objects of the specified type. The following schematic shows the high level process when using an accessor to retrieve data as an enumerable sequence of objects.

You can execute both stored procedures and SQL statements using the accessor approach. The block includes the SprocAccessor class for stored procedures and the SqlStringAccessor class for executing SQL statements. Accessors also provide methods for asynchronous data retrieval where the database you are using supports asynchronous query execution.

The mapping mechanism is flexible and extensible. For stored procedures executed against SQL Server and Oracle databases, the accessor will attempt to resolve the parameters automatically if you do not specify a parameter mapper that defines the correlation. However, default parameter mapping is not available when using SQL statements, or for other databases and providers. In these cases, you must specify a custom parameter mapper that can resolve the parameters.

When you execute an accessor, you can provide an output mapping that indicates how the accessor should map the values returned from the database to the properties of the objects it returns to the caller. 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 correlation between columns in the row set and the properties of the objects.

The accessor returns data as a sequence of objects in the form IEnumerable<TResult>, where each object represents one row of data in the data source and exposes properties that map to the columns in each row. You can handle the results in your code as objects that are part of your data model, or query them using client-side techniques such as Language Integrated Query (LINQ).

The following shows a simple example of executing a stored procedure that takes no parameters and then querying the results returned from the accessor. The code assumes you have defined the Customer class elsewhere, and you have resolved an instance of the Database class you want to use and stored it in the variable named db.

// Create an output row mapper that maps all properties based on the column names
IRowMapper<Customer> mapper = MapBuilder<Customer>.BuildAllProperties();

// Create a stored procedure accessor that uses this output mapper
var accessor = db.CreateSprocAccessor("Top Ten Customers", mapper);

// Execute the accessor to obtain the results
var customerData = accessor.Execute();

// Perform a client-side query on the returned data 
var results = from customer in customerData
              where customer.State == "WA"
              orderby customer.Name
              select new { Name = customer.Name };

// Display the results 
foreach (var customer in results)
{
  Console.WriteLine("{0} is a top customer in Washington State", customer);
}

'Usage
' Create an output row mapper that maps all properties based on the column names
Dim mapper As IRowMapper(Of Customer) = MapBuilder(Of Customer).BuildAllProperties()

' Create a stored procedure accessor that uses this output mapper
Dim accessor = db.CreateSprocAccessor("Top Ten Customers", mapper)

' Execute the accessor to obtain the results
Dim customerData = accessor.Execute()

' Perform a client-side query on the returned data 
Dim results = From customer In customerData _
              Where customer.State = "WA" _
              Order By customer.Name _
              Select Name = customer.Name
' Display the results 
For Each customer In results
  Console.WriteLine("{0} is a top customer in Washington State", customer)
Next 

There are also methods that allow you to pass parameters or a parameter mapper to the accessor, and execute a query without creating an accessor directly. For more information about using accessors to retrieve data as objects, see the following topics:

• Defining Parameter Mappers
• Building Output Mappers
• Creating and Using Accessors
• Executing Queries without Creating an Accessor
• Executing Accessor Queries Asynchronously
• Additional Information for Accessors and Client-side Queries