![]() |
Spring provides a generic factory for creating ADO.NET API artifacts
such as 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. The 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
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
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
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. 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.Core.AdoTemplate, Spring.Data"> <property name="DbProvider" ref="DbProvider"/> </object> </objects> 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
<objects xmlns='http://www.springframework.net' xmlns:db="http://www.springframework.net/database"> <db:additionalProviders resource="assembly://MyAssembly/MyAssembly.MyNamespace/AdditionalProviders.xml"/> <db:provider id="DbProvider" provider="System.Data.SqlClient" connectionString="Data Source=(local);Database=Spring;User ID=springqa;Password=springqa;Trusted_Connection=False"/> </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> 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= Where <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.Core.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. Spring provides some convenient implementations of the IDbProvider interface that add addtional behavior on top of the standard implementation.
This <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 userCredentialsDbProvider.SetCredentialsForCurrentThread("User ID=springqa", "Password=springqa");
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
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.3.0 or later MultiDelegatingDbProvider.CurrentDbProviderName = "database1ProviderName" // 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 <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"/> <property name="DefaultDbProvider" value="CreditDbProvider"/> </object> 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.
|