Spring provides a generic factory for creating ADO.NET API artificats 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 provides 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 and extension mechanism is available for defining new database providers or modifying exisiting ones. A custom database namespace for configuration aids in making terse XML based declaration of Spring's database objects you which 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 promient is the that the top level DbException exposes the HRESULT of the remote proces 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 an DbCommand with its connection and transaction properties already set based on the transaction calling context.

The IDbProvider API is shown below and should look familar 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);

        IDbMetadata DbMetadata
        {
            get;
        }               

        string ConnectionString
        {
            set;
            get;
        }
        
        string ExtractError(Exception e);

        bool IsDataAccessException(Exception e);
        
    }

The 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.

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.

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. This crude mechanism will eventually be replaced shorly with one based on custom configuration section in App.config/Web.config.

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:d="http://www.springframework.net/database">

  <d:dbProvider 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.ConfigParsersSectionHandler, Spring.Core" />
    </sectionGroup>
  </configSections>

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

</configuration>

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

The first option is to leverage spring property replacement functionality, as described in Section 4.9.1, “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:d="http://www.springframework.net/database">

  <d:dbProvider 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>

</objects>

TODO: The second option is to use a custom schema specific to your database. The case of using SqlServer is shown below.