Chapter 19. DbProvider

19.1. Introduction

Spring provides a generic factory for creating ADO.NET API artifacts
such as IDbConnection and
IDbCommand. The factory API is very
similar to the one introduced in .NET 2.0 but adds extra metadata needed
by Spring to support features provided by its DAO/ADO.NET framework such
as error code translation to a DAO exception hierarchy. The factory itself
is configured by using a standard Spring XML based configuration file
though it is unlikely you will need to modify those settings yourself, you
only need be concerned with using the factory. Out of the box several
popular databases are supported and an extension mechanism is available
for defining new database providers or modifying existing ones. A custom
database namespace for configuration aids in making terse XML based
declarations of Spring's database objects you wish to use.

The downside of Spring's factory as compared to the one in .NET 2.0
is that the types returned are lower level interfaces and not the abstract
base classes in System.Data.Common. However, there are still 'holes' in
the current .NET 2.0 provider classes that are 'plugged' with Spring's
provider implementation. One of the most prominent is the that the top
level DbException exposes the HRESULT of the remote procedure call, which
is not what you are commonly looking for when things go wrong. As such
Spring's provider factory exposes the vendor sql error code and also maps
that error code onto a consistent data access exception hierarchy. This
makes writing portable exception handlers much easier. In addition, the
DbParameter class doesn't provide the most common convenient methods you
would expect as when using say the SqlServer provider. If you need to
access the BCL provider abstraction, you still can through Spring's
provider class. Furthermore, a small wrapper around the standard BCL
provider abstraction allows for integration with Spring's transaction
management facilities, allowing you to create a DbCommand with its
connection and transaction properties already set based on the transaction
calling context.

19.2. IDbProvider and DbProviderFactory

The IDbProvider API is shown below
and should look familiar to anyone using .NET 2.0 data providers. Note
that Spring's DbProvider abstraction can be used on .NET 1.1 in addition
to .NET 2.0

ExtractError is used to return an error string for translation into
a DAO exception. On .NET 1.1 the method IsDataAccessException is used to
determine if the thrown exception is related to data access since in .NET
1.1 there isn't a common base class for database exceptions.
CreateParameterName is used to create the string for parameters used in a
CommandText object while CreateParameterNameForCollection is used to
create the string for a IDataParameter.ParameterName, typically contained
inside a IDataParameterCollection.

The class DbProviderFactory creates IDbProvider
instances given a provider name. The connection string property will be
used to set the IDbConnection returned by the factory if present. The
provider names, and corresponding database, currently configured are
listed below.

If your exact version of the database provider is not listed, you
can pick the general provider name, i.e.
MySql.Data.MySqlClient, and then perform an assembly
redirect in App.config. This will often be sufficient to upgrade to
newer versions. As shown below

The default definitions of the providers are contained in the
assembly resource
assembly://Spring.Data/Spring.Data.Common/dbproviders.xml. If
the provider you want to use is not provided "out of the box" you can
provide additional definitions. To do this follow the format of object
definitions defined in the previously mentioned assembly resource.

From Spring 1.3.1 an on you can specify the additional Spring
IResource location where additional providers are defined within Spring's
XML configuration file. See the next section for an example.
Alternatively, you can set the public static property
DBPROVIDER_ADDITIONAL_RESOURCE_NAME in
DbProviderFactory to a Spring resource location. The
default value is file://dbProviders.xml. (That isn't a typo,
there is a difference in case with the name of the embedded
resource).

It may happen that the version number of an assembly you have
downloaded is different than the one listed above. If it is a point
release, i.e. the API hasn't changed in anyway that is material to your
application, you should add an assembly redirect of the form shown
below.

If you need to register an additional IDbProvider defintions from
your own configuration file, set the attribute 'additonalDbProviders' to
the IResource location of those definitions. Examples of the format for
additional provider definitions can be found within the Spring.Data
assembly, location
assembly://Spring.Data/Spring.Data.Common/dbproviders.xml.
Open it up in Visual Studio or Reflector to see the contents of the
dbproviders.xml file.

A custom namespace should be registered in the main application
configuration file to use this syntax. This configuration, only for the
parsers, is shown below. Additional section handlers are needed to specify
the rest of the Spring configuration locations as described in previous
chapters.

19.4. Connection String management

There are a few options available to help manage your connection
strings.

The first option is to leverage the Spring property replacement
functionality, as described in Section 5.9.2.1, “Example: The
PropertyPlaceholderConfigurer”. This lets you insert
variable names as placeholders for values in a Spring configuration file.
In the following example specific parts of a connection string have been
parameterized but you can also use a variable to set the entire connection
string.

19.5. Additional IDbProvider implementations

Spring provides some convenient implementations of the IDbProvider
interface that add addtional behavior on top of the standard
implementation.

Note

These provider implementations do not take into account usage with
NHibernate. NHibernate scopes a SessionFactory, where second level
caching is managed, to each connection. This forum
thread, contains an implementation of the class
LocalDelegatingSessionFactoryObject that will create multiple
SessionFactories for each database connection.

19.5.1. UserCredentialsDbProvider

This UserCredentialsDbProvider will allow you
to change the username and password of a database connection at runtime.
The API contains the properties Username and
Password which are used as the default strings
representing the user and password in the connection string. You can
then change the value of these properties in the connection string by
calling the method SetCredentialsForCurrentThread and
fall back to the default values by calling the method
RemoveCredentialsFromCurrentThread. You call the
SetCredentialsForCurrentThread method at runtime,
before any data access occurs, to determine which database user should
be used for the current user-case. Which user to select is up to you.
You may retrieve the user information from an HTTP session for example.
Example configuration and usage is shown below

If you use dependency injection to configure a class with a
property of the type IDbProvider, you will need to
downcast to the subtype or you can change your class to have a property
of the type UserCredentialsDbProvider instead of
IDbProvider.

UserCredentialsDbProvider's has a base class,
DelegatingDbProvider, and is intended for you to use
in your own implementations that delegate calls to a target
IDbProvider instance. This class in meant to be
subclassed with subclasses overriding only those methods, such as
CreateConnection(), that should not simply delegate
to the target IDbProvider.

19.5.2. MultiDelegatingDbProvider

There are use-cases in which there will need to be a runtime
selection of the database to connect to among many possible candidates.
This is often the case where the same schema is installed in separate
databases for different clients. The
MultiDelegatingDbProvider implements the
IDbProvider interface and provides an abstraction to
the multiple databases and can be used in DAO layer such that the DAO
layer is unaware of the switching between databases.
MultiDelegatingDbProvider does its job by looking
into thread local storage. This storage location stores the name of the
dbProvider that is to be used for processing the request.

MultiDelegatingDbProvider is configured using
the dictionary property TargetDbProviders. The key of
this dictionary contains the name of a dbProvider and its value is a
dbProvider object. You can also provide this dictionary as a constructor
argument. The property DefaultDbProvider can be set
with the name of the DbProvider to use if no provider name is found in
thread local storage

During request processing, once you have determined which target
dbProvider should be use, in this example database1ProviderName, you
should execute the following code is you are using Spring 1.2 M1 or
later

If you do not change the name of the IDbProvider stored in
thread local storage during request processing, say in the web tier
where a user is identified, then you will always refer to the default
provider if the property DefaultDbProvider has
been set. If the DefaultDbProvider property has
not been set than an InvalidDataAccessApiUsageException will be
thrown.

Here is a sample configuration to build up an object definition
for MultiDelegatingDbProvider.

As seen above, MultidelegatingDbProvider works via a thread local
storage mechansims. If you prefer to place the logic to switch databases
in a single location, within a single class, then create a subclass
MultiDelegatingDbProvider and override the method GetTargetProvider. You
can then select which provider to return based on your own
implementation that does not involve thread local storage.

Note

This class is not recommended for usage with NHibernate.
NHibernate usage typically involves caches that are scoped at the
level of the SessionFactory. If you switch the database that hibernate
is pointing to and do not also managed switching the cache, then the
cache will end up with results from two different databases - which of
course you don't want to have. The helper class contained in this
post
may help you if you when using NHibernate with multiple
databases.