Custom IDataReader implementations - Strategy for providing
custom implementations of IDataReader. This can be used to centralized
and transparently map DBNull values to CLR types when accessing an
IDataReader or to provide extended mapping functionality in
sub-interfaces.

20.2. Motivations

There are a variety of motivations to create a higher level ADO.NET
persistence API.

Encapsulation of common 'boiler plate' tasks when coding directly
against the ADO.NET API. For example here is a list of the tasks typically
required to be coded for processing a result set query. Note that the code
needed when using Spring's ADO.NET framework is in italics.

Define connection parameters

Open the connection

Specify the command type and text

Prepare and execute the statement

Set up the loop to iterate through the results (if any)

Do the work for each iteration

Process any exception

Display or rollback on warnings

Handle transactions

Close the connection

Spring takes care of the low-level tasks and lets you focus on
specifying the SQL and doing the real work of extracting data. This
standard boiler plate pattern is encapsulated in a class, AdoTemplate. The
name 'Template' is used because if you look at the typical code workflow
for the above listing, you would essentially like to 'template' it, that
is stick in the code that is doing the real work in the midst of the
resource, transaction, exception management.

Another very important motivation is to provide an easy means to
group multiple ADO.NET operations within a single transaction while at the
same time adhering to a DAO style design in which transactions are
initiated outside the DAOs, typically in a business service layer. Using
the 'raw' ADO.NET API to implement this design often results in explicitly
passing around of a Transaction/Connection pair to DAO objects. This
infrastructure task distracts from the main database task at hand and is
frequently done in an ad-hoc manner. Integrating with Spring's transaction
management features provides an elegant means to achieve this common
design goal. There are many other benefits to integration with Spring's
transaction management features, see Chapter 17, Transaction management for
more information.

Provider Independent Code: In .NET 1.1 writing provider independent
code was difficult for a variety of reasons. The most prominent was the
lack of a lack of a central factory for creating interface based
references to the core ADO.NET classes such as IDbConnection, IDbCommand,
DbParameter etc. In addition, the APIs exposed by many of these interfaces
were minimal or incomplete - making for tedious code that would otherwise
be more easily developed with provider specific subclasses. Lastly, there
was no common base class for data access exceptions across the providers.
.NET 2.0 made many changes for the better in that regard across all these
areas of concern - and Spring only plugs smaller holes in that regard to
help in the portability of your data access code.

Resource Management: The 'using' block is the heart of elegant
resource management in .NET from the API perspective. However, despite its
elegance, writing 2-3 nested using statements for each data access method
also starts to be tedious, which introduces the risk of forgetting to do
the right thing all the time in terms of both direct
coding and 'cut-n-paste' errors. Spring centralizes this resource
management in one spot so you never forget or make a mistake and rely on
it always being done correctly.

Parameter management: Frequently much of data access code is related
to creating appropriate parameters. To alleviate this boiler plate code
Spring provides a parameter 'builder' class that allows for succinct
creation of parameter collections. Also, for the case of stored
procedures, parameters can be derived from the database itself which
reduces parameter creation code to just one line.

Frequently result set data is converted into objects. Spring
provides a simple framework to organize that mapping task and allows you
to reuse mapping artifacts across your application.

Exceptions: The standard course of action when an exception is
thrown from ADO.NET code is to look up the error code and then re-run the
application to set a break point where the exception occurred so as to see
what the command text and data values were that caused the exception.
Spring provides exceptions translation from these error codes (across
database vendors) to a Data Access Object exception hierarchy. This allows
you to quickly understand the category of the error that occurred and also
the 'bad' data which lead to the exception.

Warnings: A common means to extract warning from the database, and
to optionally treat those warnings as a reason to rollback is not directly
supported with the new System.Data.Common API

Portability: Where possible, increase the portability of code across
database provider in the higher level API. The need adding of a parameter
prefix, i.e. @ for SqlServer or ':' for oracle is one such example of an
area where a higher level API can offer some help in making your code more
portable.

Note that Spring's ADO.NET framework is just 'slightly' above the
raw API. It does not try to compete with other higher level persistence
abstractions such as result set mappers (iBATIS.NET) or other ORM tools
(NHibernate). (Apologies if your favorite is left out of that short list).
As always, pick and choose the appropriate level of abstraction for the
task at hand. As a side note, Spring does offer integration with higher
level persistence abstractions (currently NHibernate) providing such
features as integration with Spring's transaction management features as
well as mixing orm/ado.net operations within the same transaction.

20.3. Provider Abstraction

Before you get started executing queries against the database you
need to connect to it. Chapter 19, DbProvider covers this topic in
detail so we only discuss the basic idea of how to interact with the
database in this section. One important ingredient that increases the
portability of writing ADO.NET applications is to refer to the base
ADO.NET interfaces, such as IDbCommand or IDbParameter in your code.
However, In the .NET 1.1 BCL the only means to obtain references to
instances of these interfaces is to directly instantiate the classes, i.e.
for SqlServer this would be

IDbCommand command = new SqlCommand();

One of the classic creational patterns in the GoF Design Patterns book
addresses this situation directly, the Abstract Factory pattern. This
approach was applied in the .NET BCL with the introduction of the
DbProviderFactory class which contains various factory methods that create
the various objects used in ADO.NET programming. In addition, .NET 2.0
introduced new abstract base classes that all ADO.NET providers must
inherit from. These base classes provide more core functionality and
uniformity across the various providers as compared to the original
ADO.NET interfaces.

Spring's database provider abstraction has a similar API to that of
.ADO.NET 2.0's DbProviderFactory. The central interface is IDbProvider and
it has factory methods that are analogous to those in the
DbProviderFactory class except that they return references to the base
ADO.NET interfaces. Note that in keeping with the Spring Framework's
philosophy, IDbProvider is an interface, and can thus be easily mocked or
stubbed as necessary. Another key element of this interface is the
ConnectionString property that specifies the specific runtime information
necessary to connect to the provider. The interface also has a IDbMetadata
property that contains minimal database metadata information needed to
support the functionality in rest of the Spring ADO.NET framework. It is
unlikely you will need to use the DatabaseMetadata class directly in your
application.

20.3.1. Creating an instance of IDbProvider

Each database vendor is associated with a particular
implementation of the IDbProvider interfaces. A variety of
implementations are provided with Spring such as SqlServer, Oracle and
MySql. Refer to the documentation on Spring's DbProvider for creating a
configuration for database that is not yet provided. The programmatic
way to create an IDbProvider is shown below

Finally the Spring.Data.Support namespace is
where you find the IAdoExceptionTransactor translation
functionality and some utility classes.

20.5. Approaches to Data Access

Spring provides two styles to interact with ADO.NET. The first is a
'template' based approach in which you create an single instance of
AdoTemplate to be used by all your DAO implementations.
Your DAO methods are frequently implemented as a single method call on the
template class as described in detail in the following section. The other
approach a more object-oriented manner that models database operations as
objects. For example, one can encapsulate the functionality of a database
query via an AdoQuery class and a create/update/delete
operation as a AdoNonQuery class. Stored procedures are
also modelled in this manner via the class
StoredProcedure. To use these classes you inherit from
them and define the details of the operation in the constructor and
implement an abstract method. This reads very cleanly when looking at DAO
method implementation as you can generally see all the details of what is
going on.

Generally speaking, experience has shown that the AdoTemplate
approach reads very cleanly when looking at DAO method implementation as
you can generally see all the details of what is going on as compared to
the object based approach. The object based approach however, offers some
advantages when calling stored procedures since it acts as a cache of
derived stored procedure arguments and can be invoked passing a variable
length argument list to the 'execute' method. As always, take a look at
both approaches and use the approach that provides you with the most
benefit for a particular situation.

20.6. Introduction to AdoTemplate

The class AdoTemplate is at the heart of Spring's
ADO.NET support. It is based on an Inversion of Control (i.e. callback)
design with the central method 'Execute' handing you a
IDbCommand instance that has its Connection and
Transaction properties set based on the transaction context of the calling
code. All resource management is handled by the framework, you only need
to focus on dealing with the IDbCommand object. The
other methods in this class build upon this central 'Execute' method to
provide you a quick means to execute common data access scenarios.

There are two implementations of AdoTemplate. The
one that uses Generics and is in the namespace
Spring.Data.Generic and the other non-generic version
in Spring.Data. In either case you create an instance
of an AdoTemplate by passing it a
IDbProvider instance as shown below

AdoTemplate adoTemplate = new AdoTemplate(dbProvider);

AdoTemplate is a thread-safe class and as such a
single instance can be used for all data access operations in you
applications DAOs. AdoTemplate implements an
IAdoOperations interface. Although the
IAdoOperations interface is more commonly used for
testing scenarios you may prefer to code against it instead of the direct
class instance.

If you are using the generic version of AdoTemplate you can access
the non-generic version via the property ClassicAdoTemplate.

The following two sections show basic usage of the
AdoTemplate 'Execute' API for .NET 1.1 and 2.0.

20.6.1. Execute Callback

The Execute method and its associated
callback function/inteface is the basic method upon which all the other
methods in AdoTemplate delegate their work. If you
can not find a suitable 'one-liner' method in
AdoTemplate for your purpose you can always fall back
to the Execute method to perform any database
operation while benefiting from ADO.NET resource management and
transaction enlistment. This is commonly the case when you are using
special provider specific features, such as XML or BLOB support.

20.6.2. Execute Callback in .NET 2.0

In this example a simple query against the 'Northwind' database is
done to determine the number of customers who have a particular postal
code.

The DbCommand that is passed into the
anonymous delegate is already has it Connection property set to the
corresponding value of the dbProvider instance used to create the
template. Furthermore, the Transaction property
of the DbCommand is set based on the transactional
calling context of the code as based on the use of Spring's transaction
management features. Also note the feature of anonymous delegates to
access the variable 'postalCode' which is defined 'outside' the
anonymous delegate implementation. The use of anonymous delegates is a
powerful approach since it allows you to write compact data access code.
If you find that your callback implementation is getting very long, it
may improve code clarity to use an interface based version of the
callback function, i.e. an ICommandCallback shown
below.

As you can see, only the most relevant portions of the data access
task at hand need to be coded. (Note that in this simple example you
would be better off using AdoTemplate's ExecuteScalar method directly.
This method is described in the following sections). As mentioned
before, the typical usage scenario for the Execute callback would
involve downcasting the passed in DbCommand object to
access specific provider API features.

There is also an interface based version of the execute method.
The signatures for the delegate and interface are shown below

While it is common for .NET 2.0 ADO.NET provider implementations
to inherit from the base class System.Data.Common.DbCommand, that is not
a requirement. To accommodate the few that don't, which as of this
writing are the latest Oracle (ODP) provider, Postgres, and DB2 for
iSeries, two additional execute methods are provided. The only
difference is the use of callback and delegate implementations that have
IDbCommand and not DbCommand as callback arguments. The following
listing shows these methods on AdoTemplate.

Internally the AdoTemplate implementation
delegates to implementations of IDbCommandCallback so
that the 'lowest common denominator' API is used to have maximum
portability. If you accidentally call
Execute<T>(ICommandCallback action)and the
command does not inherit from DbCommand, an
InvalidDataAccessApiUsageException will be
thrown.

Depending on how portable you would like your code to be, you can
choose among the two callback styles. The one based on
DbCommand has the advantage of access to the more
user friendly DbParameter class as compared to
IDbParameter obtained from
IDbCommand.

20.6.3. Execute Callback in .NET 1.1

>

AdoTemplate differs from its .NET 2.0 generic counterpart in that
it exposes the interface IDbCommand in its 'Execute'
callback methods and delegate as compared to the abstract base class
DbProvider. Also, since anonymous delegates are not
available in .NET 1.1, the typical usage pattern requires you to create
a explicitly delegate and/or class that implements the
ICommandCallback interface. Example code to query In
.NET 1.1 the 'Northwind' database is done to determine the number of
customers who have a particular postal code is shown below.

Note that you have to cast to the appropriate object type returned
from the execute method.

20.6.4. Quick Guide to AdoTemplate Methods

There are many methods in AdoTemplate so it is easy to feel a bit
overwhelmed when taking a look at the SDK documentation. However, after
a while you will hopefully find the class 'easy to navigate' with
intellisense. Here is a quick categorization of the method names and
their associated data access operation. Each method is overloaded to
handle common cases of passing in parameter values.

The generic 'catch-all' method

Execute - Allows you to perform any
data access operation on a standard DbCommand object. The connection
and transaction properties of the DbCommand are already set based on
the transactional calling context. There is also an overloaded
method that operates on a standard IDbCommand object. This is for
those providers that do not inherit from the base class
DbCommand.

The following methods mirror those on the DbCommand object.

ExecuteNonQuery - Executes the
'NonQuery' method on a DbCommand, applying provided parameters and
returning the number of rows affected.

ExecuteScalar - Executes the 'Scalar'
method on a DbCommand, applying provided parameters, and returning
the first column of the first row in the result set.

Mapping result sets to objects

QueryWithResultSetExtractor - Execute
a query mapping a result set to an object with an implementation of
the IResultSetExtractor interface.

QueryWithResultSetExtractorDelegate -
Same as QueryWithResultSetExtractor but using a
ResultSetExtractorDelegate to perform result set
mapping.

QueryWithRowCallback - Execute a
query calling an implementation of IRowCallback
for each row in the result set.

QueryWithRowCallbackDelegate - Same
as QueryWithRowCallback but calling a
RowCallbackDelegate for each row.

QueryWithRowMapper - Execute a query
mapping a result set on a row by row basis with an implementation of
the IRowMapper interface.

QueryWithRowMapperDelegate - Same as
QueryWithRowMapper but using a RowMapperDelegate
to perform result set row to object mapping.

Mapping result set to a single object

QueryForObject - Execute a query
mapping the result set to an object using a
IRowMapper. Exception is thrown if the query does
not return exactly one object.

Query with a callback to create the DbCommand object. These are
generally used by the framework itself to support other functionality,
such as in the Spring.Data.Objects namespace.

QueryWithCommandCreator - Execute a
query with a callback to IDbCommandCreator to
create a IDbCommand object and using either a IRowMapper or
IResultSetExtractor to map the result set to an object. One
variation lets multiple result set 'processors' be specified to act
on multiple result sets and return output parameters.

DataTable and DataSet operations

DataTableCreate - Create and Fill
DataTables

DataTableCreateWithParameters -
Create and Fill DataTables using a parameter collection.

In turn each method typically has four overloads, one with no
parameters and three for providing parameters. Aside from the
DataTable/DataSet operations, the three parameter overloads are of the
form shown below

The first overload is a convenience method when you only have one
parameter to set. The database enumeration is the base class 'Enum'
allowing you to pass in any of the provider specific enumerations as
well as the common DbType enumeration. This is a trade off of
type-safety with provider portability. (Note generic version could be
improved to provide type safety...).

The second overload contains a collection of parameters. The data
type is Spring's IDbParameters collection class discussed in the
following section.

The third overload is a callback interface allowing you to set the
parameters (or other properties) of the IDbCommand passed to you by the
framework directly.

If you are using .NET 2.0 the delegate versions of the methods are
very useful since very compact definitions of database operations can be
created that reference variables local to the DAO method. This removes
some of the tedium in passing parameters around with interface based
versions of the callback functions since they need to be passed into the
constructor of the implementing class. The general guideline is to use
the delegate when available for functionality that does not need to be
shared across multiple DAO classes or methods and use interface based
version to reuse the implementation in multiple places. The .NET 2.0
versions make use of generics where appropriate and therefore enhance
type-safety.

20.6.5. Quick Guide to AdoTemplate Properties

AdoTemplate has the following properties that you can
configure

LazyInit - Indicates if the
IAdoExceptionTranslator should be created on
first encounter of an exception from the data provider or when
AdoTemplate is created. Default is true, i.e. to
lazily instantiate.

ExceptionTranslator - Gets or sets the
implementation of IAdoExceptionTranslator to use.
If no custom translator is provided, a default
ErrorCodeExceptionTranslator is used.

DbProvider - Gets or sets the
IDbProvider instance to use.

DataReaderWrapperType - Gets or set the
System.Type to use to create an instance of
IDataReaderWrapper for the purpose of providing
extended mapping functionality. Spring provides an implementation to
use as the basis for a mapping strategy that will map
DBNull values to default values based on the
standard IDataReader interface. See the section
custom IDataReader implementations
for more information.

CommandTimeout - Gets or sets the command
timeout for IDbCommands that this AdoTemplate
executes. Default is 0, indicating to use the database provider's
default.

20.7. Transaction Management

The AdoTemplate is used in conjunction with an implementation of a
IPlatformTransactionManager, which is Spring's portable
transaction management API. This section gives a brief overview of the
transaction managers you can use with AdoTemplate and the details of how
you can retrieve the connection/transaction ADO.NET objects that are bound
to the thread when a transaction starts. Please refer to the section key abstractions in the chapter on
transactions for more comprehensive introduction to transaction
management.

To use local transactions, those with only one transactional
resource (i.e. the database) you will typically use
AdoPlatformTransactionManager. If you need to mix
Hibernate and ADO.NET data access operations within the same local
transaction you should use HibernatePlatformTransaction
manager which is described more in the section on ORM transaction management.

While it is most common to use Spring's transaction management features to avoid the
low level management of ADO.NET connection and transaction objects, you
can retrieve the connection/transaction pair that was created at the start
of a transaction and bound to the current thread. This may be useful for
some integration with other data access APIs. The can be done using the
utility class ConnectionUtils as shown below.

It is possible to provide a wrapper around the standard .NET
provider interfaces such that you can use the plain ADO.NET API in
conjunction with Spring's transaction management features.

If you are using
ServiceDomainPlatformTransactionManager or
TxScopePlatformTransactionManager then you can retrieve
the currently executing transaction object via the standard .NET
APIs.

20.8. Exception Translation

AdoTemplate's methods throw exceptions within a Data Access Object
(DAO) exception hierarchy described in Chapter 18, DAO support. In
addition, the command text and error code of the exception are extracted
and logged. This leads to easier to write provider independent exception
handling layer since the exceptions thrown are not tied to a specific
persistence technology. Additionally, for ADO.NET code the error messages
logged provide information on the SQL and error code to better help
diagnose the issue.

20.9. Parameter Management

A fair amount of the code in ADO.NET applications is related to the
creation and population of parameters. The BCL parameter interfaces are
very minimal and do not have many convenience methods found in provider
implementations such as SqlClient. Even still, with SqlClient, there is a
fair amount of verbosity to creating and populating a parameter
collection. Spring provides two ways to make this mundane task easier and
more portable across providers.

20.9.1. IDbParametersBuilder

Instead of creating a parameter on one line of code, then setting
its type on another and size on another, a builder and parameter
interface, IDbParametersBuilder and
IDbParameter respectfully, are provided so that this
declaration process can be condensed. The IDbParameter support chaining
calls to its methods, in effect a simple language-constrained domain
specific language, to be fancy about it. Here is an example of it in
use.

IDbParametersBuilder builder = CreateDbParametersBuilder();
builder.Create().Name("Country").Type(DbType.String).Size(15).Value(country);
builder.Create().Name("City").Type(DbType.String).Size(15).Value(city);
// now get the IDbParameters collection for use in passing to AdoTemplate methods.
IDbParameters parameters = builder.GetParameters();

Please note that IDbParameters and
IDbParameter are not part of the BCL, but part of the
Spring.Data.Common namespace. The IDbParameters collection is a frequent
argument to the overloaded methods of AdoTemplate.

The parameter prefix, i.e. '@' in Sql Server, is not required to
be added to the parameter name. The DbProvider is aware of this metadata
and AdoTemplate will add it automatically if required before
execution.

An additional feature of the IDbParametersBuilder is to create a
Spring FactoryObject that creates IDbParameters for use in the XML
configuration file of the IoC container. By leveraging Spring's
expression evaluation language, the above lines of code can be taken as
text from the XML configuration file and executed. As a result you can
externalize your parameter definitions from your code. In combination
with abstract object definitions and importing of configuration files
your increase the chances of having one code base support multiple
database providers just by a change in configuration files.

20.9.2. IDbParameters

This class is similar to the parameter collection class you find
in provider specific implementations of IDataParameterCollection. It
contains a variety of convenience methods to build up a collection of
parameters.

The parameter prefix, i.e. '@' in Sql Server, is not required to
be added to the parameter name. The DbProvider is aware of this metadata
and AdoTemplate will add it automatically if required before
execution.

20.9.3. Parameter names in SQL text

While the use of IDbParameters or
IDbParametersBuilder will remove the need for use
to vendor specific parameter prefixes when creating a parameter
collection, @User in Sql SqlSerer vs. :User in Oracle, you still need to
specify the vendor specific parameter prefix in the SQL Text. Portable
SQL in this regard is possible to implement, it is available as a
feature in Spring Java. If you would like such a feature, please raise
an issue.

20.10. Custom IDataReader implementations

The passed in implementation of IDataReader can
be customized. This lets you add a strategy for handling null values to
the standard methods in the IDataReader interface or to
provide sub-interface of IDataReader that contains extended functionality,
for example support for default values. In callback code, i.e. IRowMapper
and associated delegate, you would downcast to the sub-interface to
perform processing.

Spring provides a class to map DBNull values to
default values. When reading from a IDataReader there is often the need to
map DBNull values to some default values, i.e. null or
say a magic number such as -1. This is usually done via a ternary operator
which decreases readability and also increases the likelihood of mistakes.
Spring provides an IDataReaderWrapper interface (which
inherits from the standard IDataReader) so that you can
provide your own implementation of a IDataReader that will perform DBNull
mapping for you in a consistent and non invasive manner to your result set
reading code. A default implementation,
NullMappingDataReader is provided which you can
subclass to customize or simply implement the
IDataReaderWrapper interface directly. This interface
is shown below

All of AdoTemplates callback interfaces/delegates that have an
IDataReader as an argument are wrapped with a
IDataReaderWrapper if the AdoTemplate has been
configured with one via its DataReaderWrapperType
property. Your implementation should support a zero-arg
constructor.

Frequently you will use a common mapper for DBNull across your
application so only one instance of AdoTemplate and
IDataReaderWrapper in required. If you need to use
multiple null mapping strategies you will need to create multiple
instances of AdoTemplate and configure them
appropriately in the DAO objects.

20.11. Basic data access operations

The 'ExecuteNonQuery' and 'ExecuteScalar' methods of
AdoTemplate have the same functionality as the same
named methods on the DbCommand object

20.11.1. ExecuteNonQuery

ExecuteNonQuery is used to perform create, update, and delete
operations. It has four overloads listed below reflecting different ways
to set the parameters.

20.11.2. ExecuteScalar

20.12. Queries and Lightweight Object Mapping

A common ADO.NET development task is reading in a result set and
converting it to a collection of domain objects. The family of QueryWith
methods on AdoTemplate help in this task. The responsibility of performing
the mapping is given to one of three callback interfaces/delegates that
you are responsible for developing. These callback interfaces/delegates
are:

IResultSetExtractor / ResultSetExtractorDelegate - hands you a
IDataReader object for you to iterate over and return a result
object.

IRowCallback / RowCallbackDelegate - hands you a IDataReader to
process the current row. Returns void and as such is usually stateful
in the case of IRowCallback implementations or uses a variable to
collect a result that is available to an anonymous delegate.

IRowMapper / RowMapperDelegate - hands you a IDataReader to
process the current row and return an object corresponding to that
row.

There are generic versions of the IResultSetExtractor and IRowMapper
interfaces/delegates providing you with additional type-safety as compared
to the object based method signatures used in the .NET 1.1
implementation.

As usual with callback APIs in Spring.Data, your implementations of
these interfaces/delegates are only concerned with the core task at hand -
mapping data - while the framework handles iteration of readers and
resource management.

Each 'QueryWith' method has 4 overloads to handle common ways to
bind parameters to the command text.

The following sections describe in more detail how to use Spring's
lightweight object mapping framework.

20.12.1. ResultSetExtractor

The ResultSetExtractor gives you control to iterate over the
IDataReader returned from the query. You are responsible for iterating
through all the result sets and returning a corresponding result object.
Implementations of IResultSetExtractor are typically stateless and
therefore reusable as long as the implementation doesn't access stateful
resources. The framework will close the IDataReader for you.

The interface and delegate signature for ResutSetExtractors is
shown below for the generic version in the Spring.Data.Generic
namespace

Internally the implementation of the QueryWithRowCallback and
QueryWithRowMapper methods are specializations of the general
ResultSetExtractor. For example, the QueryWithRowMapper implementation
iterates through the result set, calling the callback method 'MapRow'
for each row and collecting the results in an IList. If you have a
specific case that is not covered by the QueryWithXXX methods you can
subclass AdoTemplate and follow the same implementation pattern to
create a new QueryWithXXX method to suit your needs.

20.12.2. RowCallback

The RowCallback is usually a stateful object itself or populates
another stateful object that is accessible to the calling code. Here is
a sample take from the Data QuickStart

20.12.3. RowMapper

The RowMapper lets you focus on just the logic to map a row of
your result set to an object. The creation of a IList to store the
results and iterating through the IDataReader is handled by the
framework. Here is a simple example taken from the Data QuickStart
application

20.12.4. Query for a single object

The QueryForObject method is used when you expect there to be
exactly one object returned from the mapping, otherwise a
Spring.Dao.IncorrectResultSizeDataAccessException will be thrown. Here
is some sample usage taken from the Data QuickStart.

20.12.5. Query using a CommandCreator

There is a family of overloaded methods that allows you to
encapsulate and reuse a particular configuration of a
IDbCommand object. These methods also allow for
access to returned out parameters as well as a method that allows
processing of multiple result sets. These methods are used internally to
support the classes in the Spring.Data.Objects
namespace and you may find the API used in that namespace to be more
convenient. The family of methods is listed below.

The list must contain objects of the type
Spring.Data.Support.NamedResultSetProcessor. This is
the class responsible for associating a name with a result set
processor. The constructors are listed below.

The results of the RowMapper or ResultSetExtractor are retrieved
by name from the dictionary that is returned. RowCallbacks, being
stateless, only have the placeholder text, "ResultSet returned was
processed by an IRowCallback" as a value for the name of the RowCallback
used as a key. Output and InputOutput parameters can be retrieved by
name. If this parameter name is null, then the index of the parameter
prefixed with the letter 'P' is a key name, i.e P2, P3, etc.

The namespace Spring.Data.Objects.Generic contains generic
versions of these methods. These are listed below

The list of result set processors contains either objects of the
type Spring.Data.Generic.NamedResultSetProcessor<T> or
Spring.Data.NamedResultSetProcessor. The generic result set processors,
NamedResultSetProcessor<T>, is used to process the first result
set in the case of using QueryWithCommandCreator<T> and to process
the first and second result set in the case of using
QueryWithCommandCreator<T,U>. Additional
Spring.Data.NamedResultSetProcessors that are listed can be used to
process additional result sets. If you specify a RowCallback with
NamedResultSetProcessor<T>, you still need to specify a type
parameter (say string) because the RowCallback processor does not return
any object. It is up to subclasses of RowCallback to collect state due
to processing the result set which is later queried.

20.13. DataTable and DataSet

AdoTemplate contains several 'families' of methods to help remove
boilerplate code and reduce common programming errors when using
DataTables and DataSets. There are many methods in AdoTemplate so it is
easy to feel a bit overwhelmed when taking a look at the SDK
documentation. However, after a while you will hopefully find the class
'easy to navigate' with intellisense. Here is a quick categorization of
the method names and their associated data access operation. Each method
is overloaded to handle common cases of passing in parameter
values.

The 'catch-all' Execute methods upon which other functionality is
built up upon are shown below.

In Spring.Data.Core.AdoTemplate

object Execute(IDataAdapterCallback
dataAdapterCallback) - Execute ADO.NET operations on a
IDbDataAdapter object using an interface based callback.

The passed in IDbDataAdapter will have its
SelectCommand property created and set with its
Connection and Transaction
values based on the calling transaction context. The return value is the
result of processing or null.

There are type-safe versions of this method in
Spring.Data.Generic.AdoTemplate

T Execute<T>(IDataAdapterCallback<T>
dataAdapterCallback) - Execute ADO.NET operations on a
IDbDataAdapter object using an interface based callback.

T Execute<T>(DataAdapterDelegate<T> del)
- Execute ADO.NET operations on a IDbDataAdapter object
using an delegate based callback.

Where IDataAdapterCallback<T> and DataAdapterDelegate<T>
are defined as

20.13.1. DataTables

DataTable operations are available on the class
Spring.Data.Core.AdoTemplate. If you are using the
generic version, Spring.Data.Generic.AdoTemplate, you
can access these methods through the property
ClassicAdoTemplate, which returns the non-generic
version of AdoTemplate. DataTable operations available fall into the
general family of methods with 3-5 overloads per method.

DataTableCreate - Create and Fill
DataTables

DataTableCreateWithParameters -
Create and Fill DataTables using a parameter collection.

DataTableFill - Fill a pre-existing
DataTable.

DataTableFillWithParameters - Fill a
pre-existing DataTable using a parameter collection.

DataTableUpdateWithCommandBuilder -
Update the database using the provided DataTable, select SQL, and
parameters.

20.13.2. DataSets

DataSet operations are available on the class
Spring.Data.Core.AdoTemplate. If you are using the
generic version, Spring.Data.Generic.AdoTemplate, you
can access these methods through the property
ClassicAdoTemplate, which returns the non-generic
version of AdoTemplate. DataSet operations available fall into the
following family of methods with 3-5 overloads per method.

DataSetCreate - Create and Fill
DataSets

DataSetCreateWithParameters - Create
and Fill DataTables using a parameter collection.

Updating a DataSet can be done using a CommandBuilder,
automatically created from the specified select command and select
parameters, or by explicitly specifying the insert, update, delete
commands and parameters. Below is an example, refer to the SDK
documentation for additional overloads

In the case of needing to set parameter SourceColumn or
SourceVersion properties it may be more convenient to use
IDbParameterBuilder.

20.14. TableAdapters and participation in transactional context

Typed DataSets need to have commands in their internal DataAdapters
and command collections explicitly set with a connection/transaction in
order for them to correctly participate with a surrounding transactional
context. The reason for this is by default the code generated is
explicitly managing the connections and transactions. This issue is very
well described in the article System.Transactions
and ADO.NET 2.0 by ADO.NET guru Sahil Malik. Spring offers a
convenience method that will use reflection to internally set the
transaction on the table adapter's internal command collection to the
ambient transaction. This method on the class
Spring.Data.Support.TypedDataSetUtils and is named
ApplyConnectionAndTx. Here is sample usage of a
DAO method that uses a VS.NET 2005 generated typed dataset for a
PrintGroupMapping table.

The method that takes IDbCommand is a convenience if you will be
using AdoTemplate callback's as the passed in command object will already
have its connection and transaction properties set based on the current
transactional context. The method that takes an IDbProvider is convenient
to use when you have data access logic that is not contained within a
single callback method but is instead spead among multiple classes. In
this case passing the transactionally aware IDbCommand object can be
intrusive on the method signatures. Instead you can pass in an instance of
IDbProvider that can be obtained via standard dependency injection
techniques or via a service locator style lookup.

20.15. Database operations as Objects

The Spring.Data.Objects and Spring.Data.Objects.Generic
namespaces contains classes that allow one to access the
database in a more object-oriented manner. By way of an example, one can
execute queries and get the results back as a list containing business
objects with the relational column data mapped to the properties of the
business object. One can also execute stored procedures and run update,
delete and insert statements.

Note

There is a view borne from experience acquired in the field
amongst some of the Spring developers that the various RDBMS operation
classes described below (with the exception of the StoredProcedure class) can often be
replaced with straight AdoTemplate calls... often
it is simpler to use and plain easier to read a DAO method that simply
calls a method on a AdoTemplate direct (as opposed
to encapsulating a query as a full-blown class).

It must be stressed however that this is just a
view... if you feel that you are getting
measurable value from using the RDBMS operation classes, feel free to
continue using these classes.

20.15.1. AdoQuery

AdoQuery is a reusable, threadsafe class that
encapsulates an SQL query. Subclasses must implement the
NewRowMapper(..) method to provide a
IRowMapper instance that can create one object per
row obtained from iterating over the IDataReader that
is created during the execution of the query. The
AdoQuery class is rarely used directly since the
MappingAdoQuery subclass provides a much more
convenient implementation for mapping rows to .NET classes. Another
implementations that extends AdoQuery is
MappingadoQueryWithParameters (See SDK docs for
details).

The AdoNonQuery class encapsulates an
IDbCommand 's ExecuteNonQuery method functionality. Like the
AdoQuery object, an AdoNonQuery
object is reusable, and like all AdoOperation
classes, an AdoNonQuery can have parameters and is
defined in SQL. This class provides two execute methods

IDictionary ExecuteNonQuery(params object[]
inParameterValues)

IDictionary ExecuteNonQueryByNamedParam(IDictionary
inParams)

This class is concrete. Although it can be subclassed (for example
to add a custom update method) it can easily be parameterized by setting
SQL and declaring parameters.

An example of an AdoQuery subclass to encapsulate an insert
statement for a 'TestObject' (consisting only name and age columns) is
shown below

20.15.2. MappingAdoQuery

MappingAdoQuery is a reusable query in which
concrete subclasses must implement the abstract
MapRow(..) method to convert each row of the
supplied IDataReader into an object. Find below a
brief example of a custom query that maps the data from a relation to an
instance of the Customer class.

20.15.3. AdoNonQuery

The AdoNonQuery class encapsulates an
IDbCommand 's ExecuteNonQuery method functionality. Like the
AdoQuery object, an AdoNonQuery
object is reusable, and like all AdoOperation
classes, an AdoNonQuery can have parameters and is
defined in SQL. This class provides two execute methods

IDictionary ExecuteNonQuery(params object[]
inParameterValues)

IDictionary ExecuteNonQueryByNamedParam(IDictionary
inParams)

This class is concrete. Although it can be subclassed (for example
to add a custom update method) it can easily be parameterized by setting
SQL and declaring parameters.

20.15.4. Stored Procedure

The StoredProcedure class is designed to make it as simple as
possible to call a stored procedure. It takes advantage of metadata
present in the database to look up names of in and out parameters.. This
means that you don't have to explicitly declare parameters. You can of
course still declare them if you prefer. There are two versions of the
StoredProcedure class, one that uses generics and one that doesn't.
Using the StoredProcedure class consists of two steps, first defining
the in/out parameter and any object mappers and second executing the
stored procedure.

The non-generic version of StoredProcedure is in the namespace
Spring.Data.Objects. It contains the following methods to execute a
stored procedure

IDictionary ExecuteScalar(params object[]
inParameterValues)

IDictionary ExecuteScalarByNamedParam(IDictionary
inParams)

IDictionary ExecuteNonQuery(params object[]
inParameterValues)

IDictionary ExecuteNonQueryByNamedParam(IDictionary
inParams)

IDictionary Query(params object[]
inParameterValues)

IDictionary QueryByNamedParam(IDictionary
inParams)

Each of these methods returns an IDictionary
that contains the output parameters and/or any results from Spring's
object mapping framework. The arguments to these methods can be a
variable length argument list, in which case the order must match the
parameter order of the stored procedure. If the argument is an
IDictionary it contains parameter key/value pairs. Return values from
stored procedures are contained under the key
"RETURN_VALUE".

The standard in/out parameters for the stored procedure can be set
programmatically by adding to the parameter collection exposed by the
property DeclaredParameters. For each result sets that is returned by
the stored procedures you can registering either an
IResultSetExtractor, IRowCallback,
or IRowMapper by name, which is used later to extract
the mapped results from the returned
IDictionary.

Lets take a look at an example. The following stored procedure
class will call the CustOrdersDetail stored procedure in the Northwind
database, passing in the OrderID as a stored procedure argument and
returning a collection of OrderDetails business objects.

The 'DeriveParameters' method saves you the
trouble of having to declare each parameter explicitly. When using
DeriveParameters is it often common to use the Query
method that takes a variable length list of arguments. This assumes
additional knowledge on the order of the stored procedure arguments. If
you do not want to follow this loose shorthand convention, you can call
the method QueryByNamesParameters instead passing in
a IDictionary of parameter key/value pairs.

Note

If you would like to have the return value of the stored
procedure included in the returned dictionary, pass in
true as a method parameter to
DeriveParameters().

The StoredProcedure class is threadsafe once
'compiled', an act which is usually done in the constructor. This sets
up the cache of database parameters that can be used on each call to
Query or QueryByNamedParam. The implementation of
IRowMapper that is used to extract the business
objects is 'registered' with the class and then later retrieved by name
as a fictional output parameter. You may also register
IRowCallback and
IResultSetExtractor callback interfaces via the
AddRowCallback and
AddResultSetExtractor methods.

The generic version of StoredProcedure is in the namespace
Spring.Data.Objects.Generic. It allows you to define up to two generic
type parameters that will be used to process result sets returned from
the stored procedure. An example is shown below