Adhoc Querying

The same qualities that make Templates great at querying State of a running .NET App also makes it excel at executing adhoc queries against providers which allow free text queries like OrmLite's Database Filters which enables access to its Dynamic Result Set APIs:


Register DB Filters

To access OrmLite's Database Filters install the OrmLite NuGet package for your RDBMS then add the TemplateDbFiltersAsync to your TemplateContext and register its required IDbConnectionFactory dependency in its IOC, e.g. for SQL Server:

var context = new TemplateContext {
    TemplateFilters = {
        new TemplateDbFiltersAsync(),
    }
};
context.Container.AddSingleton<IDbConnectionFactory>(() => new OrmLiteConnectionFactory(
    connectionString, SqlServer2012Dialect.Provider));
context.Init();
If using ServiceStack's TemplatePagesFeature it's Container has already been reassigned to use ServiceStack's Funq IOC so it only needs to be registered once in ServiceStack's IOC.

Always protect free text APIs

If you're exposing filters enabling a free text API against a production database it should never be accessible by untrusted parties so you'll want to at a minimum ensure Services are protected with the [Authenticate] attribute so it's only available to Authenticated Users and ideally configure it to use a Read Only connection, e.g. for SQLite:

container.Register<IDbConnectionFactory>(c => new OrmLiteConnectionFactory(
    $"Data Source={filePath};Read Only=true", SqliteDialect.Provider));

Populating the database

The database queried above was populated in the AppHost where it re-uses the LINQ data sources to create and populate an In Memory SQLite database on Startup:

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

using (var db = container.Resolve<IDbConnectionFactory>().Open())
{
    db.CreateTable<Order>();
    db.CreateTable<Customer>();
    db.CreateTable<Product>();
    TemplateQueryData.Customers.Each(x => db.Save(x, references:true));
    db.InsertAll(TemplateQueryData.Products);
}

made with by ServiceStack