Autoquery Change IDbConnection

ServiceStack Auto Query
1

I have multiple named IDbConnectionFactories registered in Funq, my users can choose different DBs to query data from. What I would like to do is change the IDbConnection used by Autoquery based on parameters in the Request DTO. Is there a way to do this or would a PR to ServiceStack be in order?

Cheers and thanks for ServiceStack it’s pure awesome!

2

It can’t be changed with a QueryString but we’ve just added a new feature that lets you specify a named connection per Request DTO. Will that do what you need?

v4.0.53 is now available on MyGet.

3

There are multiple databases which have the same structure and are hosted on different servers. I use the same request DTO across these. If I could get access to the IDbConnection I could call the ChangeDatabase method or change the connection string.

4

Have a look at the MultiDbConnectionFactory approach taken that was posted in the original question:

It’s a bit uglier, but it’s the only way to be able to take over which DB Connection that AutoQuery ends up using.

5

I’ve just enabled an easier way to dynamically change which DB should be used at runtime where now all Requests to resolve a DB Connection go through AppHost.GetDbConnection(IRequest) which as it’s overridable and accepts an IRequest can be used to change which IDbConnection gets used at runtime. This change is available from v4.0.53 that’s now on MyGet.

The default implementation lets your Request Filters change the DB used by populating the ConnectionInfo POCO in IRequest.Items Dictionary with:

req.Items[Keywords.DbInfo] = new ConnectionInfo { ... };

The AutoQuery tests shows an example of this working for an AutoQuery and Normal ServiceStack Service which both share a common interface so we can easily apply common logic across multiple Request DTO’s, e.g:

public interface IChangeDb
{
    string NamedConnection { get; set; }
    string ConnectionString { get; set; }
    string ProviderName { get; set; }
}

[Route("/querychangedb")]
public class QueryChangeDb : QueryBase<Rockstar>, IChangeDb
{
    public string NamedConnection { get; set; }
    public string ConnectionString { get; set; }
    public string ProviderName { get; set; }
}

[Route("/changedb")]
public class ChangeDb : IReturn<ChangeDbResponse>, IChangeDb
{
    public string NamedConnection { get; set; }
    public string ConnectionString { get; set; }
    public string ProviderName { get; set; }
}

The Service Implementation just returns all Rockstars in Current Database:

public class ChangeDbResponse
{
    public List<Rockstar> Results { get; set; }
}

public class DynamicDbServices : Service
{
    public object Any(ChangeDb request)
    {
        return new ChangeDbResponse { Results = Db.Select<Rockstar>() };
    }
}

Then to enable this in your AppHost you just need to register a Request Filter that populates a ConnectionInfo:

RegisterTypedRequestFilter<IChangeDb>((req, res, dto) =>
    req.Items[Keywords.DbInfo] = dto.ConvertTo<ConnectionInfo>());

Typed Request Filters is just a short-hand for “only run this filter on Request DTO’s of this type or containing this interface”. The ConvertTo<T> is an Auto Mapping extension method which just copies over matching properties so the above example is equivalent to:

GlobalRequestFilters.Add((req, res, dto) =>
{
    var changeDb = dto as IChangeDb;
    if (changeDb == null) return;

    req.Items[Keywords.DbInfo] = new ConnectionInfo
    {
        NamedConnection = changeDb.NamedConnection,
        ConnectionString = changeDb.ConnectionString,
        ProviderName = changeDb.ProviderName,
    };
});

With just the normal OrmLite configuration you can now change what connection string the database will run on, which works in both in AutoQuery and Normal Services, e.g:

var response = client.Get(new ChangeDb { ConnectionString = connString });

var response = client.Get(new QueryChangeDb { ConnectionString = connString });

Whilst this changes the DB Connection string used, it’s still uses the default Dialect Provider, e.g. if you’ve registered a SQL Server OrmLite Connection, the Connection string would also need to use a SQL Server database.

One way to change which RDBMS to use is to use a named connection which you’ll need to register in your AppHost with any user defined key, e.g:

dbFactory.RegisterConnection("MasterDB", ":memory:", SqliteDialect.Provider);

Which will let you specify which named connection to use:

var response = client.Get(new QueryChangeDb { NamedConnection = "MasterDB" });

Another option for making a DB Connection using a different RDBMS Dialect Provider is to register it in your AppHost:

dbFactory.RegisterDialectProvider("InMemoryDB", SqliteDialect.Provider);

This now lets you refer to it by the provider name, e.g:

var response = client.Get(new QueryChangeDb { 
    ConnectionString = connString,
    ProviderName = "InMemoryDB",
});

Multi Tenancy Example

I’ve also updated the Multi Tenancy AppHost Example showing how much easier it is to implement a Multi Tenancy Service with this feature vs the previous approach of implementing a Custom IDbConnectionFactory.

6

You sir…are awesome.

If you are ever in Bermuda the beers are on me! This has simplified my code considerably and made my life so much easier!

1 Like