Additional Information for Accessors and Client-side Queries

This topic describes some additional factors and issues related to using accessors to retrieve data as a sequence of objects. It covers the following:

• Creating Accessors through Dependency Injection
• Accessing and Iterating the Results Set
• Querying the Results Set Using LINQ
• Deferred Loading and Multiple Iterations of the Result Set

Creating Accessors through Dependency Injection

Commonly, you will obtain a reference to a Database instance, such as a SqlDatabase or OracelDatabase, by resolving it through the Enterprise Library dependency injection container. You can also use the container to create and inject instances of the SprocAccessor and SqlStringAccessor into your application classes. Both of these accessors have public constructors that allow you to create them using dependency injection.

The registrations and type mappings for resolving configured databases are implemented automatically by the Enterprise Library container extension, but it does not register any mappings for accessors, as they will be specific to your application. However, you can register suitable mappings in the container yourself. In general, it is good practice to do this in the configuration file or in code that executes when the application initializes, unless you want to be able to vary the mappings based on environmental or other external run-time factors.

Using dependency injection means that you can change the behavior of the application by changing the configuration, without touching the code or requiring recompilation and redeployment. It also makes testing easier, because you can mock an accessor more easily than you can a Database implementation. Finally, it also makes it easier to see from the source code exactly what each class in your application actually does.

You can register mappings between the DataAccessor base class and the SprocAccesor and SqlStringAccessor, using names to identify the mappings if you create more than one mapping for the DataAccessor base class. The following code shows how you can create mappings for both accessors, differentiated by a name, so that they can be used to access instances of the CustomerType class from a database named CustomerDB.

container.RegisterType<DataAccessor<CustomerType>, SprocAccessor<CustomerType>>("Sproc",
                new InjectionConstructor(
                    new ResolvedParameter<Database>(),
                    new InjectionParameter<string>("CustomerDB"),
                    new InjectionParameter(MapBuilder<CustomerType>.BuildAllProperties())
                ));

container.RegisterType<DataAccessor<CustomerType>, SqlStringAccessor<CustomerType>>("Sql",
                new InjectionConstructor(
                    new ResolvedParameter<Database>(),
                    new InjectionParameter<string>("CustomerDB"),
                    new InjectionParameter(MapBuilder<CustomerType>.BuildAllProperties())
                ));

'Usage
container.RegisterType(Of DataAccessor(Of CustomerType), SprocAccessor(Of CustomerType))("Sproc", _
          New InjectionConstructor(New ResolvedParameter(Of Database)(), _
          New InjectionParameter(Of String)("CustomerDB"), _
          New InjectionParameter(MapBuilder(Of CustomerType).BuildAllProperties())))

container.RegisterType(Of DataAccessor(Of CustomerType), SqlStringAccessor(Of CustomerType))("Sql", _
          New InjectionConstructor(New ResolvedParameter(Of Database)(), _
          New InjectionParameter(Of String)("CustomerDB"), _
          New InjectionParameter(MapBuilder(Of CustomerType).BuildAllProperties())))

You can also use the same technique to register mappings between the IParameterMapper interface and your own custom parameter mapper implementations, and between the IRowMapper or IResultSetMapper interfaces and your custom row and results set mapper implementations, as shown here.

container.RegisterType<IParameterMapper, MyCustomParamMapper>();
container.RegisterType<IRowMapper<Product>, MyCustomRowMapper<Product>>();

'Usage
container.RegisterType(Of IParameterMapper, MyCustomParamMapper)("Sproc")
container.RegisterType(Of IRowMapper(Of Product), MyCustomRowMapper(Of Product))()

Then, in your code, you can inject an accessor by specifying the dependencies using attributes and then resolving the class through the container. Alternatively, if you resolve the main form or startup class of your application through the container, it will automatically populate all of the dependencies. The following code shows a simple class that has a dependency in its constructor on an accessor identified by the name Sproc.

public class MyDataLayer<T> 
{
  private DataAccessor<T> accessor;

  public MyDataLayer([Dependency("Sproc")] DataAccessor<T> dataAccessor)
  {
    accessor = dataAccessor;
  }

  // Methods to execute the accessor and return the results here
}

'Usage
Public Class MyDataLayer(Of T)

  Private accessor As DataAccessor(Of T)

  Public Sub New(<Dependency("Sproc")> dataAccessor As DataAccessor(Of T))
    accessor = dataAccessor
  End Sub

  ' Methods to execute the accessor and return the results here

End Class

If you resolve this class through the container, it will create an instance of the configured accessor (in this example, SprocAccessor). The constructor for the accessor takes as parameters a Database instance, the stored procedure name, a parameter mapper, and a row or result set mapper.

Accessing and Iterating the Results Set

The Execute method of the accessors, and the methods of the Database class that create and execute 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.

For example, you can usually carry out the following types of operations on an IEnumerable sequence:

• Get information about the sequence using methods such as Count, Max, and Average.
• Navigate the sequence using methods such as First, Last, and Skip.
• Extract items from the sequence using methods such as Remove, Take, and TakeWhile.
• Get distinct elements, reverse the sequence, and check if it contains specific elements.
• Copy the contents to an array or a list.
• Assign the sequence to objects such as UI controls that can display a list of items, using the properties to specify how they should be displayed.
• Access each item in code using the properties of the class to perform any custom processing you require on the data.

For more information about the operations permissible on IEnumerable sequences, see IEnumerable<(Of <(T>)>) Members on MSDN.

Querying the Results Set Using LINQ

One common use of accessors is to retrieve data so that you can perform additional processing on it using a query language such as Language Integrated Query (LINQ). LINQ allows you to write queries using syntax similar to that used in SQL queries.

For example, the following code takes an IEnumerable sequence of Customer objects returned from the Execute method of an accessor and selects only those that have "WA" as the value of the State property. It sorts the matching objects into order based on the customer name and returns an IEnumerable sequence of just the names, which can then be displayed.

var results = from customer in customerData
              where customer.State == "WA"
              orderby customer.Name
              select customer.Name;

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

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

For information about LINQ syntax and usage, see LINQ: .NET Language-Integrated Query on MSDN.

Deferred Loading and Multiple Iterations of the Result Set

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 IEnumerator instance returned by the Execute method, which can cause performance issues if your client-side query code performs multiple operations on the data.

For example, if you call the Count, First, and Skip methods on the data, you will cause the block to open multiple connections (one for each method call). To avoid this, you can use the ToList method to force the accessor to load all of the data into memory. You can then perform multiple client-side iterations and operations on the data without opening any additional connections to the database, as shown in the following code.

var resultInMemory = myAccessor.Execute().ToList();
int count = resultInMemory.Count();
var first = resultInMemory.First();
var second = resultInMemory.Skip(1).First();

'Usage
Dim resultInMemory = myAccessor.Execute().ToList()
Dim count As Integer = resultInMemory.Count()
Dim first = resultInMemory.First()
Dim second = resultInMemory.Skip(1).First()