Chapter 19. DbProvider

The Spring.NET Framework

previous page next page

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

public interface IDbProvider
{
  IDbCommand CreateCommand();

  object CreateCommandBuilder();        

  IDbConnection CreateConnection();

  IDbDataAdapter CreateDataAdapter();

  IDbDataParameter CreateParameter();

  string CreateParameterName(string name);

  string CreateParameterNameForCollection(string name);

  IDbMetadata DbMetadata
  {
      get;
  }               

  string ConnectionString
  {
      set;
      get;
  }

  string ExtractError(Exception e);

  bool IsDataAccessException(Exception e);
    
}

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.

  • SqlServer-1.1 - Microsoft SQL Server, provider V1.0.5000.0 in framework .NET V1.1

  • SqlServer-2.0 (aliased to System.Data.SqlClient) - Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0

  • SqlServerCe-3.1 - Microsoft SQL Server Compact Edition, provider V9.0.242.0

  • SqlServerCe-3.5.1 (aliased to System.Data.SqlServerCe) - Microsoft SQL Server Compact Edition, provider V3.5.1.0

  • OleDb-1.1 - OleDb, provider V1.0.5000.0 in framework .NET V1.1

  • OleDb-2.0 (aliased to System.Data.OleDb) - OleDb, provider V2.0.0.0 in framework .NET V2.0

  • OracleClient-2.0 (aliased to System.Data.OracleClient) - Oracle, Microsoft provider V2.0.0.0

  • OracleODP-2.0 (aliased to System.DataAccess.Client) - Oracle, Oracle provider V2.102.2.20

  • MySql - MySQL, MySQL provider 1.0.10.1

  • MySql-1.0.9 - MySQL, MySQL provider 1.0.9

  • MySql-5.0 - MySQL, MySQL provider 5.0.7.0

  • MySql-5.0.8.1 - MySQL, MySQL provider 5.0.8.1

  • MySql-5.1 - MySQL, MySQL provider 5.1.2.2

  • MySql-5.1.4 - MySQL, MySQL provider 5.1.2.2

  • MySql-5.2.3 (aliased to MySql.Data.MySqlClient) MySQL, MySQL provider 5.2.3.0

  • Npgsql-1.0 - Postgresql provider 1.0.0.0 (and 1.0.0.1 - were build with same version info)

  • Npgsql-2.0-beta1 - Postgresql provider 1.98.1.0 beta 1

  • Npgsql-2.0 - Postgresql provider 2.0.0.0

  • DB2-9.0.0-1.1 - IBM DB2 Data Provider 9.0.0 for .NET Framework 1.1

  • DB2-9.0.0-2.0 - (aliased to IBM.Data.DB2) - IBM DB2 Data Provider 9.0.0 for .NET Framework 2.0

  • DB2-9.1.0-1.1 - IBM DB2 Data Provider 9.1.0 for .NET Framework 1.1

  • DB2-9.1.0.2 - (aliased to IBM.Data.DB2.9.1.0) - IBM DB2 Data Provider 9.1.0 for .NET Framework 2.0

  • SQLite-1.0.43 SQLite provider 1.0.43 for .NET Framework 2.0

  • SQLite-1.0.47 - (aliased to System.Data.SQLite) - SQLite provider 1.0.43 for .NET Framework 2.0

  • SybaseAse-12 - Sybase ASE provider for ASE 12.x

  • SybaseAse-15 - Sybase ASE provider for ASE 15.x

  • SybaseAse-AdoNet2 - Sybase ADO.NET 2.0 provider for ASE 12.x and 15.x

  • Odbc-1.1 - ODBC provider V1.0.5000.0 in framework .NET V1.1

  • Odbc-2.0 - ODBC provider V2.0.0.0 in framework .NET V2

[Note]Note

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

<runtime>

  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

    <dependentAssembly>
      <assemblyIdentity name="Npgsql" 
                        publicKeyToken="5d8b90d52f46fda7" 
                        culture="neutral"/>
      <bindingRedirect oldVersion="0.0.0.0-65535.65535.65535.65535
                       newVersion="2.0.0.0"/>

    </dependentAssembly>

  </assemblyBinding>

</runtime>

An example using DbProviderFactory is shown below

IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");

The default definitions of the providers are contained in the assembly resource assembly://Spring.Data/Spring.Data.Common/dbproviders.xml. Future additions to round out the database coverage are forthcoming. The current crude mechanism to add additional providers, or to apply any standard Spring IApplicationContext functionality, such as applying AOP advice, is to 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). This crude mechanism will eventually be replaced with one based on a custom configuration section in App.config/Web.config.

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.

<dependentAssembly>
  <assemblyIdentity name="MySql.Data" 
                           publicKeyToken="c5687fc88969c44d" 
                           culture="neutral"/>
  <bindingRedirect oldVersion="0.0.0.0-65535.65535.65535.65535"
                         newVersion="1.0.10.1"/>
</dependentAssembly>

This redirects any reference to an older version of the assembly MySql.Data to the version 1.0.10.1.

19.3. XML based configuration

Creating a DbProvider in Spring's XML configuration file is shown below in the typical case of using it to specify the DbProvider property on an AdoTemplate.

<objects xmlns='http://www.springframework.net'
         xmlns:db="http://www.springframework.net/database">

  <db:provider id="DbProvider" 
      provider="System.Data.SqlClient" 
      connectionString="Data Source=(local);Database=Spring;User ID=springqa;Password=springqa;Trusted_Connection=False"/>
  
  <object id="adoTemplate" type="Spring.Data.AdoTemplate, Spring.Data">  
    <property name="DbProvider" ref="DbProvider"/>                
  </object>

</objects>

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.

<configuration>

  <configSections>
    <sectionGroup name="spring">
      <section name="parsers" type="Spring.Context.Support.NamespaceParsersSectionHandler, Spring.Core" />
    </sectionGroup>
  </configSections>

  <spring>
    <parsers>
      <parser type="Spring.Data.Config.DatabaseNamespaceParser, Spring.Data" />
    </parsers>
  </spring>

</configuration>

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.

An example of such a setting is shown below

<configuration>
  <configSections>
    <sectionGroup name="spring">
      <section name='context' type='Spring.Context.Support.ContextHandler, Spring.Core'/>
    </sectionGroup>

    <section name="databaseSettings" type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
       
  </configSections>

  <spring>
    <context>      
      <resource uri="Aspects.xml" />
      <resource uri="Services.xml" />
      <resource uri="Dao.xml" />
    </context>
  </spring>

  <!-- These properties are referenced in Dao.xml -->
  <databaseSettings>
    <add key="db.datasource" value="(local)" />
    <add key="db.user" value="springqa" />
    <add key="db.password" value="springqa" />
    <add key="db.database" value="Northwind" />
  </databaseSettings>

</configuration>

Where Dao.xml has a connection string as shown below

<objects xmlns='http://www.springframework.net'
         xmlns:db="http://www.springframework.net/database">

  <db:provider id="DbProvider" 
      provider="System.Data.SqlClient" 
      connectionString="${db.datasource};Database=${db.database};User ID=${db.user};Password=${db.password};Trusted_Connection=False"/>
  
  <object id="adoTemplate" type="Spring.Data.AdoTemplate, Spring.Data">  
    <property name="DbProvider" ref="DbProvider"/>                
  </object>

  <!-- configuration of what values to substitute for ${ } variables listed above -->
  <object name="appConfigPropertyHolder"
          type="Spring.Objects.Factory.Config.PropertyPlaceholderConfigurer, Spring.Core">
    <property name="configSections" value="DatabaseConfiguration"/>
  </object> 

</objects>

Please refer to the Section Section 5.9.2.1, “Example: The PropertyPlaceholderConfigurer” for more information.

19.5. Additional IDbProvider implementations

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

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

<object id="DbProvider" type="Spring.Data.Common.UserCredentialsDbProvider, Spring.Data">
  <property name="TargetDbProvider" ref="targetDbProvider"/>
  <property name="Username" value="User ID=defaultName"/>
  <property name="Password" value="Password=defaultPass"/>
</object>
 
<db:provider id="targetDbProvider" provider="SqlServer-2.0"
    connectionString="Data Source=MARKT60\SQL2005;Database=Spring;Trusted_Connection=False"/>

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.SetCredentialsForCurrentThread("User ID=springqa", "Password=springqa");

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 under the key dbProviderName. 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.)

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

// Spring 1.2 M1 or later
LogicalThreadContext.SetData(MultiDelegatingDbProvider.CURRENT_DBPROVIDER_SLOTNAME, "database1ProviderName")

and the following ocde if you are using earlier versions

// Prior to Spring 1.2 M1
LogicalThreadContext.SetData("dbProviderName", "database1ProviderName")

and then call the data access layer.

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

<db:provider id="CreditAndDebitsDbProvider"
    provider="System.Data.SqlClient"
    connectionString="Data Source=MARKT60\SQL2005;Initial Catalog=CreditsAndDebits;User ID=springqa; Password=springqa"/>

<db:provider id="CreditDbProvider"
    provider="System.Data.SqlClient"
    connectionString="Data Source=MARKT60\SQL2005;Initial Catalog=Credits;User ID=springqa; Password=springqa"/>

<object id="dbProviderDictionary" type="Spring.Collections.SynchronizedHashtable, Spring.Core">
  <property name="['DbProvider1']" ref="CreditAndDebitsDbProvider"/>
  <property name="['DbProvider2']" ref="CreditDbProvider"/>
</object>

<object id="DbProvider" type="Spring.Data.MultiDelegatingDbProvider, Spring.Data">
  <property name="TargetDbProviders" ref="dbProviderDictionary"/>
</object>
previous page start next page