ORMLite Dialects

I have followed the answer given to this [question] (http://stackoverflow.com/questions/13691575/servicestack-ormlite-with-mutliple-database-servers), but am still getting a error. The error is:

{System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'dual'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at ServiceStack.MiniProfiler.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at ServiceStack.OrmLite.OrmLiteReadExtensions.ExecReader(IDbCommand dbCmd, String sql) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteReadExtensions.cs:line 48 at ServiceStack.OrmLite.OrmLiteReadExtensions.GetScalar[T](IDbCommand dbCmd, String sql, Object[] sqlParams) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteReadExtensions.cs:line 498 at ServiceStack.OrmLite.Oracle.OracleOrmLiteDialectProvider.GetNextValue(IDbCommand dbCmd, String sequence, Object value) at ServiceStack.OrmLite.Oracle.OracleOrmLiteDialectProvider.ToInsertRowStatement(Object objWithProperties, IList1 insertFields, IDbCommand dbCommand) at ServiceStack.OrmLite.OrmLiteDialectProviderBase1.ToInsertRowStatement(Object objWithProperties, IDbCommand command) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteDialectProviderBase.cs:line 439 at ServiceStack.OrmLite.OrmLiteWriteExtensions.Insert[T](IDbCommand dbCmd, T[] objs) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteWriteExtensions.cs:line 394 at ServiceStack.OrmLite.OrmLiteWriteConnectionExtensions.<>c__DisplayClass421.b__41(IDbCommand dbCmd) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteWriteConnectionExtensions.cs:line 164 at ServiceStack.OrmLite.ReadConnectionExtensions.Exec(IDbConnection dbConn, Action1 filter) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\Expressions\ReadConnectionExtensions.cs:line 55 at ServiceStack.OrmLite.OrmLiteWriteConnectionExtensions.Insert[T](IDbConnection dbConn, T[] objs) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteWriteConnectionExtensions.cs:line 164 at ServiceStack.ServiceInterface.Auth.OrmLiteAuthRepository.<>c__DisplayClass5.<CreateUserAuth>b__4(IDbConnection db) at ServiceStack.OrmLite.OrmLiteConnectionFactoryExtensions.Run[T](IDbConnectionFactory connectionFactory, Func2 runDbCommandsFn) in C:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteConnectionFactory.cs:line 171 at ServiceStack.ServiceInterface.Auth.OrmLiteAuthRepository.CreateUserAuth(UserAuth newUser, String password) at ServiceStack.ServiceInterface.Auth.RegistrationService.OnPost(Registration request) at ServiceStack.ServiceInterface.RestServiceBase`1.Post(TRequest request) ClientConnectionId:55977ae9-775d-474f-a543-d4cdfeb66a0d}

I am setting things up with the following code:

private void ConfigureAuth(Funq.Container container)
{
   ...
   //Create a DB Factory configured to access the UserAuth SQL Server DB
   var connStr = appSettings.Get("SQLSERVER_CONNECTION_STRING", //AppHarbor or Local connection string
   ConfigUtils.GetConnectionString("UserAuth"));
   container.Register<IDbConnectionFactory>(
                new AuthDBConnectionFactory(connStr, //ConnectionString in Web.Config
                SqlServerOrmLiteDialectProvider.Instance)
                {
                    ConnectionFilter = x => new ProfiledDbConnection(x, Profiler.Current)
                });

        //Store User Data into the referenced SqlServer database
    container.Register<IUserAuthRepository>(c =>
            new OrmLiteAuthRepository(c.Resolve<IDbConnectionFactory>())); //Use OrmLite DB Connection to persist the UserAuth and AuthProvider info

     var authRepo = (OrmLiteAuthRepository)container.Resolve<IUserAuthRepository>();
...
}

A little later I set up a connection to a vacancy database held on an oracle server:

private void ConfigureVacancies(Funq.Container container)
{
...
var connStr = appSettings.Get ("VACANCIES_CONNECTION_STRING", //AppHarbor or Local connection string
            ConfigUtils.GetConnectionString("VacanciesDB"));

var dbFactory = (OrmLiteConnectionFactory)container.Resolve<IDbConnectionFactory>();

dbFactory.RegisterConnection("vacancies",  connStr, ServiceStack.OrmLite.Oracle.OracleDialect.Provider);
...
}

The connection to the vacancies database works fine, but when I try to register a user on the auth database I get the error above. It seems to me that it is holding onto the Oracle Dialect?

Solution

I've dicovered that the autodispose connection is being used in the connection factory to also set the setGlobalConnection varible:

public void RegisterConnection(string connectionKey, string connectionString, IOrmLiteDialectProvider dialectProvider, bool autoDisposeConnection = true)
{
        NamedConnections[connectionKey] = new OrmLiteConnectionFactory(connectionString, autoDisposeConnection, dialectProvider, autoDisposeConnection);
}

Is that intentional? I've modified my code to set autoDisposeConnection to False, this fixes my dialect problem and allows service stack to query Oracle and MS SQL. I've not quite worked out what the consequence of setting autoDisposeConnection to false is. All my tests are passing now though!