SUBSONIC MYSQL SharedDbConnectionScope TransactionScope

Question

The next code runs normally in SQLSERVER but when i change the web.config to runs with mySQL after the loadbykey the connection is closed.

is there any configuration to make in MYSQL to change that ???

thanks any help !!!

    //-----------------------------------------------------------
    string tipoBanco = System.Configuration.ConfigurationManager.AppSettings["tipoBanco"];
    string conexao = System.Configuration.ConfigurationManager.ConnectionStrings[tipoBanco].ToString();

    using (SharedDbConnectionScope scope = new SharedDbConnectionScope(conexao))
    {
        //-----------------------------------------------------------
        using (TransactionScope ts = new TransactionScope())
        {
            Cargo c = new Cargo();
            c.LoadByKey(9999);

            SubSonic.StoredProcedure sp = null;
            sp = new SubSonic.StoredProcedure("sp_test");
            sp.Command.AddParameter("paramTabela", "zzz", DbType.AnsiStringFixedLength, null, null);

            Convert.ToInt32(sp.ExecuteScalar());
        }
    }

the web.config

<SubSonicService defaultProvider="mysql" enableTrace="false" templateDirectory="">
 <providers>
  <clear/>
  <add name="mysql" type="SubSonic.MySqlDataProvider, SubSonic"
             regexDictionaryReplace="Empresaendereco,EmpresaEndereco;Empresacontato,EmpresaContato;Franqueadoendereco,FranqueadoEndereco;Franqueadocontato,FranqueadoContato;Funcionarioacesso,FuncionarioAcesso;Funcionarioendereco,FuncionarioEndereco;Funcionariocontato,FuncionarioContato;Clienteendereco,ClienteEndereco;Clientecontato,ClienteContato;Clientehistorico,ClienteHistorico;Agendastatus,AgendaStatus;Historicostatus,HistoricoStatus"
             fixDatabaseObjectCasing="true" connectionStringName="mysql"
             generateRelatedTablesAsProperties="true"
             fixPluralClassNames="false"
             generatedNamespace="ModeloDados"
             regexIgnoreCase="true"
             removeUnderscores="false"
             setPropertyDefaultsFromDatabase="true"
             generateNullableProperties="true"
             useExtendedProperties="true"
             useUtc="true" />

</providers>
</SubSonicService>

Answer 1

I think your TransactionScope() and SharedDbConnectionScope() are the wrong way round. You have them in the order they were listed in the docs on the SubSonic web site, but there was a discussion on the old SubSonic forum that implied they should be the other way round.

Certainly I use them reversed (I have just posted an extract of my code to another answer on here), but I have to say I only use MSSQL ATM, and can't comment on MySQL.