T4 Template AutoQuery feature

ServiceStack.OrmLite
1

I have done some more on extending the T4 templates to auto-generate fully typed AutoQuery DTOs for each table exposed. Like this:

[UseNamedConnectionAttribute("SqlConnection.CommonDB")]
public partial class PackageTypeQuery: QueryBase<PackageType> 
{
    public string Id { get; set;}

    public string IdStartsWith { get; set;}
    public string IdEndsWith { get; set;}
    public string IdContains { get; set;}
    public string IdLike { get; set;} 
    public string[] IdBetween { get; set;}
    public string[] IdIn { get; set;}

    public bool? ActiveFlag { get; set;}

    public DateTime? CreatedDateGreaterThanOrEqualTo { get; set;}
    public DateTime? CreatedDateGreaterThan { get; set;}
    public DateTime? CreatedDateLessThan { get; set;}
    public DateTime? CreatedDateLessThanEqualTo { get; set;}
    public DateTime? CreatedDateNotEqualTo { get; set;} 
    public DateTime[] CreatedDateBetween { get; set;}
    public DateTime[] CreatedDateIn { get; set;}
 }

Its worked out really well, and now have a few thousand tables fully exposed to HTTP querying with no real coding beyond setting up the AppHost. But we needed to handle multiple Dbs so the following plug-in was required.

/// <summary>
/// Will cause IDbConnectionFactory to resolve to named connection
/// </summary>
public class UseNamedConnectionAttribute : RequestFilterAttribute
{
    public string NamedConnection { get; set; }

    public UseNamedConnectionAttribute(string namedConnection)
    {
        NamedConnection = namedConnection;
    }

    public override void Execute(ServiceStack.Web.IRequest req, ServiceStack.Web.IResponse res, object requestDto)
    {
        RequestContext.Instance.Items.Add(MultiDbConnectionFactory.RequestContextKey, NamedConnection);
    }
}
 

//Will pull injected context named connection to open
public class MultiDbConnectionFactory : IDbConnectionFactory
{
    public MultiDbConnectionFactory(IDbConnectionFactory failbackDbFactory)
    {
        FailbackDbFactory = failbackDbFactory;
    }
    private IDbConnectionFactory FailbackDbFactory;
    
    public const string RequestContextKey = "_UseNamedConnection";
   
    public IDbConnection CreateDbConnection()
    {
        try
        {
            return OrmLiteConnectionFactory.NamedConnections[(string)RequestContext.Instance.Items[RequestContextKey]].CreateDbConnection();
        }
        catch{}
        return FailbackDbFactory.CreateDbConnection();
    }

    public IDbConnection OpenDbConnection()
    {
        var conn = CreateDbConnection();
        conn.Open();
        return conn;
    }
}

/// <summary>
/// Registers a multi-db aware request filter to help handle T4 generated *Query 
/// requests. It will inject the proper named Db, as well as register them from
/// the AppSettings.
/// </summary>
public class AutoQueryMultiDbConnectionFeature : IPlugin
{
    private readonly ILog Log = LogManager.GetLogger(typeof(MultiDbConnectionFactory));

    public bool AutoLoadFromAppSettings { get; set; }
    public string AppKeyPrefix { get; set; }

    public AutoQueryMultiDbConnectionFeature(bool autoLoadFromAppSettings = true, string appKeyPrefix = "SqlConnection.")
    {
        AutoLoadFromAppSettings = autoLoadFromAppSettings;
        AppKeyPrefix = appKeyPrefix;
    }

    public void Register(IAppHost appHost)
    {
        OrmLiteConfig.DialectProvider = SqlServer2012Dialect.Provider;
        
        appHost.Register<IDbConnectionFactory>(new MultiDbConnectionFactory(new OrmLiteConnectionFactory() { DialectProvider = SqlServer2012OrmLiteDialectProvider.Instance}));
        if (AutoLoadFromAppSettings)
        {
            foreach (var key in HostContext.AppSettings.GetAllKeys().Where(t => t.StartsWith(AppKeyPrefix))) //This is our internal convention for AppSetting storage of connection strings (also is nice with Tier feature)
            {
                var nameKey = key;
                var factory = new OrmLiteConnectionFactory()
                {
                    DialectProvider = SqlServer2012Dialect.Provider ,
                    ConnectionString = HostContext.AppSettings.Get(key,string.Empty),
                };
                OrmLiteConnectionFactory.NamedConnections.Add(nameKey, factory); 
            }
        }
    }
}

I was planning on resubmitting the changes so others could use it, but was wondering if I should include the multi-db support, or how it could be refactored (beyond removing Sql2012 specifics) to better fit in to the framework.I’m also reluctant to include that aspect because I haven’t figured out a clean way to run unit tests. Suggestions are welcomed, because I imagine there is a cleaner way (I am just manually setting the named connection since filters don’t run):

[Test]
public void CountryCodeQueryTest()
{
        var betweenRange = new string[] { "C", "G" };
        var req = new CountryCodeQuery() { IdBetween = betweenRange, SortOrderGreaterThan = 52, Take = 40, Skip = 3, };
        var aq = req.GetType().FirstAttribute<UseNamedConnectionAttribute>();

        RequestContext.Instance.Items.Add(MultiDbConnectionFactory.RequestContextKey, aq.NamedConnection);
            HostContext.AppHost.ExecuteService(req).PrintDump();
    }
2

That’s awesome, happy to hear AutoQuery’s been that useful.

The Plugin can’t be included as it has concrete dependencies on OrmLite providers (no core package can have), the named connections would be different for each use-case. I think the [UseNamedConnection] feature could be useful but I’d want to explore it without needing a MultiDbConnectionFactory by making AutoQuery “named connection aware” and automatically using the selected named connection for its query.

At the moment I think this would be most useful in a blog post or GitHub repo containing a working example which we can announce and link to from the AutoQuery docs.

3

I’ve just added support for specifying different Named Connections by Type in this commit.

This lets you use the new [NamedConnection] attribute to have AutoQuery use a different named connection that’s registered in the AppHost as normal:

var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
container.Register<IDbConnectionFactory>(dbFactory);

dbFactory.RegisterConnection("SqlServer",
    "Server=localhost;Database=test;User Id=test;Password=test;", 
    SqlServer2012Dialect.Provider);

Then you can use the [NamedConnection] on any Table you want to use the named connection instead, e.g:

[NamedConnection("SqlServer")]
public class Rockstar { ... }

[Route("/query/rockstars")]
public class QueryRockstars : QueryBase<Rockstar> { ... }

This is the preferred approach as it doesn’t need to rely on using the static RequestContext to hot-swap which Db connection gets used.

This change is available from v4.0.53 that’s now available on MyGet.

4

Awesome! I’ll update my project and modify the T4 template

5

Hi Richard,

Just wanted to let you know about a new feature that makes it a lot easier to dynamically change databases at runtime. You shouldn’t need it for this, but may help if you want to support multiple databases in the same Service in future.

6

Cool, I can already see a few places I’ll end up using this in the coming months!