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.

C#

Copy Code

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()) ));

Visual Basic

Copy Code

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.

C#	Copy Code
container.RegisterType<IParameterMapper, MyCustomParamMapper>(); container.RegisterType<IRowMapper<Product>, MyCustomRowMapper<Product>>();

Visual Basic	Copy Code
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.

C#	Copy Code
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 }

Visual Basic	Copy Code
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.

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.

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.

C#	Copy Code
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); }

Visual Basic	Copy Code
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.

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.

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

Visual Basic	Copy Code
Dim resultInMemory = myAccessor.Execute().ToList() Dim count As Integer = resultInMemory.Count() Dim first = resultInMemory.First() Dim second = resultInMemory.Skip(1).First()

Note:
When you execute an accessor asynchronously, you can only iterate over the result set once. If you attempt to iterate over it again, the block will raise an exception.