Minimum configuration example showing how to use AdoTemplate with MySql or Postgresql

[samotjo]

Is it possible to use MySql or Postgresql in as simple way as it is to use SQL Server with AdoTemplate ?

Or is it necessary to do some additional xml (or other) configuration, e.g. is it necessary to use some Xml file with configuration code, e.g. Spring Dependency Injection xml files ?

This working example below shows how easy it is to use SQL Server with AdoTemplate but without doing any kind of xml configuration:

using System.Data;
using NUnit.Framework;
using Spring.Data.Common;
using Spring.Data.Generic;
 ...
    public void TestingSqlServer() {
        IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
        dbProvider.ConnectionString = @"Data Source=localhost;Initial Catalog=myExperimentalDatabase; User Id=myUserId; Password=myPassword;";
        var adoTemplate = new AdoTemplate(dbProvider);
        int count = (int)adoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from persons");
        Console.WriteLine("count: " + count);
    }

However, when trying with MySql, then the following code line throws an exception:
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("MySql.Data.MySqlC lient");
The exception thrown is the following:

Spring.Objects.Factory.UnsatisfiedDependencyException : Error thrown by a dependency of object 'MySql.Data.MySqlClient' defined in 'assembly [Spring.Data, Version=1.3.2.40943, Culture=neutral, PublicKeyToken=65e474d141e25e07], resource [Spring.Data.Common.dbproviders.xml] line 853' : Unsatisfied dependency expressed through constructor argument with index 2 of type [System.Type] : Could not convert constructor argument value [MySql.Data.MySqlClient.MySqlConnection, MySql.Data, Version=6.2.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d] to required type [System.Type] : Cannot convert property value of type [System.String] to required type [System.Type] for property ''.
 while resolving 'constructor argument with name dbmetadata' to 'Spring.Data.Common.DbMetadata#152C067' defined in 'assembly [Spring.Data, Version=1.3.2.40943, Culture=neutral, PublicKeyToken=65e474d141e25e07], resource [Spring.Data.Common.dbproviders.xml] line 853'
at Spring.Objects.Factory.Support.ObjectDefinitionValueResolver.ResolveInnerObjectDefinition(String name, String innerObjectName, String argumentName, IObjectDefinition definition, Boolean singletonOwner) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\ObjectDefinitionValueResolver.cs: line 305
at Spring.Objects.Factory.Support.ObjectDefinitionValueResolver.ResolvePropertyValue(String name, IObjectDefinition definition, String argumentName, Object argumentValue) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\ObjectDefinitionValueResolver.cs: line 151
at Spring.Objects.Factory.Support.ObjectDefinitionValueResolver.ResolveValueIfNecessary(String name, IObjectDefinition definition, String argumentName, Object argumentValue) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\ObjectDefinitionValueResolver.cs: line 113
at Spring.Objects.Factory.Support.ConstructorResolver.ResolveConstructorArguments(String objectName, RootObjectDefinition definition, ObjectWrapper wrapper, ConstructorArgumentValues cargs, ConstructorArgumentValues resolvedValues) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\ConstructorResolver.cs: line 592
at Spring.Objects.Factory.Support.ConstructorResolver.GetConstructorInstantiationInfo(String objectName, RootObjectDefinition rod, ConstructorInfo[] chosenCtors, Object[] explicitArgs) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\ConstructorResolver.cs: line 156
at Spring.Objects.Factory.Support.ConstructorResolver.AutowireConstructor(String objectName, RootObjectDefinition rod, ConstructorInfo[] chosenCtors, Object[] explicitArgs) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\ConstructorResolver.cs: line 95
at Spring.Objects.Factory.Support.AbstractAutowireCapableObjectFactory.CreateObjectInstance(String objectName, RootObjectDefinition objectDefinition, Object[] arguments) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractAutowireCapableObjectFactory.cs: line 1000
at Spring.Objects.Factory.Support.AbstractAutowireCapableObjectFactory.InstantiateObject(String name, RootObjectDefinition definition, Object[] arguments, Boolean allowEagerCaching, Boolean suppressConfigure) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractAutowireCapableObjectFactory.cs: line 940
at Spring.Objects.Factory.Support.AbstractObjectFactory.GetObjectInternal(String name, Type requiredType, Object[] arguments, Boolean suppressConfigure) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractObjectFactory.cs: line 2086
at Spring.Objects.Factory.Support.AbstractObjectFactory.GetObject(String name, Type requiredType) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractObjectFactory.cs: line 1835
at Spring.Data.Common.DbProviderFactory.GetDbProvider(String providerInvariantName) in c:\_prj\spring-net\trunk\src\Spring\Spring.Data\Data\Common\DbProviderFactory.cs: line 94

It does not matter whether or not I have MySql.Data connector available within the project references or not.
I have tried with the latest connector version 6.5.4.0, retrieved with nuget, which also is the latest version currently available at http://www.mysql.com/downloads/connector/net/.
The Spring version I am using is also the latest version (1.3.2) which was also retrieved with nuget.

According to the documentation at http://www.springframework.net/docs/...bprovider.html
then the string "MySql.Data.MySqlClient" should be equivalent to be using "MySql-6.2.2" (as far as I understand these strings).

However, since it does not matter whether or not I a referencing any "MySql.Data.dll" in the project or not (i.e. the same error message) I suppose the version is not the problem.
I mean, if only 6.2.2 is supported while I only have the 6.5.4.0 assembly in the project references I would have expected an error message complaining about the version of that dll assembly file.

I have also tried with Postgresql and the following code:
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("Npgsql-2.0");
(with the string "Npgsql-2.0" defined at the Spring 1.3.2 reference page, and I have the latest Npgsql version "2.0.11.0" retrieved with nuget referenced in the project)
gives a similar UnsatisfiedDependencyException message ( ... while resolving 'constructor argument with name dbmetadata' to 'Spring.Data.Common.DbMetadata#1939738' defined in 'assembly ... )

I have also tried with Spring 2.0.0-M1 (instead of 1.3.2) but still get the same problems.

So, please can someone show a minimum working example (with as little required configuration as possible) how you are supposed to use MySql (or postgresql) access with AdoTemplate, and preferably while also being able to use the latest version of the MySql.Data (or postgresql's Npgsql) ADO.NET driver ?

[samotjo]

In the following line:
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("MySql.Data.MySqlC lient");
I really tried to avoid the space between the "C" and the "l" in "MySqlC lient", but could not post it in that way.
I suppose it has to be a bug with the forum software used here at the site "forum.springframework.net" ).
I just wanted to mention that to avoid suggestions that the space is causing problems.
I do not have that space in the code, and tried to get rid of it before I posted the above (I hoped that it was a "preview" problem, but now see it turned up like that in the posted online version)

[samotjo]

For those who are interested in this topic, I can provide working examples for both mysql and postgresql, though without the latest version and without using Nuget, which is annoying...

I have tested to use the same dll assemblies with the below source code (DbProviderFactory.GetDbProvider...) with the following four combinations of operating systems (Windows and Linux) and database engines (MySql and PostgreSql):
* Windows 7, MySql version 5.5.21
* Linux Ubuntu 12.04, MySql version 5.5.24
* Windows 7, Postgresql version 9.1.4
* Linux Ubuntu 12.04, Postgresql version 9.1.4


MySql example that works with the DLL assembly files specified further down:

            
using System.Data;
using Spring.Data.Common;
using Spring.Data.Generic;
...
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("MySql-6.2.2");
dbProvider.ConnectionString = "Server=localhost;Database=myDatabase;Uid=myUserId;Pwd=myPassword;";
var adoTemplate = new AdoTemplate(dbProvider);

PostgreSql example that works with the DLL assembly files specified further down:

IDbProvider dbProvider = DbProviderFactory.GetDbProvider("Npgsql-2.0");
dbProvider.ConnectionString = "Server=127.0.0.1;Port=5432;User Id=myUserId;Password=myPassword;Database=myDatabase;";
var adoTemplate = new AdoTemplate(dbProvider);

The annoying thing with making the above code work is that neither MySql-6.2.2 nor Npgsql-2.0 are the latest version (and also currently not available with Nuget).

Since they were not available with nuget, I had to try finding these versions from other places.

I found the MySql-6.2.2 assembly here:
http://dev.mysql.com/downloads/conne...html#downloads
(.NET & Mono version, "mysql-connector-net-6.2.2-noinstall.zip" )
http://dev.mysql.com/downloads/mirror.php?id=379360
I do not know how old (which date) the above 6.2.2 version is, but the currently latest version is 6.5.4.
( I was not able to find the above 6.2.2 version by clicking the link "Looking for previous GA versions?" but was instead able to find the link with googling ... )

I found the Npgsql-2.0 assembly here:
http://pgfoundry.org/frs/?group_id=1000140
( Npgsql2.0RTM , "Npgsql2.0rtm-bin-mono2.0.zip", from 2008-10-06 04:35 , i.e. almost four year old version )
I can also mention that to be able to use the PostgreSql from ASP.NET MVC 3 at Windows, I had to add a reference to the file 'Mono.Security' to avoid the following exception:
"Could not load file or assembly 'Mono.Security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756' or one of its dependencies. The system cannot find the file specified."
I used the assembly "Mono.Security.dll" from "Npgsql2.0rtm-bin-mss.net3.5sp1.zip" which was downloaded from above Url.

I believe that the Spring.NET supported database connector versions are those defined in the file "...\src\Spring\Spring.Data\Data\Common\dbproviders.xml".
(by looking in these files, I figured out what latest version numbers that seem to be supported, and then tried to find these assemblies from outside of nuget and downloaded and tested them...)
As far as I understand, the currently latest Spring version "Spring.NET-2.0.0-M1" ( 2012-06-26 ) does not support any more updated versions (of the mysql and postgresql ADO.NET drivers / connectors ) than those supported by Spring 1.3.2, i.e. MySql connector 6.2.2 and PostgreSql connector 2.0 (i.e. more recent versions of the connectors seems to not be specified in "dbproviders.xml").

From my point of view, it seems strange that nothing newer than a four year old version of postgres seem to be supported in Spring.NET's AdoTemplate, considering that I assume that both Spring.NET and PostgreSql are frequently used libraries, but I guess the explanation still might be that only few developers are using the _combination_ of Spring.NET and Postgresql (nor mysql) i.e. not so many people have requested that these two should be working together with the latest drivers/connectors ?

If I have misunderstood something, then please illustrate how to be able to use the latest drivers for mysql and postgresql together with AdoTemplate.